Подзапрос в условии, IN и EXISTS, коррелированные подзапросы, подзапрос в FROM и CTE — WITH.
Подзапрос — SELECT внутри другого запроса, в скобках. Самый простой случай — подзапрос, который возвращает одно
значение, и с ним сравнивают как с обычным числом:
SELECT name, price
FROM products
WHERE price > (SELECT avg(price) FROM products);
PostgreSQL сначала посчитает среднюю цену, потом подставит её в условие. Написать WHERE price > avg(price) нельзя:
агрегаты в WHERE запрещены.
IN (подзапрос) — значение входит в список, который вернул подзапрос:
SELECT name
FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE status = 'cancelled');
EXISTS (подзапрос) — нашлась ли хотя бы одна строка. Подзапрос внутри ссылается на строку внешнего запроса
и проверяется для каждой строки заново. Такой подзапрос называют коррелированным:
SELECT c.name
FROM customers c
WHERE NOT EXISTS (
SELECT 1 FROM orders o WHERE o.customer_id = c.id
);
Что выводит подзапрос в EXISTS, неважно — важно, нашлась ли строка, поэтому пишут SELECT 1. NOT EXISTS
находит строки без пары и не ломается на NULL. С NOT IN есть ловушка: если подзапрос вернул хотя бы один
NULL, результат будет пустым. Её разберём в последнем блоке.
Коррелированный подзапрос может стоять и в сравнении — например, «товары туризма дороже среднего по своей категории»:
SELECT p.name, p.price
FROM products p
WHERE p.category_id = 16
AND p.price > (SELECT avg(p2.price) FROM products p2 WHERE p2.category_id = p.category_id);
Результат подзапроса можно использовать как таблицу. Ему дают псевдоним — здесь t: с PostgreSQL 16 он
необязателен, но в более старых версиях и во многих других СУБД без него запрос падает с ошибкой.
SELECT round(avg(total)) AS avg_check
FROM (
SELECT order_id, sum(quantity * price) AS total
FROM order_items
GROUP BY order_id
) t;
Тот же подзапрос можно вынести в начало и дать ему имя — это CTE (common table expression). Длинный запрос тогда читается сверху вниз, по шагам, а один шаг можно использовать несколько раз:
WITH order_totals AS (
SELECT order_id, sum(quantity * price) AS total
FROM order_items
GROUP BY order_id
)
SELECT order_id, total
FROM order_totals
WHERE total > 80000;
Шагов в WITH может быть несколько, через запятую: WITH a AS (…), b AS (… FROM a …) SELECT ….