Предикат NOT IN

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

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

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

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

А теперь напишем запрос, который должен вернуть все корабли, год спуска на воду не находится в наборе значений столбца 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;

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

Казалось бы, мы должны получить корабли 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;