Подзапросы и CTE: урок по SQL и базы данных к собеседованию

Подзапрос это SELECT внутри другого запроса. Он бывает трёх видов по тому, что возвращает.

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

Возвращает одно значение (одна строка, одна колонка). Можно ставить туда, где ждут значение.

SELECT title, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);

Если такой подзапрос вернёт больше одной строки, будет ошибка. Если ноль строк, получится NULL.

Подзапрос-список: IN

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

Ловушка с NOT IN: если подзапрос вернул хотя бы один NULL, условие NOT IN никогда не станет true, и результат будет пустым. Поэтому для «у кого нет» используйте NOT EXISTS.

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

Коррелированный подзапрос ссылается на колонки внешнего запроса и логически выполняется для каждой строки.

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

EXISTS проверяет только факт наличия строк, что вы пишете в SELECT подзапроса, не важно. Он корректно работает с NULL. Современные планировщики (и PostgreSQL) превращают EXISTS и IN в semi-join, так что «IN всегда медленнее» это миф. NOT IN и NOT EXISTS различаются по смыслу из-за NULL, а не только по скорости.

Подзапрос в FROM

Результат подзапроса можно использовать как таблицу (обязателен псевдоним):

SELECT AVG(cnt)
FROM (SELECT customer_id, COUNT(*) AS cnt FROM orders GROUP BY customer_id) t;

CTE: WITH

Тот же подзапрос, вынесенный наверх и названный. Читается сверху вниз как шаги.

WITH spent AS (
  SELECT customer_id, SUM(total) AS total
  FROM orders WHERE status = 'paid'
  GROUP BY customer_id
)
SELECT c.name, s.total
FROM customers c JOIN spent s ON s.customer_id = c.id
WHERE s.total > (SELECT AVG(total) FROM spent);

В PostgreSQL до 12 версии CTE всегда материализовались и мешали оптимизации. С 12 версии CTE, на который ссылаются один раз и без побочных эффектов, встраивается в запрос. Управлять можно явно: WITH x AS MATERIALIZED (...) или NOT MATERIALIZED.

Рекурсивный CTE

Нужен для деревьев и иерархий: оргструктура, категории, комментарии.

WITH RECURSIVE sub AS (
  SELECT id, name, manager_id, 1 AS lvl
  FROM employees WHERE id = 1            -- якорь
  UNION ALL
  SELECT e.id, e.name, e.manager_id, s.lvl + 1
  FROM employees e JOIN sub s ON e.manager_id = s.id   -- шаг
)
SELECT * FROM sub;

Сначала выполняется якорь, затем шаг повторяется над строками предыдущей итерации, пока шаг не вернёт пустоту. Если в данных цикл, а вы используете UNION ALL, запрос не остановится: защищайтесь ограничением глубины или проверкой пути.

LATERAL

Подзапрос в FROM, который видит колонки таблиц слева. Удобно для «топ-N на группу»:

SELECT c.name, last.id
FROM customers c
CROSS JOIN LATERAL (
  SELECT id FROM orders o WHERE o.customer_id = c.id
  ORDER BY created_at DESC LIMIT 1
) last;

Частые ошибки

NOT IN по колонке, где возможен NULL.
Скалярный подзапрос, который вдруг вернул две строки.
Рекурсивный CTE без условия остановки на данных с циклом.
Вера в то, что CTE в современном PostgreSQL всегда «барьер оптимизации».

Что спросят на собесе

Чем EXISTS отличается от IN и почему NOT IN опасен.
Что такое коррелированный подзапрос.
Зачем нужны CTE и материализуются ли они в PostgreSQL.
Как обойти дерево сотрудников рекурсивным запросом.
Как найти строки с ценой выше средней по своей категории.

Подзапросы и CTE за 6 минут