Средний

6 мин

9.7K

Привет, Хабр!

В SQL есть отдельный класс ошибок, неприятных именно тем, что они никак себя не проявляют: база не выдаёт исключения, не пишет предупреждения — запрос просто возвращает не то, что нужно. Чаще всего это пустой результат там, где вы ожидаете десятки строк. Один из самых распространённых случаев — NOT IN в сочетании с NULL.

Возьмём задачу: найти сотрудников, которые никем не руководят. В таблице employees есть поле manager_id — ссылка на начальника. Рассуждение простое: руководитель — это тот, кто хотя бы раз встречается в manager_id, а значит, не руководители — все, кого там нет. Запрос пишется почти сам собой:

SELECT name
FROM employees
WHERE id NOT IN (SELECT manager_id FROM employees);

Вы запускаете его и получаете ноль строк, хотя точно знаете: обычных сотрудников в компании большинство, и в выборку они обязаны попасть.

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

Дальше разберём, почему NOT IN ломается из‑за одного‑единственного NULL, почему IN рядом продолжает работать корректно и почему NOT IN, вопреки названию, не противоположность IN.

Пример

Пять сотрудников, одна простая иерархия:

CREATE TABLE employees (
    id         int PRIMARY KEY,
    name       text,
    manager_id int
);

INSERT INTO employees VALUES
    (1, 'Алексей', NULL),   -- директор, начальника нет
    (2, 'Борис',   1),
    (3, 'Вера',    1),
    (4, 'Глеб',    2),
    (5, 'Дина',    2);

Кто здесь руководитель? Тот, на кого ссылаются. В колонке manager_id встречаются 1 и 2 — значит, руководят Алексей и Борис. Остальные трое — Вера, Глеб и Дина — никем не командуют. Их мы и хотим получить.

SELECT name FROM employees
WHERE id NOT IN (SELECT manager_id FROM employees);

 name
------
(0 rows)

Ноль. Не Вера с Глебом и Диной, а пусто. Притом что IN на тех же данных отрабатывает безупречно:

SELECT name FROM employees
WHERE id IN (SELECT manager_id FROM employees);

 name
---------
 Алексей
 Борис
(2 rows)

Разворачиваем NOT IN

Подзапрос SELECT manager_id FROM employees возвращает набор значений: NULL, 1, 1, 2, 2. Тот самый NULL — от Алексея, у которого начальника нет.

Теперь развернём NOT IN в то, чем он работает под капотом. x IN (a, b, c) — это сокращение для x = a OR x = b OR x = c. Соответственно, x NOT IN (a, b, c) — это NOT (x = a OR x = b OR x = c), по законам логики — x <> a AND x <> b AND x <> c.

Цепочка неравенств через AND. Подставим наш набор и проверим Веру, у неё id = 3:

3 <> NULL   AND   3 <> 1   AND   3 <> 2

3 <> 1 — это TRUE. 3 <> 2 — тоже TRUE. А 3 <> NULL?

В SQL NULL — не значение, а неизвестно. Сравнить с ним нельзя ничего, и 3 <> NULL не вернёт ни TRUE, ни FALSE — только UNKNOWN, третий результат трёхзначной логики.

Складываем цепочку: TRUE AND TRUE AND UNKNOWN. И весь результат — UNKNOWN.

А WHERE пропускает дальше только строгое TRUE. UNKNOWN он выбрасывает наравне с FALSE. Вера вылетает. Глеб и Дина — по той же схеме: у них в цепочке такой же <> NULL, и каждая цепочка снова сваливается в UNKNOWN. Вылетают все.

Заметьте, настоящие руководители — Алексей и Борис — отсеялись бы и так, и это честно: у Алексея в цепочке есть 1 <> 1, это FALSE, а FALSE AND что угодно даёт FALSE. А вот не‑руководители, которые должны были пройти, спотыкаются именно об UNKNOWN. Один NULL в подзапросе сделал так, что NOT IN не способен вернуть TRUE ни для кого. Отсюда и ноль строк.

И подзапрос для этого не нужен — достаточно списка вручную:

SELECT name FROM employees WHERE id NOT IN (3, 4, NULL);  -- 0 строк

Один NULL в скобках — и весь NOT IN отравлен.

Самое скверное тут даже не в логике, а в поведении со стороны.

База не падает, ошибку не кидает. С её точки зрения всё стерильно: трёхзначная логика отработала строго по стандарту. Запрос вернул результат — просто пустой или подозрительно куцый. А это глазами ловится в разы хуже, чем честный ERROR в консоли. И ведёт себя так не только PostgreSQL — это поведение стандарта SQL.

NULL в таких колонках — норма. У директора нет начальника. У товара может не быть категории, у события — связанного заказа. NULL в manager_id не баг, который кто‑то однажды вычистит. Это валидные данные, они там навсегда. Хватит одной такой строки на миллион — и любой NOT IN по этой колонке отравлен.

Чем лечить: NOT EXISTS

Надёжное лекарство — NOT EXISTS с коррелированным подзапросом:

SELECT e.name
FROM employees e
WHERE NOT EXISTS (
    SELECT 1
    FROM employees sub
    WHERE sub.manager_id = e.id
);

 name
------
 Вера
 Глеб
 Дина
(3 rows)

Те самые трое. EXISTS устроен иначе, в этом вся соль, он не сравнивает значения, а отвечает на один вопрос — нашлась хоть одна строка или нет. Ответ двузначный, да или нет, третьего не дано. NULL внутри подзапроса ему не страшен: строка Алексея с manager_id = NULL просто не совпадёт с e.id в условии sub.manager_id = e.id и не попадёт в подсчёт. Никакого UNKNOWN, который полез бы наружу.

Второй крепкий вариант — антиджойн через LEFT JOIN:

SELECT e.name
FROM employees e
LEFT JOIN employees sub ON sub.manager_id = e.id
WHERE sub.id IS NULL;

Тоже отдаёт Веру, Глеба и Дину, тоже не боится NULL.

Можно ли спасти сам NOT IN? Можно — выкинуть NULL из подзапроса руками:

SELECT name FROM employees
WHERE id NOT IN (
    SELECT manager_id FROM employees WHERE manager_id IS NOT NULL
);

Сработает, но это костыль на честном слове: держится на том, что вы помните про NULL и не забыли дописать фильтр. В следующий раз забудете и снова получите тихую пустоту. NOT EXISTS помнить ничего не просит.

Как поймать это у себя

Подозреваете NOT IN по конкретной колонке — спросите базу, есть ли там NULL:

SELECT count(*) FROM employees WHERE manager_id IS NULL;

Ноль — NOT IN по этой колонке пока безопасен. Больше нуля — он уже отравлен, и результат запроса вам врёт.

Также стоит пройтись поиском по NOT IN (SELECT — почти каждое такое место кандидат на разбор. Колонка в подзапросе объявлена как NOT NULL? Тогда NOT IN безопасен, его можно оставить. Колонка NULL — переписывайте на NOT EXISTS, не дожидаясь, пока туда заедет первый NULL.

NOT IN — это не «IN наоборот»

Остался вопрос с начала: почему IN выжил, а NOT IN рухнул на том же NULL?

Потому что они по‑разному дружат с UNKNOWN. IN раскрывается в цепочку OR. Для руководителя это TRUE OR ... OR UNKNOWN — а OR хватает одного TRUE, чтобы выдать TRUE, остальное не важно. UNKNOWN в цепочке OR просто тонет.

NOT IN раскрывается в цепочку AND. А AND к UNKNOWN беспощаден: одного UNKNOWN достаточно, чтобы увести весь результат из TRUE в UNKNOWN. Та же NULL‑строка, которую OR не заметил, для AND оказывается ядом.

Эта несимметрия и подводит. IN работает — и кажется само собой разумеющимся, что NOT IN просто его зеркало. Не зеркало.

Вернёмся к нулю строк из начала. Ваши не‑руководители никуда не пропадали, их отравил единственный NULL в manager_id, переведя условие из TRUE в UNKNOWN, и WHERE молча вынес всех за дверь.

В итоге NOT IN — не противоположность IN, как нашёптывает название. Под трёхзначной логикой это две разные операции с разным характером, и NULL ломает только одну. Рабочее правило короткое: исключаете что‑то по подзапросу — берите NOT EXISTS по умолчанию, не задумываясь. А NOT IN оставьте для захардкоженных списков, про которые точно знаете — NULL там нет и не будет.

В статье мы разобрали, как один NULL может превратить корректный на вид NOT IN в тихую логическую ловушку: запрос не падает, но возвращает не тот результат. Если хотите увереннее работать со сложной SQL‑логикой и лучше понимать, почему база ведёт себя именно так, обратите внимание на два бесплатных открытых урока OTUS:

• 21 мая, 20:00 — «SQL: Обобщенное табличное выражение (CTE) — как писать сложные запросы просто».
  _{На уроке покажем, как CTE помогают раскладывать сложную SQL‑логику на понятные шаги, чтобы запросы было проще читать, проверять и сопровождать.}

• 2 июня, 20:00 — «Ты — индекс в Postgres, Я — индекс в ClickHouse. Мы — разные».
  _{Поговорим о том, почему похожие на первый взгляд механики в разных СУБД работают по‑разному, и как это учитывать при работе с запросами и данными.}

Больше бесплатных открытых уроков по аналитике, базам данных и не только можно найти в календаре мероприятий OTUS.