EXISTS и NOT EXISTS

EXISTS задаёт более простой вопрос, чем IN: находит ли подзапрос хотя бы одну строку? Если находит, EXISTS истинно, если ничего не находит — ложно. У каких клиентов был возвращённый заказ?

SELECT c.name
FROM customers AS c
WHERE EXISTS (
    SELECT *
    FROM orders AS o
    WHERE o.customer_id = c.id AND o.status = 'refunded'
)
ORDER BY c.name;

Посмотрите на условие подзапроса: o.customer_id = c.id. Подзапрос обращается к c — таблице внешнего запроса. Такой подзапрос называется коррелированным. Сам по себе он выполниться не может (получилась бы ошибка no such column: c.id), потому что зависит от строки, о которой внешний запрос сейчас принимает решение. Представьте, что база данных запускает его заново для каждого клиента. Для Emma Clark c.id равно 1, и подзапрос ищет возвращённые заказы клиента 1. Он не находит ни одного, поэтому Emma в результат не попадает. Для Jack Harris он находит заказ 7, значит, Jack в результате есть.

Подзапросы из предыдущих упражнений выполнялись всего один раз, и их результат был одинаков для каждой строки. Коррелированный подзапрос может давать для каждой строки свой ответ. (На самом деле база данных может выполнять его хитрее, но результат такой же, как если бы она выполняла его для каждой строки.)

Что именно показывает подзапрос, неважно: EXISTS лишь проверяет, есть ли строка. Поэтому программисты пишут там SELECT * или SELECT 1.

NOT EXISTS истинно, когда подзапрос ничего не находит. Вот клиенты, никому не рекомендовавшие магазин — вопрос, который попал в ловушку с NOT IN:

SELECT c.name
FROM customers AS c
WHERE NOT EXISTS (
    SELECT *
    FROM customers AS r
    WHERE r.referred_by = c.id
)
ORDER BY c.name;

У NOT EXISTS нет ловушки с NULL: клиент, у которого referred_by равен NULL, просто никогда не подходит под условие r.referred_by = c.id. Внешний запрос и подзапрос здесь используют одну и ту же таблицу, поэтому псевдонимы c и r обязательны: они показывают, какой клиент какой.

Типичная ошибка: забыть условие, которое связывает подзапрос со строкой внешнего запроса. WHERE EXISTS (SELECT * FROM orders WHERE status = 'refunded') спрашивает, есть ли вообще хоть один возвращённый заказ. Он есть, поэтому условие истинно для каждого клиента, и запрос показывает всех 14.