Порой, очевидные вещи таят в себе удивительные сюрпризы. Казалось бы, простые SQL конструкции: «IN» и «NOT IN». Что тут обсуждать-то?

Учитель философии. Конечно. Вы хотите написать ей стихи?

Г-н Журден. Нет-нет, только не стихи.

Учитель философии. Вы предпочитаете прозу?

Г-н Журден. Нет, я не хочу ни прозы, ни стихов.

Учитель философии. Так нельзя: или то, или другое.

Г-н Журден. Почему?

Учитель философии. По той причине, сударь, что мы можем излагать свои мысли не иначе как прозой или стихами.

Г-н Журден. Не иначе как прозой или стихами?

Учитель философии. Не иначе, сударь. Все, что не проза, то стихи, а что не стихи, то проза.

                                                                       Мольер. Мещанин во дворянстве

Действительно! Все же просто! Все что не «IN», то «NOT IN», а что не «NOT IN», то «IN». Разве не так?

Ну что ж проведем эксперимент.

Для начала создадим таблицу HUMAN c данными о некоторых сотрудниках, включающих ID, Фамилию, и номер отдела, где они работают.

CREATE TABLE hr.human (

id_human int4 NOT NULL,

last_name text NULL,

dept int4 NULL,

CONSTRAINT human_pkey PRIMARY KEY (id_human)

);

Заполним таблицу данными.

INSERT INTO hr.human (id_human, last_name, dept) VALUES

(1, 'Иванов', 1),

(2, 'Петров', 2),

(3, 'Сидоров', 3),

(4, 'Гаврилов', 1),

(5, 'Смирнов', 2),

(6, 'Андреев', 3),

(7, 'Соболев', NULL);

И проверим, что содержит наша таблица.

SELECT * 

FROM human

Таблица содержит сведения о 7 сотрудниках.

Tеперь выведем сведения о сорудниках работающих в отделах 1 и 2. Следующим запросом.

SELECT * 

FROM human

WHERE dept IN (1,2);

Все верно. Таких сотрудников, работающих в этих отделах действительно четверо.

Ну а теперь попробуем получить сведения об остальных сотрудниках. Сделать это просто заменив в нашем примере «IN» на «NOT IN».

SELECT * 

FROM human

WHERE dept NOT IN (1,2);

И получим результат.

И вот тут мы видим, что один сотрудник «потерялся». Сотрудник Соболев с номером 7 не попал ни в один из списков. Не трудно заметить, что значение отдела у него не заполнено, т.е. имеет значение NULL. Именно поэтому он, очевидно, и был отбракован обоими запросами.

Почему же это произошло? Дело в том, что значение NULL следует воспринимать не как отсутствие значения, а как потенциально любое значение, или, другими словами, неизвестное значение. Увы, в теории баз данных это общепринятая концепция, не зависящая от того, как лично вы (или пользователи-предметники) интерпретируют отсутствующее значение.

Понятно, что как только «IN» наткнулся на это значение он «понял» что оно наверняка не совпадает ни с одним из перечисленных значений списка и забраковал его.

В свою очередь «NOT IN» проверял что значение наверняка не входит в перечень и так же забраковал его. И если от «IN» мы интуитивно ожидали такого поведения, то «NOT IN» повел себя не много не так ожидалось. Казалось бы “NOT” должен «переворачивать» результат, который получает следующий за ним “IN”. Но оказывается, что словосочетание «NOT IN» следует воспринимать как единую команду. интерпретировать как «значение невходит» (орфография нарушена умышленно), одним действием, а не как отрицание «значение не входит», т.е. отрицание предыдущего полученного результата.

Eсли наша гипотеза верна тогда конструкция NOT (IN…) должна включить именно инвертированных от IN список. Логика такова, что IN получит список из четырех сотрудников, а уж NOT будучи отдельным оператором его перевернет (инвертирует). Убедимся в этом.

SELECT * 

FROM human

WHERE NOT (dept IN (1, 2)); 

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

Дело в том, что мы, опять-таки, забыли про трехзначную логику. У логических операторов не два, а три результата, а именно: ИСТИНА, ЛОЖЬ и НЕ ЗНАЮ. Перебирая значение оператор IN натыкается на NULL и для этой строки результат выполнение этого оператора будет NULL. Соответственно оператор NOT просматривая результат полученный IN натыкается на полученное значение NULL и так же не знает, что с ним делать и не включает в результат. Таким образом строка, содержащая значение NULL, не попадает ни в один из результатов.

Это достаточно опасная ситуация для разработчика, поскольку в сложных запросах может привести к потере данных в выводимых отчетах и вообще непредсказуемое (с точки зрения неопытного разработчика) поведение. А если тестирование системы проводилось на данных, не содержащих неопределенные значения, ошибка может выявиться уже на этапе продуктива, причем не в виде сообщения об ошибке на экране монитора, а в получении неверных отчетов, на основе которых будут приниматься неверные решения.

Ну а мы не привыкли отступать! Попробуем решить проблему достаточно простым способом. Включим значение NULL в один из списков. Теперь то уж точно получиться!

Для начала пусть это будет вариант с IN.

SELECT * 

FROM human

WHERE dept IN (1, 2, NULL); 

Хм? Странно. Соболев опять не попал в результат.

А что же тогда выдаст второй запрос с NOT IN?

SELECT * 

FROM human

WHERE dept IN (1, 2, NULL); 

Вообще ничего не выдал.

Мало того, что нам так и не удалось включить Соболева ни в один из списков, так еще и запросы перестали вести себя симметрично. Теперь мы потеряли еще и сотрудников, которые работают во вполне конкретном третьем отделе. Почему?

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

Как же работает запрос с IN? Сравнивая поочередно конкретные значения из списка 1 и 2 с неопределенным значением отдела у Соболева, оператор не может уверенно сказать, что они совпадают, т.е. не знает совпали они или нет, а следовательно результат такого сравнения будет NULL. Но вот мы доходим до проверки третьего значения NULL. Проверяем NULL = NULL. Каков результат такого сравнения? Конечно же NULL. Ведь мы ничего не знаем о значении правой части и левой части этого сравнения. Может они совпадают, а может и нет. Результат так же не известен. Поскольку мы не нашли совпадений значения отдела Соболева ни с одним из перечисленных значений в списке IN Соболев в результат не попадает.

Ну а что же с NOT IN?

Перебирая значения NOT IN должен для всех сравнений без исключения получить ЛОЖЬ. И это удается до тех пор, пока он не добирается до значения NULL. А вдруг NULL из списка IN все же совпадет со значение отдела, например у Иванова, имеющего вполне конкретное значение 1? Мы не знаем. Соответственно Иванова в результат не включаем, как и всех остальных сотрудников. Я уже и не говорю про Соболева, ведь результат сравнения NULL<>NULL будет конечно же NULL.

Как же нам написать запрос, отражающий все данные?  Например, можно явно указать дополнительное условие добавляя в список все строки со значением NULL.

SELECT * 

FROM human

WHERE dept NOT IN (1, 2) OR dept IS NULL; 

Или инвертировав полученный через IN список во внешнем запросе.

select * 

FROM human

WHERE id_human NOT IN ( 

       SELECT id_human 

       FROM human

       WHERE dept IN (1, 2)); 

Можно придумать еще множество красивых и хитроумных способов решения этой задачи, но я не знаю ни одного способа, как это сделать сочетанием классических логических операторов OR, AND, NOT и операторов IN и NOT IN.

Главное при написании запросов не забывать про значения NULL, обязательно обрабатывать эти ситуации, даже если, казалось бы, и так все работает нормально.

Будьте бдительны!