Готовимся ко второму этапу тестирования

Типичные проблемы

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

Пусть требуется определить корабли с неизвестным годом спуска на воду (база данных "Корабли").

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

Решение 8.5.1

Консоль

Выполнить

SELECT * 
FROM Ships 
WHERE launched = NULL;

SELECT * FROM Ships WHERE launched = NULL;

    Схема

Консоль

Выполнить

SELECT * 
FROM (SELECT name, launched, 
 CASE 
 WHEN launched < 1940 
 THEN NULL 
 ELSE launched 
 END year 
 FROM Ships
 ) x
WHERE year = 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;

SELECT * FROM Ships WHERE launched IS NULL;

    Схема

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

SET ANSI_NULLS OFF|ON

Напишите в Management Studio (или в Query Analyzer для  Cистема управления реляционными базами данных (СУБД), разработанная корпорацией Microsoft.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;

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;

    Схема

Содержание:

(8.5.1.) Предикат NOT IN