Подзапросы становятся длинными
Запрос с подзапросом во FROM читается изнутри наружу: сначала разбираешься с подзапросом в середине, потом возвращаешься к внешней части.
Общее табличное выражение (CTE, Common Table Expression) — подзапрос, которому дали имя и вынесли перед основным запросом с помощью слова WITH:
WITH имя AS (
SELECT …
)
SELECT … FROM имя …;
Возьмём средние баллы учеников из прошлого урока и с помощью CTE выберем тех, у кого средний балл ниже 3,5:
WITH student_avg AS (
SELECT student_id, AVG(grade) AS avg_grade
FROM grades
GROUP BY student_id
)
SELECT s.name, ROUND(sa.avg_grade, 2) AS avg_grade
FROM students s
JOIN student_avg sa ON sa.student_id = s.id
WHERE sa.avg_grade < 3.5
ORDER BY sa.avg_grade, s.name;
Результат:
| name | avg_grade |
| Егор Новиков | 2.75 |
| Захар Волков | 3.0 |
| Борис Иванов | 3.4 |
Запрос читается сверху вниз: «пусть student_avg — средние баллы учеников; выберем из неё тех, у кого меньше 3,5».
Таблица student_avg существует только во время выполнения этого запроса, в базе она не сохраняется.
Сначала дай шагу имя в WITH, потом пользуйся им.