NOT EXISTS и NOT IN при наличии NULL

Логика антисоединения и шаблон NOT EXISTS

В реляционных базах данных часто требуется отобрать объекты, у которых отсутствуют связанные события заданного типа: клиентов без покупок, пользователей без авторизаций или заказы без возвратов. Такая операция называется антисоединением. В отличие от обычного соединения, антисоединение возвращает строки первой таблицы только тогда, когда во второй таблице не найдено ни одной подходящей записи.

Главное свойство антисоединения — сохранение исходной гранулярности левой таблицы. Каждая строка родительской таблицы либо входит в итоговую выборку ровно один раз, либо не входит вовсе. Если у одного клиента есть десять платежей, антисоединение не размножит строку родительской таблицы, а просто исключит её из результата.

Для проверки работы антисоединения подготовим две таблицы: список клиентов и журнал платежей.

CREATE TABLE clients (
    client_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE payments (
    payment_id INTEGER PRIMARY KEY,
    client_id INTEGER,
    amount INTEGER NOT NULL,
    status TEXT NOT NULL
);

INSERT INTO clients (client_id, name) VALUES
(1, 'Алиса'),
(2, 'Борис'),
(3, 'Вера'),
(4, 'Глеб');

INSERT INTO payments (payment_id, client_id, amount, status) VALUES
(101, 1, 500, 'success'),
(102, 1, 1200, 'success'),
(103, NULL, 300, 'pending'),
(104, 4, 800, 'failed');

В таблице payments у Алисы два успешных платежа, у Глеба — один со статусом 'failed', у платежа 103 клиент не указан (NULL), а у Бориса и Веры платежей нет.

Базовый шаблон антисоединения строится с помощью предиката NOT EXISTS и коррелированного подзапроса:

SELECT c.client_id, c.name
FROM clients c
WHERE NOT EXISTS (
    SELECT 1
    FROM payments p
    WHERE p.client_id = c.client_id
      AND p.status = 'success'
)
ORDER BY c.client_id;

Результат выполнения:

client_id	name
2	Борис
3	Вера
4	Глеб

Предикат NOT EXISTS истинен тогда и только тогда, когда коррелированный подзапрос возвращает ровно 0 строк. Проекция подзапроса (SELECT 1 или SELECT *) никак не влияет на проверку: движок базы данных проверяет факт наличия строк, а не их значения. Для Алисы найдено две строки со статусом 'success', поэтому подзапрос вернул непустое множество, и NOT EXISTS дал FALSE. Для Бориса, Веры и Глеба подзапрос вернул 0 строк, предикат вернул TRUE, и они вошли в результат с сохранением своего первичного ключа.

Трёхзначная логика NOT IN и потеря строк при NULL

Для поиска отсутствующих элементов часто пытаются применить предикат NOT IN. Однако при наличии значений NULL во вложенной выборке этот предикат приводит к полной потере данных во внешнем запросе.

Рассмотрим следующий запрос, целью которого является поиск клиентов без единого платежа:

SELECT c.client_id, c.name
FROM clients c
WHERE c.client_id NOT IN (
    SELECT p.client_id
    FROM payments p
)
ORDER BY c.client_id;

Этот запрос возвращает 0 строк. Ни Борис, ни Вера в результат не попадают, хотя их идентификаторов нет в таблице платежей.

Причина кроется в правилах трёхзначной логики SQL. Подзапрос SELECT p.client_id FROM payments возвращает набор значений: {1, 1, NULL, 4}. Конструкция вида val NOT IN (list) разворачивается СУБД в цепочку условий с оператором <> через логическое AND:

c.client_id <> 1 AND c.client_id <> 1 AND c.client_id <> NULL AND c.client_id <> 4

Когда проверяется Борис (client_id = 2), СУБД вычисляет каждое сравнение:

• 2 <> 1 возвращает TRUE
• 2 <> NULL возвращает UNKNOWN
• 2 <> 4 возвращает TRUE

Итоговое выражение принимает вид TRUE AND TRUE AND UNKNOWN AND TRUE. Результатом конъюнкции, содержащей UNKNOWN и ни одного FALSE, является UNKNOWN. Фильтр WHERE пропускает только те строки, для которых условие строго равно TRUE. Значение UNKNOWN отбрасывается. В результате одиночный NULL в дочерней таблице делает истинный ответ для NOT IN математически невозможным для любых внешних строк.

Сравнение NOT EXISTS и NOT IN на пустых и частичных выборках

Чтобы безопасно выбирать между NOT EXISTS и NOT IN, необходимо различать две пограничные ситуации: когда подзапрос возвращает ноль строк и когда подзапрос возвращает одну строку со значением NULL.

Если подзапрос возвращает 0 строк (пустое множество), оба предиката ведут себя одинаково. Проверим случай, когда мы ищем клиентов без платежей со статусом 'refunded'. Таких платежей в базе нет вовсе:

SELECT c.client_id, c.name
FROM clients c
WHERE c.client_id NOT IN (
    SELECT p.client_id
    FROM payments p
    WHERE p.status = 'refunded'
)
ORDER BY c.client_id;

Результат выполнения:

client_id	name
1	Алиса
2	Борис
3	Вера
4	Глеб

В этом случае подзапрос вернул 0 строк. В пустом наборе нет ни одного элемента, равного client_id, поэтому предикат IN возвращает FALSE, а NOT IN возвращает TRUE для каждого клиента. Аналогично EXISTS на пустом множестве возвращает FALSE, а NOT EXISTS — TRUE.

Разница возникает, как только в подзапросе появляется хотя бы одна строка. Если эта строка содержит NULL, то:

• Подзапрос для NOT IN возвращает непустое множество {NULL}. Предикат превращается в val <> NULL, даёт UNKNOWN и отсекает всю выборку.
• В подзапросе для NOT EXISTS условие связи p.client_id = c.client_id при p.client_id IS NULL даёт NULL = c.client_id, что вычисляется как UNKNOWN. Строка с NULL не проходит фильтр WHERE подзапроса. Подзапрос остаётся пустым (0 строк), и NOT EXISTS возвращает TRUE.

Пустой результат подзапроса безопасен для обоих предикатов, но появление строки со значением NULL нарушает работу только предиката NOT IN.

Практический разбор: поиск клиентов без платежей с нераспознанными записями

Сверим поведение запросов на подготовленной базе clients и payments. Задача — вывести клиентов, у которых нет вообще никаких платежей, независимо от их статуса.

В таблице payments строка 103 имеет client_id IS NULL — это нераспознанный платёж без привязки к клиенту. Попытка решить задачу через NOT IN без дополнительной обработки даёт пустую таблицу, как было показано ранее. Чтобы NOT IN отработал корректно, разработчик обязан вручную исключить неопределённые значения во вложенном запросе:

SELECT c.client_id, c.name
FROM clients c
WHERE c.client_id NOT IN (
    SELECT p.client_id
    FROM payments p
    WHERE p.client_id IS NOT NULL
)
ORDER BY c.client_id;

Результат выполнения:

client_id	name
2	Борис
3	Вера

Подзапрос вернул значения {1, 4}. Для Бориса (id = 2) и Веры (id = 3) условие c.client_id NOT IN (1, 4) вычисляется в TRUE, так как неопределённых элементов больше нет.

Теперь выполним ту же задачу с помощью NOT EXISTS без ручной фильтрации IS NOT NULL:

SELECT c.client_id, c.name
FROM clients c
WHERE NOT EXISTS (
    SELECT 1
    FROM payments p
    WHERE p.client_id = c.client_id
)
ORDER BY c.client_id;

Результат выполнения:

client_id	name
2	Борис
3	Вера

Оба корректных варианта вернули одинаковые строки: Бориса и Веру. Однако NOT EXISTS устойчив к наличию нераспознанных записей по своей структуре: строка платежа с client_id = NULL не совпадает ни с одним c.client_id при вычислении условия связи и не ломает логику для остальных записей. Кроме того, наличие двух платежей у Алисы не потребовало добавления DISTINCT, так как NOT EXISTS завершает проверку строки, как только обнаружено первое совпадение.