Использование значения NULL в условиях поиска

Предикат:

IS [NOT] NULL

позволяет проверить отсутствие (наличие) значения NULL в столбцах таблицы. Использование в этих случаях обычных предикатов сравнения может привести к неверным результатам, так как сравнение со значением NULL дает результат UNKNOWN (неизвестно).

Так, если требуется найти записи в таблице PC, для которых в столбце price отсутствует значение (например, при поиске ошибок ввода), можно воспользоваться следующим оператором:

SELECT *
FROM PC
WHERE price IS NULL;

Характерной ошибкой является написание предиката в виде:

WHERE price = NULL

Этому предикату не соответствует ни одной строки, поэтому результирующий набор записей будет пуст, даже если имеются изделия с неизвестной ценой. Это происходит потому, что сравнение с NULL-значением согласно предикату сравнения оценивается как UNKNOWN. А строка попадает в результирующий набор только в том случае, если предикат в предложении WHERE есть TRUE. Это же справедливо и для предиката в предложении HAVING.

Аналогичной, но не такой очевидной, ошибкой является сравнение с NULL в предложении CASE). Чтобы продемонстрировать эту ошибку, рассмотрим такую задачу: «Определить год спуска на воду кораблей из таблицы Outcomes. Если последний неизвестен, указать 1900».

Поскольку год спуска на воду (launched) находится в таблице Ships, нужно выполнить левое соединение:

SELECT ship, launched
FROM Outcomes o 
    LEFT JOIN Ships s ON o.ship = s.name;

Для кораблей, отсутствующих в Ships, столбец launched будет содержать NULL-значение. Теперь попробуем заменить это значение значением 1900 с помощью оператора CASE:

SELECT ship, 
CASE launched
    WHEN NULL
    THEN 1900
    ELSE launched
END "year"
FROM Outcomes o 
    LEFT JOIN Ships s ON o.ship=s.name;

Однако ничего не изменилось. Почему? Потому что используемый оператор CASE эквивалентен следующему:

CASE
    WHEN launched = NULL
    THEN 1900
    ELSE launched
END 'year'

А здесь мы получаем сравнение с NULL-значением, и в результате — UNKNOWN, что приводит к использованию ветви ELSE, и все остается, как оно и было. Правильным будет следующее написание:

CASE
    WHEN launched IS NULL 
    THEN 1900
    ELSE launched
END 'year'

то есть выполняется проверка именно на наличие NULL-значения.

Рекомендуемые упражнения: 43, 52, 59, 64, 75