NOT EXISTS, NOT IN и анти-LEFT JOIN

— Запрос тормозит, — выдохнул Алекс, — Использую NOT IN, чтобы отсеять ненужные записи.

— Попробуй NOT EXISTS. Все знают, он быстрее. — посоветовала Ольга, сидевшая напротив.

Макс, проходивший мимо, остановился с улыбкой:

— А что, если я скажу вам, что это — главный миф PostgreSQL, а настоящая ловушка скрыта в вашем NOT IN?

Он опёрся о стол.

— Давайте поиграем в детективов. Подозреваемый №1: универсальное правило, будто NOT EXISTS всегда быстрее LEFT JOIN ... IS NULL. Современный PostgreSQL часто распознаёт обе безопасно записанные формы как анти-соединение и строит одинаковый план.

SELECT o.*
FROM orders AS o
WHERE NOT EXISTS (
  SELECT 1
  FROM blocked_orders AS b
  WHERE b.order_id = o.id
);

SELECT o.*
FROM orders AS o
LEFT JOIN blocked_orders AS b ON b.order_id = o.id
WHERE b.order_id IS NULL;

Алекс набросал два варианта своего запроса и запустил EXPLAIN для обоих. На его лице проступило удивление. «Планы… идентичны. Hash Anti Join в обоих случаях».

— Именно, — подтвердил Макс. — В этом примере выбор формы — вопрос читаемости. Но проверка IS NULL должна относиться к гарантированно непустому столбцу правой таблицы, а дополнительные условия соединения должны сохранить исходную семантику. Иначе внешнее соединение уже не эквивалентно NOT EXISTS.

— А теперь — к настоящему виновнику, — голос Макса стал серьезнее. — Ваш NOT IN. Он коварен. Что будет, если в таблице, по которой вы исключаете записи появится хотя бы один NULL?

—Запрос его проигнорирует? — предположил Алекс.

— Хуже, — ответил Макс. — Логика SQL превращает условие id NOT IN (1, 2, NULL) в id <> 1 AND id <> 2 AND id <> NULL. А любое сравнение с NULL дает результат UNKNOWN. И строки, для которых условие UNKNOWN, в выборку не попадают. Ваш запрос не сломается. Он молча вернет ноль строк. Это мина замедленного действия.

Алекс откинулся на спинку стула и медленно выдохнул.

— Вот это да. Я лет десять писал NOT IN, уверенный, что это нормально. И всегда ругал LEFT JOIN за громоздкость.

— Идея не в том, чтобы запомнить, что «NOT EXISTS и LEFT JOIN — молодцы, а NOT IN — плохой». — Улыбнулся Макс. — Главное — понять, почему. И всегда спрашивать: «А что, если здесь будут NULL?». И, конечно, проверять с помощью EXPLAIN.

— Для обычного анти-соединения я предпочитаю NOT EXISTS: он прямо выражает намерение и не зависит от NULL в правой выборке. NOT IN оставляйте для заведомо непустых наборов или явно исключайте NULL, но всё равно проверяйте план и результат на пограничных данных, — подытожил Макс.