Подзапросы, CTE и EXISTS

Подзапрос

Подзапрос — это SELECT внутри другого запроса, в скобках. Сначала считается внутренний, его результат подставляется наружу. Так сравнивают с агрегатом, проверяют вхождение в список или подставляют готовую таблицу во FROM.

Снаружи подзапрос ставят в WHERE, SELECT, FROM, HAVING. Внутри него доступны те же операторы, что и в обычном запросе.

Скалярный подзапрос

Скалярный подзапрос возвращает одну строку и одну колонку. Его можно сравнить с числом или подставить как колонку в SELECT. Если строк несколько, PostgreSQL выдаст ошибку.

SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products)
ORDER BY price DESC, name;

Внутренний AVG(price) считается один раз: он не ссылается на внешнюю строку. Такой подзапрос называют некоррелированным.

Если внутри взять значение снаружи, подзапрос станет коррелированным: для каждой строки внешний запрос заново считает внутренний.

SELECT p.name, p.price
FROM products p
WHERE p.category_id = 1
  AND p.price > (
    SELECT AVG(p2.price)
    FROM products p2
    WHERE p2.category_id = p.category_id
  )
ORDER BY p.price DESC, p.name;

Наушники и планшет из электроники дешевле средней по категории — их нет в результате.

IN

IN проверяет, входит ли значение в список. Список можно написать вручную: city IN ('Казань', 'Самара'). Или получить подзапросом — тогда подзапрос возвращает одну колонку и сколько угодно строк.

SELECT name
FROM customers
WHERE id IN (
  SELECT customer_id
  FROM orders
  WHERE status = 'paid'
)
ORDER BY name;

Повторы во внутреннем списке не важны: Елена с двумя оплаченными заказами попадает один раз.

EXISTS

EXISTS отвечает на вопрос «нашлась ли хотя бы одна строка», а не «какое у неё значение». Внутри обычно пишут SELECT 1: колонку всё равно не читают, важно только наличие строки.

Подзапрос коррелированный: для каждого клиента смотрят его заказы.

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

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

NOT EXISTS

NOT EXISTS — «не нашлось ни одной строки». Клиенты без заказов: в первой главе это был LEFT JOIN и WHERE o.id IS NULL. Тот же смысл через NOT EXISTS:

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

NOT IN с подзапросом здесь даст то же, потому что customer_id не бывает NULL. Если внутренний список может содержать пустое значение, NOT IN не найдёт никого. Для «нет строк» надёжнее NOT EXISTS.

Подзапрос во FROM

Во FROM подзапрос ведёт себя как таблица: у него должны быть имена колонок и алиас. В PostgreSQL без алиаса запрос не соберётся.

SELECT city, customers
FROM (
  SELECT city, COUNT(*) AS customers
  FROM customers
  GROUP BY city
) t
WHERE customers >= 2
ORDER BY customers DESC, city;

Это тот же фильтр, что HAVING COUNT(*) >= 2 в первой главе. Подзапрос во FROM нужен, когда промежуточную таблицу дальше джойнят или фильтруют по уже посчитанным колонкам.

WITH

WITH даёт подзапросу имя — CTE, common table expression. Дальше к этому имени обращаются как к таблице. Смысл тот же, что у подзапроса во FROM, читать обычно проще.

WITH city_counts AS (
  SELECT city, COUNT(*) AS customers
  FROM customers
  GROUP BY city
)
SELECT city, customers
FROM city_counts
WHERE customers >= 2
ORDER BY customers DESC, city;

Несколько CTE пишут через запятую. Следующий может ссылаться на предыдущий. Рекурсивный WITH RECURSIVE — для иерархий вроде «сотрудник и его руководитель»; его разберём отдельно.

Всё вместе

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

WITH big_cities AS (
  SELECT city
  FROM customers
  GROUP BY city
  HAVING COUNT(*) >= 2
)
SELECT c.name, c.city
FROM customers c
JOIN big_cities b ON b.city = c.city
WHERE EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.id
    AND o.status = 'paid'
)
ORDER BY c.name;

Наталья Лебедева заказ оплатила, но в Екатеринбурге она одна — big_cities её не берёт. Сергей из Казани в городе проходит, а оплаченного заказа нет.

Дальше — окна, UNION, INTERSECT и EXCEPT.