Анти-джойны: NOT EXISTS, NOT IN, LEFT JOIN IS NULL

Найти строки, у которых НЕТ пары: NOT EXISTS, NOT IN и LEFT JOIN ... IS NULL.

Анти-джойн — выборка строк одной таблицы, для которых нет соответствия в другой. Три способа делают одно, но ведут себя по-разному с NULL и по скорости.

Задача

Найдём клиентов, у которых нет ни одного заказа. Это и есть анти-джойн: «строки слева без пары справа». Покажем три канонических способа.

Способ 1: LEFT JOIN ... IS NULL

Делаем LEFT JOIN и оставляем строки, где правая часть оказалась NULL — это и значит «пары не нашлось».

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO customers (id, name) VALUES (1,'Аня'), (2,'Борис'), (3,'Вера');

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER);
INSERT INTO orders (customer_id) VALUES (1), (1), (2);

SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL
ORDER BY c.name;

Вывод:

Вера

Только Вера — у неё нет заказов, поэтому после LEFT JOIN правые колонки NULL.

Способ 2: NOT EXISTS

Самый надёжный и обычно эффективный. Для каждого клиента проверяем: «не существует ли заказа с его id».

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO customers (id, name) VALUES (1,'Аня'), (2,'Борис'), (3,'Вера');

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER);
INSERT INTO orders (customer_id) VALUES (1), (1), (2);

SELECT c.name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id
)
ORDER BY c.name;

Вывод:

Вера

Тот же результат — Вера. NOT EXISTS корректно работает даже при NULL в данных и часто оптимизируется лучше всего.

Способ 3: NOT IN — и его опасность

NOT IN читается проще всего, но это ловушка: если в подзапросе встретится NULL, результат станет пустым (см. урок про трёхзначную логику). Здесь данные без NULL, поэтому работает:

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO customers (id, name) VALUES (1,'Аня'), (2,'Борис'), (3,'Вера');

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER);
INSERT INTO orders (customer_id) VALUES (1), (1), (2);

SELECT c.name
FROM customers c
WHERE c.id NOT IN (SELECT customer_id FROM orders)
ORDER BY c.name;

Вывод:

Вера

Снова Вера. Но стоит подзапросу вернуть хоть один NULL в customer_id — и весь NOT IN вернёт пусто. Поэтому в проде предпочитают NOT EXISTS.

Сравнение

Итог

• Все три способа выражают анти-джойн «строки без пары».
• NOT EXISTS — NULL-безопасен и обычно эффективнее всего.
• NOT IN ломается при NULL в подзапросе — будьте осторожны.