EXISTS, ANY, ALL

EXISTS, ANY, ALL

В предыдущих уроках вы использовали IN и NOT IN для проверки вхождения в список из подзапроса. Теперь разберём три связанных оператора: EXISTS проверяет, вернул ли подзапрос хотя бы одну строку; ANY и ALL сравнивают значение с набором значений из подзапроса. Каждый из них решает задачи, где IN неудобен или опасен.

EXISTS: есть ли строки в подзапросе

EXISTS возвращает TRUE, если подзапрос вернул хотя бы одну строку, и FALSE если подзапрос пустой. Содержимое строк не важно — только факт их наличия.

SELECT name, email
FROM   customers c
WHERE  EXISTS (
    SELECT 1
    FROM   orders o
    WHERE  o.customer_id = c.id
);

SELECT 1 внутри EXISTS — стандартная практика. СУБД не читает данные строк, только проверяет их наличие. Можно написать SELECT * или SELECT id — результат одинаковый, но SELECT 1 явно показывает, что содержимое строк неважно.

Это коррелированный подзапрос: для каждого клиента проверяется, есть ли заказы с его customer_id.

NOT EXISTS: нет строк в подзапросе

NOT EXISTS — противоположность: возвращает TRUE если подзапрос пустой.

-- Клиенты без заказов
SELECT name, email
FROM   customers c
WHERE  NOT EXISTS (
    SELECT 1
    FROM   orders o
    WHERE  o.customer_id = c.id
);

Это эквивалент «LEFT JOIN + WHERE IS NULL» и «NOT IN», но безопаснее при наличии NULL:

-- NOT EXISTS безопасен с NULL в данных
WHERE  NOT EXISTS (SELECT 1 FROM orders WHERE customer_id = c.id)

-- NOT IN опасен: если хоть один customer_id в orders = NULL, результат пустой
WHERE  c.id NOT IN (SELECT customer_id FROM orders)

Поэтому для задачи «найти строки без пары» большинство разработчиков предпочитают NOT EXISTS или LEFT JOIN ... IS NULL, а не NOT IN.

Проверь себя: почему NOT IN с NULL в подзапросе возвращает пустой результат?

EXISTS vs IN: что выбрать

EXISTS и IN часто взаимозаменяемы. Тонкое различие:

IN материализует список значений из подзапроса и потом проверяет каждую строку внешнего запроса. При большом списке (тысячи значений) это нормально.

EXISTS останавливается при первом найденном совпадении (short-circuit evaluation). Для задачи «есть ли хоть одна строка» это быстрее.

На практике PostgreSQL оптимизирует оба варианта, и разница в производительности обычно незначительна. Выбор за читаемостью: EXISTS читается как «если существует», IN читается как «если входит в список».

-- EXISTS: «если у клиента существует хотя бы один заказ»
WHERE EXISTS (SELECT 1 FROM orders WHERE customer_id = c.id)

-- IN: «если id клиента входит в список id из заказов»
WHERE c.id IN (SELECT customer_id FROM orders)

ANY: сравнение с любым из набора

ANY (синоним SOME) используется с операторами сравнения (=, >, <, >=, <=, <>) и означает «хотя бы одно значение из набора удовлетворяет условию»:

-- Сотрудники, зарплата которых выше хотя бы одного сотрудника в IT-отделе
SELECT name, salary
FROM   employees
WHERE  salary > ANY (
    SELECT salary FROM employees WHERE department = 'IT'
);

Это эквивалентно «зарплата выше минимальной в IT». > ANY — хотя бы одно из значений меньше текущего, значит текущее больше хотя бы одного.

Частный случай: = ANY (подзапрос) эквивалентен IN (подзапрос):

-- Эти два запроса идентичны
WHERE department = ANY (SELECT department FROM departments WHERE active = true)
WHERE department IN (SELECT department FROM departments WHERE active = true)

ALL: сравнение со всеми значениями

ALL означает «условие выполняется для всех значений из набора»:

-- Сотрудники, зарплата которых выше зарплаты ВСЕХ сотрудников в отделе 'HR'
SELECT name, salary
FROM   employees
WHERE  salary > ALL (
    SELECT salary FROM employees WHERE department = 'HR'
);

Это эквивалентно «зарплата выше максимальной в HR». > ALL — все значения меньше текущего, значит текущее больше всех.

<> ALL (подзапрос) эквивалентен NOT IN (подзапрос) — с той же проблемой NULL.

Ловушка: ANY/ALL с NULL

ANY и ALL имеют те же проблемы с NULL, что IN/NOT IN:

-- Если хоть одна salary в HR = NULL:
WHERE salary > ALL (SELECT salary FROM employees WHERE department = 'HR')
-- вернёт FALSE для всех строк (сравнение с NULL = UNKNOWN)

В реальных данных всегда лучше добавить WHERE salary IS NOT NULL в подзапрос для ALL:

WHERE salary > ALL (
    SELECT salary FROM employees WHERE department = 'HR' AND salary IS NOT NULL
)

Практический пример: найти «лучших» клиентов

Задача: клиенты, у которых сумма заказов выше, чем у любого клиента из Казани:

SELECT c.name, c.city, SUM(o.amount) AS total
FROM   customers c
JOIN   orders    o ON c.id = o.customer_id
GROUP BY c.id, c.name, c.city
HAVING SUM(o.amount) > ALL (
    SELECT SUM(o2.amount)
    FROM   orders    o2
    JOIN   customers c2 ON o2.customer_id = c2.id
    WHERE  c2.city = 'Казань'
    GROUP BY c2.id
);

HAVING > ALL (...) означает «сумма больше максимальной суммы среди казанских клиентов».

EXISTS для проверки связанных данных

EXISTS удобен для условий «у объекта A есть связанные объекты B с условием»:

-- Категории товаров, у которых есть хотя бы один товар дороже 10000
SELECT DISTINCT category
FROM   products p_cat
WHERE  EXISTS (
    SELECT 1
    FROM   products p
    WHERE  p.category = p_cat.category
      AND  p.price > 10000
);

-- Заказы, в которых есть хотя бы один товар категории 'Электроника'
SELECT o.id, o.created_at, o.amount
FROM   orders      o
WHERE  EXISTS (
    SELECT 1
    FROM   order_items oi
    JOIN   products    p ON oi.product_id = p.id
    WHERE  oi.order_id = o.id
      AND  p.category  = 'Электроника'
);

Это читается очень естественно: «заказы, для которых существует позиция, относящаяся к электронике».

Выбор между EXISTS, IN и LEFT JOIN: шпаргалка

Все три способа часто решают одну и ту же задачу. Вот ориентиры:

Эти операторы — инструменты. Умение выбрать подходящий делает запросы короче, выразительнее и понятнее следующему разработчику.

Краткий итог

• EXISTS — возвращает TRUE если подзапрос вернул хотя бы одну строку; не важно что именно
• NOT EXISTS — безопаснее NOT IN при возможных NULL в данных
• ANY — условие выполняется для хотя бы одного значения из набора (= ANY ≡ IN)
• ALL — условие выполняется для всех значений из набора (<> ALL ≡ NOT IN, с той же NULL-ловушкой)
• В подзапросах для ANY/ALL добавляйте IS NOT NULL если данные могут содержать NULL

Отладка EXISTS-запросов

Если EXISTS-запрос возвращает неожиданный результат, полезно запустить подзапрос отдельно для конкретного значения:

-- Основной запрос не возвращает клиента с id=5?
-- Проверьте подзапрос для него:
SELECT 1
FROM   orders o
WHERE  o.customer_id = 5;   -- подставьте конкретный id

Если результат пустой — EXISTS даёт FALSE для этой строки, и клиент не попадает в результат. Так можно точно понять логику: подзапрос ничего не нашёл, значит клиент действительно не должен быть в результате.

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

Что дальше

Вы освоили все виды подзапросов. Следующий урок — UNION и UNION ALL: как объединять результаты нескольких запросов в один набор данных.