Порой, очевидные вещи таят в себе удивительные сюрпризы. Казалось бы, простые 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, обязательно обрабатывать эти ситуации, даже если, казалось бы, и так все работает нормально.
Будьте бдительны!
Комментарии (17)
qw1
17.08.2025 15:32Можно нормализовать NULL например в (-1)
WHERE coalesce(dept,-1) NOT IN (1,2);Не будет работать индекс на dept, если он есть, но в запросе NOT IN индекс и не должен использоваться.
topharley
17.08.2025 15:32Если индекс сделать прям вот по этой же функции coalesce(dept,-1) и искать по ней, то работать будет
shich
17.08.2025 15:32Я наткнулся на это довольно давно и больше скажу, мне попадались СУБД, где NULL==NULL. Возможно это была MSSQL
zVlad909
17.08.2025 15:32Это наверное потому что MSSQL тихой сапой допускает что NULL это бланк, пробел. Что не есть так.
Tzimie
17.08.2025 15:32Это не так. Вы путаете с Oracle
В MSSQL есть устаревший режим set ansi nulls off где можно сравнивать null на равенство
zVlad909
17.08.2025 15:32Хорошо, спасибо за уточнение.
Я не с MS SQL ни с Оракл всерьез не занимаюсь, но поддерживаю реприкацию из Оракл в MS SQL и наш DBA по этим базам мне то и дело показывает как что-то работает вот так вот тут и не так вот там. Про set ansi nulls off он тоже вспоминается говорил. Лично я считают это не допустимо. Подход к NULL должен быть един в БД, а не на выбор. Это приводит к плохим последствиям в использовании приложений.
Всерьез я работаю только с DB2 for z/OS и у меня никогда не возникало проблем и недопониманий по поводу темы статьи. Все очень четко и однозначно.
LeshaRB
17.08.2025 15:32А так чего не сделать ?
... WHERE dept not IN (1, 2) or dept is nullЗачем вложенный запрос
alexmib
17.08.2025 15:32"Для человека, не изучавшего в школе физику, мир полон чудес" ;-)
Документация:
https://www.postgresql.org/docs/17/functions-comparisons.html
9.2. Comparison Functions and Operators
Do not write
expression= NULLbecauseNULLis not “equal to”NULL. (The null value represents an unknown value, and it is not known whether two unknown values are equal.)https://www.postgresql.org/docs/17/functions-comparisons.html
9.25.2.
NOT INNote that if the left-hand expression yields null, or if there are no equal right-hand values and at least one right-hand expression yields null, the result of the
NOT INconstruct will be null, not true as one might naively expect. This is in accordance with SQL's normal rules for Boolean combinations of null values.Tip
x NOT IN yis equivalent toNOT (x IN y)in all cases. However, null values are much more likely to trip up the novice when working withNOT INthan when working withIN. It is best to express your condition positively if possible.
zVlad909
17.08.2025 15:32Дело в том, что мы, опять-таки, забыли про трехзначную логику. У логических операторов не два, а три результата, а именно: ИСТИНА, ЛОЖЬ и НЕ ЗНАЮ.
Это интересно в каких логических операторах результат может быть "НЕ ЗНАЮ". Я таких не знаю. Это может быть в операторах сравнения если сравниваемые значения содержат NULL может получиться "НЕ ЗНАЮ", но строки с такими значениями вообще то исключаются до самих сравнений. Потому что значения NULL не сравниваемые ни с чем. Даже друг с другом.
hkm2
17.08.2025 15:32В любых. Даже очевидный
(not nulled_expression) is nullв результате дает true. Так же как иtrue_expression and nullиfalse_experssion or nullв результате дают null aka boolean unknown. И строки выборки исключаются после вычисления условий их отбора, а не до - заглядывать в будущее даже оптимизаторы SQL пока не умеют.
zVlad909
17.08.2025 15:32Дело в том, что значение NULL следует воспринимать не как отсутствие значения, а как потенциально любое значение, или, другими словами, неизвестное значение. Увы, в теории баз данных это общепринятая концепция, не зависящая от того, как лично вы (или пользователи-предметники) интерпретируют отсутствующее значение.
Если бы это (выделеное жирным) было бы так то тогда 2 = NULL давало бы YES. И 2 < NULL тоже и вообще все старнения с NULL были бы положительными, но это не так.
"Неизвестное" и "любое" это не одно и тоже.
Кстати по этому существуют два специальных оператора сравнения: IS NULL, and IS NOT NULL. Во всех остальных предикатах строки содержащие NULL в колонках участвующих в этих предикатах просто отбрасываются.
Вот как это объясненно в доках DB2 for z/OS (на мой взгляд наиболее полное и корректное описание. Возможно не совпадающее с некоторыми дркгими РСУБД, например MS SQL):
В DB2 для z/OS концепции IN, NOT IN и NULL являются основополагающими для построения SQL-запросов и обработки данных. Значения NULL:
NULL представляет неизвестное или отсутствующее значение в столбце. Оно не эквивалентно нулю, пустой строке или любому другому определённому значению.
Столбцы могут быть определены так, чтобы разрешать или запрещать значения NULL. Если столбец определён как NOT NULL, он не может содержать значения NULL. Столбцы первичного ключа всегда должны быть определены как NOT NULL.
-
Значения NULL требуют особой обработки при сравнении. Стандартные операторы сравнения (=, <, >) не работают с NULL. Вместо этого для проверки наличия или отсутствия значения NULL используются предикаты IS NULL и IS NOT NULL.
Предикат IN:
Предикат IN используется для проверки соответствия значения выражения любому значению в списке значений или результирующем наборе подзапроса. Синтаксис: выражение IN (значение1, значение2, ...) или выражение
IN (подзапрос). Пример:
SELECT EMPNO, LASTNAME FROM EMPLOYEE WHERE JOB IN ('CLERK', 'ANALYST');Предикат NOT IN:
Предикат NOT IN используется для проверки того, не совпадает ли значение выражения ни с одним значением в списке значений или результирующем наборе подзапроса. Синтаксис: выражение NOT IN (значение1, значение2, ...) или выражение NOT IN (подзапрос)
Важное примечание с NULL:
Если список значений или результирующий набор подзапроса для NOT IN содержит значение NULL, предикат NOT IN всегда будет иметь значение unknown (false), даже если значение выражения явно не равно ни одному из ненулевых значений. Это связано с тем, что NULL нельзя сравнить ни с одним значением с помощью стандартных операторов сравнения, и, следовательно, его отсутствие в списке не может быть однозначно подтверждено.
Пример (демонстрирующий проблему с NULL):
SELECT EMPNO, LASTNAME FROM EMPLOYEE WHERE JOB NOT IN ('CLERK', 'ANALYST', NULL);Скорее всего, это не вернет ни одной строки, если JOB может быть NULL. Чтобы корректно исключить значения NULL или обработать их явно, IS NOT NULL следует использовать вместе с NOT IN, или подзапрос должен отфильтровывать значения NULL.
Обработка значений NULL с помощью IN/NOT IN: При использовании IN или NOT IN со столбцами, которые могут содержать значения NULL, рассмотрите возможность фильтрации значений NULL в подзапросе или использования IS NOT NULL в предложении WHERE, чтобы избежать непредвиденных результатов, особенно с NOT IN. Исправленный пример для NOT IN с NULL:
SELECT EMPNO, LASTNAME FROM EMPLOYEE WHERE JOB NOT IN ('CLERK', 'ANALYST') AND JOB IS NOT NULL;
urvanov
17.08.2025 15:32Там у NULL все операции работают по-особенному. Именно поэтому и нужно всегда IS NULL проверять его, а не другим способом. Как тут уже выше писали, мир полон физики, если не знаешь магию. В статье хорошее напоминание об особенностях NULL, я считаю. Повторить пойдёт.
Politura
Конкретно в этом случае, красивый и хитроумный способ это сделать поле department not null, чтоб нельзя было завести сотрудника вне какого-либо отдела. Да и в целом, необходимость null значений бывает довольно редко и каждый раз означает денормализацию данных. Так-то без денормализации далеко не всегда можно обойтись, но в общем это неклевая штука в реляционных базах данных, нужно четко понимать зачем именно ее добавляешь.
zVlad909
Хочется задать вопросы.
1. Каким образом использоваение NULL связано с нормализацией (равно как и с денормализацией)?
2. Интересный ход мысли отталкивающийся от странной предпосылки что денормализация это нечто желаемое. И не очень понятно что, нормализацию или денормализацию, надо понимать зачем добавлять. Поясните свою мысль, пожалуцйста.
Politura
В полностью нормализованной базе null-ов просто не будет. В примере из статьи, вместо того, чтоб добавлять departmentId null поле, нормализацией будут две таблицы, типа:
Но, такой вариант приведет к потере производительности когда нам надо работать с сотрудниками, а не с персонами, из-за дополнительного джойнта в селекте.
Жаль, я вовсе не хотел чтоб сложилось впечатление, что денормализация это нечто желаемое. И даже изначально написал, что денормализация это "неклевая штука". Наверное слишком я старый и слова "клевый" и "неклевый" уже непонятные стали, тогда поясню понятными словами: На мой взгляд, надо в первую очередь стремиться к нормализации. Но очень часто в целях повышения производительности приходится делать денормализацию в некоторых местах.