На главную страницу
Трехзначная логика и предложение 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.
На главную страницу