Как обезопасить NOT IN от NULL-ловушки

Условие

Как убрать риск «NULL-ловушки» в выражении поиска вида:

WHERE id NOT IN (SELECT parent_id FROM tree)

• A) NOT EXISTS (SELECT 0 FROM tree WHERE parent_id = t.id)
• B) Добавить DISTINCT в подзапрос.
• C) Заменить NOT IN на <> ALL.
• D) Просто индексировать parent_id.

Решение

Правильный ответ: A — переписать через NOT EXISTS.

Что такое «NULL-ловушка» в NOT IN

NOT IN (...) разворачивается в <> ALL (...). Если в подзапросе есть хотя бы один NULL, то id <> NULL = UNKNOWN. Логическое AND с UNKNOWN не может стать TRUE → весь NOT IN возвращает UNKNOWN → строка не попадает в результат.

Итог: если в parent_id есть хотя бы один NULL, NOT IN вернёт пустоту, что почти всегда не то, чего ожидает разработчик.

-- Покажет 0 строк, если в tree.parent_id есть хотя бы один NULL
SELECT id FROM nodes
WHERE id NOT IN (SELECT parent_id FROM tree);

Почему NOT EXISTS решает проблему

NOT EXISTS проверяет наличие строки, удовлетворяющей условию. NULL = NULL в WHERE подзапроса даёт UNKNOWN → не считается совпадением → строка не «исключается». То есть строка с id = X попадёт в результат, если в tree нет ни одной строки с parent_id = X (а строки с parent_id IS NULL просто игнорируются, как и должны).

-- Корректно отфильтрует
SELECT n.id FROM nodes n
WHERE NOT EXISTS (
    SELECT 0 FROM tree t WHERE t.parent_id = n.id
);

Почему другие варианты не работают

• B DISTINCT. Уберёт дубли, но NULL останется. Проблема не в дублях.
• C <> ALL. Это то же самое, что NOT IN. Эквивалентная форма с той же ловушкой.
• D Индекс. Это про скорость, а не про корректность результата.

Альтернативы

-- Вариант 2: явно отфильтровать NULL-ы
WHERE id NOT IN (SELECT parent_id FROM tree WHERE parent_id IS NOT NULL)
 
-- Вариант 3: LEFT JOIN ... IS NULL
SELECT n.id FROM nodes n
LEFT JOIN tree t ON t.parent_id = n.id
WHERE t.parent_id IS NULL;

Подводные камни

1. IN тоже подвержен NULL-эффекту, но менее опасно: id IN (1, NULL) = TRUE для id = 1, UNKNOWN для остальных. То есть IN ведёт себя «дружелюбнее».
2. NOT EXISTS vs NOT IN по производительности. На больших данных NOT EXISTS обычно быстрее (anti-semi-join), а NOT IN материализует список.
3. Считать, что WHERE parent_id IS NOT NULL достаточно для фикса. Это работает, но не показывает структурную причину; код-ревьюер может пропустить, что фильтр критичен.

Эталонный ответ

A) NOT EXISTS (SELECT 0 FROM tree WHERE parent_id = t.id) — корректно обрабатывает NULL-ы и обычно быстрее на больших таблицах.