SQL с нуля. Часть 6: подзапросы и CTE (WITH)

TL;DR: Подзапрос — это SELECT внутри другого SELECT. Может быть во FROM (подзапрос-таблица), WHERE (фильтр-условие), SELECT (значение колонки). CTE (WITH name AS (SELECT...)) — именованный подзапрос, читается сверху вниз. Для сложных запросов CTE предпочтительнее.

В этой части:

Что разберёшь в этом уроке

• Что встретится в этой части
• Что такое подзапрос?
• Где может быть подзапрос — 3 места?
• Что такое CTE и зачем он?
• Какие 4 причины брать CTE вместо подзапросов?
• Можно ли использовать одну CTE несколько раз?
• Что такое recursive CTE?
• Как объединить результаты двух запросов — UNION, INTERSECT, EXCEPT?
• Какие 4 типичные ошибки с подзапросами и CTE?
• Практика: 9 задач прямо в браузере

Частые вопросы

Что такое подзапрос?

Подзапрос — SELECT внутри другого SELECT, в скобках.

Простой пример: «юзеры с выручкой > среднего»:

Где может быть подзапрос — 3 места?

Что такое CTE и зачем он?

CTE = Common Table Expression. Это именованный подзапрос в виде WITH name AS (SELECT...). Читается сверху вниз, не вложено.

Тот же запрос «юзеры с выручкой > среднего» через CTE:

Какие 4 причины брать CTE вместо подзапросов?

• Читаемость — сверху вниз, как программа
• Повторное использование — на одну CTE можно ссылаться несколько раз
• Отладка — можно SELECT \* FROM cte_name чтобы посмотреть результат
• Recursive CTE — обход иерархий (см. ниже)

Минусы CTE:

• В PostgreSQL до версии 12 CTE был «барьером оптимизатора» (по-английски — optimization fence): база считала CTE отдельно и не могла переписать запрос через её границу, поэтому план был хуже подзапроса. С PG 12+ — эквивалентно подзапросу.
• В MySQL CTE появилось только с 8.0
• В очень больших dbt-моделях (dbt — инструмент, который превращает SQL-файлы в таблицы или представления в хранилище) CTE может «съесть» план оптимизатора. Тогда — материализованное представление (заранее посчитанный SELECT, сохранённый как физическая таблица, обновляется командой REFRESH).

Что такое recursive CTE?

Recursive CTE — CTE, которая ссылается на саму себя. Используется для обхода иерархий (организационная структура, BOM, граф ссылок).

Как объединить результаты двух запросов — UNION, INTERSECT, EXCEPT?

Set-операции комбинируют результаты двух SELECT'ов (а не таблицы, как JOIN). Условие: у обоих запросов должно быть одинаковое число колонок одинаковых типов.