Сложные выражения со значением
Специальное выражение CASE – NULLIF
Время от времени предметы переходят из одного известного состояния в другое. Иногда вам кажется, что вы что-то знаете, но в конце концов выясняется, что это не так. Классическая термодинамика, как и современная теория хаоса, утверждает, что системы легко переходят из хорошо известного, упорядоченного состояния в состояние хаоса, которое никто не может предсказать. Кто когда-либо видел состояние комнаты подростка через неделю после того, как в ней была генеральная уборка, тот может поручиться за точность этих теорий.
В таблицах из базы данных точно определенные значения находятся в тех полях, в которых имеются известные данные. А если значение поля неизвестно, то в этом поле обычно находится неопределенное значение (NULL). SQL дает возможность с помощью выражения CASE менять определенное значение табличного поля на неопределенное. Значение NULL означает, что значение поля вам больше не известно.
Представьте, что вы владеете небольшой авиакомпанией, которая выполняет рейсы между Южной Калифорнией и штатом Вашингтон. До недавних пор во время некоторых из рейсов делалась промежуточная посадка в международном аэропорту Сан-Хосе для дозаправки. Затем вы лишились разрешения на полеты в Сан-Хосе. Теперь дозаправку приходится проводить в одном из двух международных аэропортов: или Сан-Франциско, или Окленда. Так что сейчас вам точно не известно, во время какого рейса в каком из аэропортов будут садиться ваши самолеты, ясно лишь, что не в Сан-Хосе. У вас имеется база данных FLIGHT (полет), в которой находится важная информация о каждом из ваших рейсов (в том числе данные в столбце RefuelStop (остановка для дозаправки)). Теперь эту базу нужно обновить, чтобы удалить из нее все упоминания о Сан-Хосе. Один из способов это сделать показан в следующем примере:
UPDATE FLIGHT SET RefuelStop = CASE WHEN RefuelStop = 'San Jose1 THEN NULL ELSE RefuelStop END;
Так как подобные ситуации, когда нужно заменить известное значение неопределенным (NULL), бывают часто, то для выполнения этой задачи в SQL имеется специальный упрощенный синтаксис. Вот как выглядит предыдущий пример, переписанный в таком упрощенном виде:
UPDATE FLIGHT SET RefuelStop = NULLIF(RefuelStop, 'San Jose');
Это выражение можно прочитать следующим образом: "Обновить базу данных FLIGHT, заменяя в столбце RefuelStop значение San Jose на NULL. Другие значения менять не надо".
Синтаксис NULLIF еще более удобен, если нужно преобразовать данные, накопленные в таком виде, который позволял с ними работать с помощью программы, написанной на стандартном языке программирования, например на COBOL или Fortran. В стандартных языках программирования значение NULL не используется, поэтому очень часто для выражения понятия "не известен" или "не применяется" используются специальные значения. Например, значение "не известен" в поле SALARY (зарплата) может быть представлено числом -1, а значение "не известен" или "не применим" в поле JOBCODE (код задания) – символьной строкой "***". Если необходимо в SQL-совместимой базе данных представить состояния "не известен" или "не применим" с помощью значения NULL, то специальные значения придется преобразовать в неопределенные. В следующем примере эта операция выполняется для таблицы с данными о сотрудниках, в которой некоторые значения окладов неизвестны:
UPDATE EMP SET Salary = CASE Salary WHEN -1 THEN NULL ELSE Salary END;
Это преобразование удобнее выполнять, используя выражение NULLIF:
UPDATE EMP SET Salary = NULLIF(Salary, -1);