Books and articles about SQL Rambler's Top100 Сменить язык на: Русский 06 December 2019 09:25:58


www.sql-ex.ru
Skip Navigation Links  

 

Print  Версия для печати

На главную страницу

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

Моисеенко С.И.

Рассмотрим следующий пример. Пусть требуется определить корабли с неизвестным годом спуска на воду.

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

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

Здесь мы добавили в подзапросе поле 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). Тогда для решения нашей задачи можно написать:

SELECT * FROM Ships WHERE launched IS NULL

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

SET ANSI_NULLS OFF|ON

Напишите в Query Analyzer следующий код, и вы все поймете:

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

Предикат NOT IN

Рассмотрим еще один пример, позаимствованный мной у Селко. Идея его состоит в использовании предиката NOT IN (<набор значений, включающий NULL>).

Опять таки, для того, чтобы вы могли проверить справедливость рассуждений на сайте, давайте искусственно добавим NULL-значения в результат запроса:

SELECT name, launched,
   CASE WHEN launched < 1915 THEN NULL ELSE launched END year
FROM Ships WHERE launched <=1915

Я специально взял 1915 год, чтобы результирующий набор был невелик. Вот он:

name launched year
Hiei 1914 NULL
Kirishima 1915 1915
Kongo 1913 NULL

А теперь напишем запрос, который должен вернуть все корабли, год спуска на воду не находится в наборе значений столбца year:

SELECT * FROM Ships     WHERE launched <=1916 AND
        launched NOT IN
    (SELECT year FROM
    (
    SELECT name, launched, CASE WHEN launched < 1915 THEN NULL ELSE launched END year
    FROM Ships WHERE launched <=1915
    ) x
    )

Запрос

SELECT * FROM Ships
WHERE launched <=1915

дает нам следующий набор кораблей

name class launched
Hiei Kongo 1914
Kirishima Kongo 1915
Kongo Kongo 1913

Казалось бы, мы должны получить корабли Hiei и Kongo, т.к. год их спуска на воду известен и не равен 1915. Ан нет, мы опять получаем пустой результирующий набор.

Оценим значение предиката для первого из этих кораблей - Hiei (для остальных все будет аналогично). Итак,

1914 NOT IN (1915, NULL)

Еще одно NULL-значение я опустил для краткости. Последний предикат можно заменить следующим:

1914 <> ALL (1915, NULL)

что эквивалентно

1914 <> 1915
        AND
1914 <> NULL

Последнее выражение всегда равно UNKNOWN, следовательно, предикат можно переписать в виде:

1914 <> 1915
        AND
UNKNOWN

Следовательно, и все выражение будет равно UNKNOWN, т.к. первое сравнение дает TRUE. Если бы первое сравнение было ложным (для 1915 года), то результат всего выражения был бы равен FALSE.

Поэтому можно сделать вывод, что при наличии NULL-значения в наборе предикат NOT IN в предложении WHERE всегда будет давать пустой набор записей.

В заключение следует сказать, что если вы выполняете горизонтальную фрагментацию некоторой таблицы, используя некоторое пороговое значение столбца, допускающего NULL-значения, то объединение фрагментов типа

SELECT * FROM Ships WHERE launched <= 1915
UNION
SELECT * FROM Ships WHERE launched > 1915

не гарантирует восстановления исходной таблицы. Для этого потребуется еще один фрагмент, содержащий в столбце launched NULL-значения:

SELECT * FROM Ships WHERE launched IS NULL

Приведенные здесь примеры можно выполнить непосредственно на сайте, установив флажок "Без проверки" на странице с упражнениями на SELECT.

На главную страницу

Print  Версия для печати


Usage of any materials of this site is possible
only under condition of mandatory allocation of the direct link to a site
http://www.sqlbooks.ru
on each page where used materials are placed.

 Main   Articles    Books 
Рейтинг@Mail.ru Rambler's Top100 Alt Упражнения по SQL: обучение, тестирование, сертификация по языку SQL Copyright c 2002-2006. All rights reserved.