Трехзначная логика и предложение Where

Рассмотрим следующий пример.

Если мы напишем

Решение 8.5.1

SELECT *
FROM Ships
WHERE launched = NULL;

то, как бы ни казалось это странным, мы не получим ни одной записи, даже если такие корабли имеются в таблице Ships (напомним, что столбец launched допускает NULL-значения) Поскольку в доступной базе данных нет кораблей с неизвестным годом спуска на воду, давайте их создадим, чтобы вы могли проверить справедливость данного утверждения:

SELECT *
FROM (SELECT 
        name, 
        launched,
        CASE
            WHEN launched < 1940
            THEN NULL
            ELSE launched
        END year
FROM Ships
) x
WHERE year = NULL;

Здесь мы добавили в подзапросе столбец year, который содержит NULL, если корабль был спущен на воду до 1940 года.

Итак, почему мы ничего не получили? Здесь следует вспомнить о том, что в SQL (и вообще в реляционной теории) используется трехзначная логика, то есть истинностным значением операции сравнения может быть не только TRUE (истина) и FALSE (ложь), но и UNKNOWN (неизвестно). Это обусловлено существованием NULL-значения, сравнение с которым и дает это истинностное значение. Это интуитивно понятно, если помнить, что NULL-значение служит для замены неизвестной информации. Если мы спросим: «Является ли годом спуска на воду корабля Бисмарк 1939 год»? Ответом будет: «Не знаю». Так как у нас нет информации в базе данных о годе спуска на воду этого корабля, это «не знаю» и есть UNKNOWN.

Что происходит, если в предложении WHERE мы используем сравнение с NULL-значением явно или неявно (с NULL-значением в сравниваемом столбце)? Запись попадает в результирующий набор, если предикат дает истинностное значение TRUE. И все, то есть при значениях FALSE или UNKNOWN запись не попадает в результат. Именно поэтому мы ничего и не получили в приведенном выше примере, поскольку для всех строк получаем UNKNOWN.

Так как же получить список кораблей с неизвестным годом спуска на воду? Для этого в стандарте SQL имеется специальный предикат IS NULL (и обратный ему IS NOT NULL). Истинностным значением этого предиката не может быть UNKNOWN, то есть год либо известен (FALSE), либо неизвестен (TRUE). Тогда для решения нашей задачи можно написать:

Решение 8.5.2

SELECT *
FROM Ships
WHERE launched IS NULL;

Это стандарт. А что же реализации? Все сказанное выше справедливо для SQL Server. Однако это не единственная возможность. Видимо, чтобы сделать программирование на SQL более привычным для тех, кто пользуется традиционными языками программирования, можно отключить стандартную трактовку NULL-значений (по умолчанию включено) с помощью соответствующей установки параметра ANSI_NULLS:

SET ANSI_NULLS OFF|ON

Напишите в Management Studio (или в Query Analyzer для SQL Server 2000 и ранее) следующий код, и вы все поймете:

SET ANSI_NULLS OFF;
SELECT *
FROM (SELECT 
            name, 
            launched,
            CASE
                WHEN launched < 1940
                THEN NULL
                ELSE launched
            END year
FROM Ships
) x
WHERE year = NULL;