← Все курсы · Курс SQL

Анти-соединение в SQL: NOT EXISTS vs NOT IN vs LEFT JOIN IS NULL

Майкл Носито · Обновлено в августе 2026 г. · Каждый запрос на этой странице был выполнен до его публикации.

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

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

Краткая версия. Анти-соединение сохраняет строки, которые ничего не нашли. NOT EXISTS это безопасный способ спросить.

Две таблицы, на которых выполняются примеры

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

CREATE TABLE customers(customer_id INT, name TEXT, city TEXT);
INSERT INTO customers VALUES
(1,'Alvarez','North'),(2,'Brennan','North'),(3,'Cho','South'),(4,'Diaz','South'),
(5,'Ellis','North'),(6,'Fowler','South'),(7,'Grant','North'),(8,'Hale','South');

CREATE TABLE orders(order_id INT, customer_id INT, amount INT, status TEXT);
INSERT INTO orders VALUES
(101,1,120,'completed'),(102,1, 80,'completed'),(103,2, 50,'cancelled'),
(104,3,300,'completed'),(105,5, 90,'completed'),(106,5, 40,'cancelled'),
(107,8,200,'completed'),(108,NULL,60,'completed');

Прочитайте строки заказа и ответ там. Появляются клиенты 1, 2, 3, 5 и 8. Клиенты 4, 6 и 7 этого не делают, то есть Диас, Фаулер и Грант. Так что правильный ответ на вопрос «кто никогда не заказывал» — три человека.

Еще одна вещь, на которую следует обратить внимание, потому что вся страница посвящена этому: заказ 108 имеет NULL клиент. Веб-форма пропустила его, или при импорте был удален ключ. Это не надуманный скандал. Таблицы поиска и столбцы внешних ключей на реальных складах хранят NULL все время.

НЕ СУЩЕСТВУЕТ, тот, который используется по умолчанию

Перед запросом: сколько строк должно быть возвращено?

Три, как указано выше. NOT EXISTS понимает.

SELECT c.name
FROM customers c
WHERE NOT EXISTS (SELECT 1
                  FROM orders o
                  WHERE o.customer_id = c.customer_id);
-- Diaz, Fowler, Grant

Прочитайте это вслух, и там будет написано, что это значит. Для каждого клиента найдите в заказах строку с идентификатором этого клиента и сохраните клиента, если не найдете ни одного.

тот SELECT 1 выглядит странно в первый раз. Оно там, потому что EXISTS никогда не смотрит на то, что выбрал подзапрос. Он только спрашивает, вернулась ли вообще какая-либо строка, поэтому 1 является заполнителем. Вы можете написать SELECT * или SELECT o.order_id и получите точно такой же результат и тот же план.

Условие, связывающее две таблицы, находится внутри подзапроса. Именно это делает его коррелированным подзапросом: он выполняется для каждой строки клиента, а не один раз для всего запроса. Если ваш ключ состоит из двух столбцов, оба входят туда, соединенные с помощью AND.

NOT IN, и один NULL, который его очищает.

Первое: тот же вопрос с NOT IN. В одной строке заказа есть NULL клиент. Что вы ожидаете вернуться?

SELECT name
FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders);

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

Вот механизм. NOT IN спрашивает для каждого клиента: «Отличается ли этот идентификатор от каждого значения в этом списке?» Одна из ценностей – NULL, и SQL не может сказать, отличается ли 4 от неизвестного значения. Сравнение возвращается неизвестным. Неизвестно неверно, поэтому строка не сохраняется. Это происходит с каждым клиентом, поэтому каждый клиент удаляется.

Прежде чем читать дальше, скажите, почему это делает результат пустым, а не просто неверным. Причина в том, что неизвестное сравнение происходит для всех восьми клиентов, а не только для одного заказа с отсутствующим ключом.

Вы можете исправить это, исключив NULL, и патч работает:

SELECT name
FROM customers
WHERE customer_id NOT IN (SELECT customer_id
                          FROM orders
                          WHERE customer_id IS NOT NULL);
-- Diaz, Fowler, Grant

Это работает сегодня и продолжает работать до тех пор, пока кто-нибудь не напишет тот же запрос без защиты. Это аргумент в пользу NOT EXISTS скорее как привычка, чем как спасение. Ему не нужна охрана, поэтому следующему человеку нечего забыть.

LEFT JOIN с IS NULL, старый способ

Перед запросом: LEFT JOIN удерживает каждого клиента и заполняет столбцы заказов NULL когда нет совпадения. Итак, какие клиенты имеют NULL в o.order_id после?

Трое, которые никогда не заказывали. В этом весь фокус, и он дает здесь правильный ответ.

SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;
-- Diaz, Fowler, Grant

Два правила делают эту версию честной.

Протестируйте столбец, который не может быть NULL в реальных данных. Проверьте объединенный ключ или первичный ключ другой таблицы. Если вы тестируете o.amount и для некоторых заказов действительно не указана сумма, эти клиенты присоединяются нормально и по-прежнему возвращаются как не имеющие себе равных. Запрос говорит «никогда не заказывал» и означает «никогда не заказывал или заказал с пустой суммой».

Любое условие в правой таблице принадлежит к ON, не в WHERE. Это то, что стоит людям дня. Спросите клиентов без Завершено порядок, и разница проявляется:

-- correct: the filter is part of what counts as a match
SELECT c.name
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.customer_id AND o.status = 'completed'
WHERE o.order_id IS NULL;
-- Brennan, Diaz, Fowler, Grant   (4 rows)

-- broken: the filter runs after the join has already filled in NULLs
SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.status = 'completed' AND o.order_id IS NULL;
-- 0 rows

Сломанный ничего не возвращает по причине, которую стоит сохранить. После присоединения у непревзойденного клиента появился NULL в каждом столбце заказа, поэтому o.status = 'completed' неизвестно, какие именно строки вы пытались сохранить. WHERE выбрасывает их раньше IS NULL когда-нибудь посмотрит.

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

Тот же вопрос в NOT EXISTS не требует правила размещения, поскольку условие может размещаться только в одном месте:

SELECT c.name
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o
                  WHERE o.customer_id = c.customer_id
                    AND o.status = 'completed');
-- Brennan, Diaz, Fowler, Grant

Представьте на мгновение свои два стола. Если бы вы присоединились к ним и посчитали ряды, увеличился бы счет? Если бы это было так, LEFT JOIN версия выполняет дополнительную работу, которую затем приходится отменить, это следующий раздел.

Трое рядом

Та же таблица, тот же вопрос, три ответа. Средний ряд – не маленькая разница. Это отчет, который возвращается пустым и читается как «никто не пропал», что является наиболее убедительным неправильным ответом, который может дать запрос.

Пограничные случаи, которые стоит знать

Скорость не является причиной выбора между ними. PostgreSQL и SQL Server распознают NOT EXISTS и LEFT JOIN шаблон и запускайте их как одну и ту же операцию против объединения, поэтому для индексированного ключа планы обычно совпадают. NOT IN это тот, который движок часто не может превратить в анти-соединение именно из-за NULL правило, которое оно должно уважать. Корректность и скорость здесь имеют одинаковое значение, что является редкой и приятной вещью.

Индекс объединенного столбца имеет большее значение, чем синтаксис. Какую бы форму вы ни выбрали, антиобъединение считывает вторую таблицу один раз для каждой строки-кандидата, поэтому индекс на orders.customer_id в этом разница между мгновенным и медленным на большом столе. Есть еще кое-что в Индексирование для аналитиков.

тот LEFT JOIN версия строит строки, которые нужно выбросить. Покупатель с 400 заказами создает 400 объединенных строк, все из которых отбрасываются, кроме чека. NOT EXISTS может остановиться на первом найденном совпадении. На маленьких таблицах никто этого не замечает, а на широкой таблице фактов стоит знать, какая из них выполняет больше работы.

Ключи с более чем одним столбцом работают одинаково. Поместите каждую часть в подзапрос, объединенную с помощью AND. NOT IN здесь становится сложнее, и это еще одна причина, по которой это не значение по умолчанию.

EXCEPT отвечает немного на другой вопрос. SELECT customer_id FROM customers EXCEPT SELECT customer_id FROM orders также находит недостающие идентификаторы и при этом удаляет дубликаты. Это удобно, если вам нужен только ключевой столбец. Это неудобно, когда вам нужна остальная часть строки клиента, потому что обе стороны должны иметь совпадающие столбцы. Оракул вызывает того же оператора MINUS.

Пустая вторая таблица не является крайним случаем. если orders не содержит строк, анти-соединение правильно возвращает каждого клиента. Людей удивляет обратное: NOT IN подзапрос, не возвращающий ни одной строки, сохраняет все, и это правильно, в то время как тот же подзапрос возвращает одну NULL ничего не держит.

Почему существует правило NULL

Все это не является ошибкой или особенностью движка. SQL имеет три значения истинности, а не два: Верно, Неверно и неизвестно. Любое сравнение с NULL производит неизвестное, потому что NULL означает, что значение не записано, и невозможно доказать, что ничто не равно или не равно ценности, которой ни у кого нет.

WHERE сохраняет строку только тогда, когда ее условие истинно. Неизвестное не соответствует действительности, поэтому неизвестные строки отбрасываются, причем так же незаметно, как и ложные. Это единственное правило объясняет все на этой странице: пустое NOT IN, сломанный WHERE o.status = 'completed', и причина IS NULL существует как собственный оператор, а не как = NULL.

NOT EXISTS обходит все это дело, никогда не сравнивая значения на верхнем уровне. Он спрашивает, пуст ли набор, и набор либо пуст, либо нет. Третьего варианта утечки не существует.

Как применить это в своей работе

1. Найдите по своим запросам NOT IN (SELECT. Каждое попадание либо уже защищено IS NOT NULL или один плохой импорт отделяет от возврата ничего.
2. Перепишите их как NOT EXISTS. Это механическое изменение, и результат идентичен на чистых данных, что облегчает проверку.
3. Для любого LEFT JOIN плюс IS NULL вы держите, проверьте две вещи: тестируемый столбец не может быть NULL в реальных данных, и ни одно условие в правой таблице не находится в WHERE.
4. Разумность проверяет каждое антисоединение с помощью арифметики. Совпадающий плюс несовпадающий должен равняться общей сумме. Здесь 5 плюс 3 равно 8.
5. Если результат оказывается пустым, не сообщайте о нем как «ничего не найдено», пока вы не подсчитаете всю таблицу. Пустой — это результат, который заслуживает еще одного запроса.

Если у вас под рукой есть бумага, нарисуйте восемь клиентов в одном столбце, а заказы — в другом, затем проведите линию от каждого заказа до его покупателя. Три клиента, до которых не доходит линия, являются ответом, а заказ, в котором нет очереди на обоих концах, — это NULL который опустошает NOT IN.

Шпаргалка

Единственная привычка, которую нужно сохранить

Когда анти-соединение возвращает ноль строк, воспринимайте это как вопрос, а не как ответ. Посчитайте таблицу, посчитайте совпадения и проверьте, совпадают ли они. Пустой результат — это единственный вывод, который выглядит одинаково независимо от того, правильно вы спросили или нет.

Какой последний результат «никто не пропал» вы сообщили, и был ли он записан с помощью NOT IN?