Подзапросы SQL и CTE

Коротко: Подзапрос — запрос внутри другого запроса. CTE («common table expression», WITH) даёт промежуточному запросу имя и делает длинные запросы читаемыми.

Подробнее: Оба решают задачи, которые не выполнить одним простым запросом, например «клиенты с оборотом выше среднего».

Подробности

• Скалярный: Возвращает ровно одно значение: WHERE amount > (SELECT AVG(amount) FROM orders).
• Список: WHERE customer_id IN (SELECT id FROM customer WHERE country = 'DE').
• EXISTS: WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id) проверяет, есть ли хотя бы одна строка, и останавливается на первом совпадении. Часто быстрее и безопаснее IN, особенно в отношении ловушек NULL (см. NULL в SQL).
• Коррелированный: Подзапрос ссылается на внешнюю строку и логически выполняется для каждой строки.
• В FROM: Подзапрос как «производная таблица», которой даётся псевдоним.
• CTE: WITH revenue AS (SELECT customer_id, SUM(amount) s FROM orders GROUP BY customer_id) SELECT ... FROM revenue .... Несколько CTE подряд структурируют сложные выборки. Рекурсивные: рекурсивные CTE.
• Выбор: Читаемость важна. Современные оптимизаторы часто обрабатывают подзапросы как соединения.

См. также: JOIN в SQL, оконные функции