SQL NOT IN и NULL: почему запрос не вернул ни одной строки

Нужно выбрать клиентов, которых нет в списке блокировки. В таблице клиентов четыре человека, заблокирован только один. Но запрос с NOT IN возвращает пустоту. Частая причина прячется в правой части условия: туда попало NULL. Посмотрим на небольшом наборе строк, почему SQL не считает остальные идентификаторы «не заблокированными» и как переписать запрос по смыслу задачи.

Воспроизведите странный результат

Этот фрагмент запускается в SQLite. Клиент 2 заблокирован; в таблице блокировок есть ещё запись без указанного клиента.

CREATE TABLE clients (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);
CREATE TABLE blocked (
  client_id INTEGER
);
INSERT INTO clients(id, name) VALUES
  (1, 'Аня'), (2, 'Борис'), (3, 'Вера'), (4, 'Глеб');
INSERT INTO blocked(client_id) VALUES (2), (NULL);

SELECT id, name
FROM clients
WHERE id NOT IN (SELECT client_id FROM blocked)
ORDER BY id;

Последний SELECT вернёт ноль строк. Для Ани проверка похожа на 1 NOT IN (2, NULL). Сравнение 1 = 2 ложно, а 1 = NULL даёт неизвестный результат. Поэтому нельзя уверенно сказать, что единицы нет среди значений справа: весь NOT IN тоже даёт неизвестно. WHERE оставляет только строки, для которых условие истинно. Тот же механизм затрагивает Веру и Глеба; для Бориса результат просто ложен, потому что 2 найдена.

Прежде чем менять основной запрос, посмотрите на подзапрос отдельно: SELECT client_id FROM blocked покажет 2 и пустое значение. Чтобы оценить масштаб проблемы в рабочей таблице, выполните SELECT COUNT(*) FROM blocked WHERE client_id IS NULL. В нашем наборе счётчик равен 1. Если пустые ключи появились после загрузки, стоит выяснить, почему они вообще записываются: фильтр исправит выдачу, но не источник ошибки данных.

Так вы отличите ошибку логики запроса от неожиданного состояния таблицы и сможете исправить оба уровня отдельно.

Выберите исправление по смыслу таблицы

Если пустой client_id в блокировках означает плохую запись, отберите известные идентификаторы внутри подзапроса:

SELECT id, name
FROM clients
WHERE id NOT IN (
  SELECT client_id FROM blocked WHERE client_id IS NOT NULL
)
ORDER BY id;

Теперь результат — Аня, Вера и Глеб: идентификаторы 1, 3, 4. Вы исключили неизвестные значения до сравнения. Но если правило звучит «оставить клиента, для которого не существует строки блокировки», удобнее выразить его напрямую:

SELECT c.id, c.name
FROM clients AS c
WHERE NOT EXISTS (
  SELECT 1
  FROM blocked AS b
  WHERE b.client_id = c.id
)
ORDER BY c.id;

Этот запрос тоже возвращает 1, 3, 4. Для каждого клиента подзапрос ищет совпадающую строку. Запись с NULL не совпадает ни с одним известным c.id, поэтому не блокирует всех сразу. У EXISTS вопрос о наличии строки, а не о значении в списке, которое потом придётся сравнивать с каждым клиентом. Фильтр IS NOT NULL и NOT EXISTS совпали на нашем наборе, но выражают разные решения о данных: первое очищает список, второе проверяет существование связи.

Не забудьте о NULL слева

У клиентов id — первичный ключ, поэтому он заполнен. В реальном запросе слева часто стоит необязательный внешний ключ: например, orders.client_id. Если у заказа он равен NULL, условие NOT EXISTS (… WHERE b.client_id = orders.client_id) окажется истинным: совпадения не найдётся. Такой заказ попадёт в выборку «клиенты без блокировки», хотя клиент у него вообще не определён.

Если нужны только заказы известных незаблокированных клиентов, добавьте orders.client_id IS NOT NULL во внешний WHERE. Если же незаполненный клиент — отдельная категория отчёта, выведите её отдельно и решите, кто исправляет данные. Замена NOT IN на NOT EXISTS устраняет ловушку с NULL справа, но не решает за вас смысл пустого ключа слева.

Похожие ошибки в аналитике возникают, когда запрос считает правильную формулу на неподходящих строках. Посмотрите подборку разборов SQL-отчётов; если после исправления результата важна скорость, отдельный пример показывает, как читать план SQLite для составного индекса.

Продолжение для сложных запросов

В курсе «Продвинутый SQL: сложные запросы и задачи для собеседований» есть урок «NOT EXISTS и NOT IN при наличии NULL». Дальше вы разбираете дубли после JOIN, исторические значения, оконные расчёты и сверку отчётов, а запросы проверяются на разных наборах данных, включая пропуски и пустые выборки. Откройте программу и пробный урок, чтобы тренировать такие решения на SQL-задачах, а не только узнавать ошибку по одному примеру.