Как читается SQL-запрос: порядок выполнения

Пишем в одном порядке, выполняем в другом

Запрос пишут, начиная с SELECT, но выполняется он не сверху вниз. Представь кухню ресторана: сначала со склада берут продукты (FROM), отбраковывают негодные (WHERE), раскладывают по кастрюлям (GROUP BY), выбрасывают неудачные кастрюли (HAVING), готовят и раскладывают по тарелкам (SELECT), выстраивают тарелки на раздаче (ORDER BY) и отдают первые несколько (LIMIT). Меню (SELECT) написано первым, но блюда из него появляются почти в самом конце.

Логический порядок шагов:

1. FROM и JOIN — берутся строки таблиц, таблицы соединяются.
2. WHERE — отбрасываются строки, не подходящие под условие.
3. GROUP BY — оставшиеся строки собираются в группы.
4. HAVING — отбрасываются группы, не подходящие под условие.
5. SELECT — вычисляются колонки результата и их имена из AS; здесь же считаются оконные функции (OVER), а затем DISTINCT убирает повторы.
6. ORDER BY — строки результата сортируются.
7. LIMIT и OFFSET — от отсортированного результата отрезается нужный кусок.

Каждый шаг видит только то, что сделали шаги до него. Отсюда растут почти все «странные» ошибки: запрос ссылается на то, чего на его шаге ещё нет. На самом деле оптимизатор PostgreSQL волен переставлять работу как угодно, но результат всегда такой, как будто шаги шли именно в этом порядке, — поэтому думать о запросе удобно именно так.

Один запрос — все шаги

Вопрос: в каких двух отделах (из тех, где таких хотя бы двое) больше всего сотрудников, нанятых с 2020 года, и какой у них самый высокий оклад?

SELECT department, COUNT(*) AS staff, MAX(salary) AS top_salaryFROM employeesWHERE hired_at >= '2020-01-01'GROUP BY departmentHAVING COUNT(*) >= 2ORDER BY staff DESC, departmentLIMIT 2

 department | staff | top_salary
------------+-------+------------
 Продажи    |     5 |  120000.00
 Поддержка  |     4 |  160000.00
(2 rows)

Прочитай его в порядке выполнения. FROM берёт всех 15 сотрудников. WHERE оставляет 12: директор и двое, нанятые в 2019 году, отсеялись — поэтому оклад директора в top_salary не попал. GROUP BY раскладывает 12 строк на три отдела, HAVING выполняет условие «таких хотя бы двое» и пропускает группы минимум из двух человек (здесь проходят все три). SELECT считает для каждой группы COUNT(*) и MAX(salary) и даёт им имена staff и top_salary. ORDER BY сортирует уже по имени staff, а LIMIT 2 отрезает две верхние строки — «Склад» с тремя сотрудниками остался третьим.

Почему имя из AS не работает в WHERE и HAVING

Имена из AS появляются на шаге SELECT. WHERE и HAVING выполняются раньше — для них этих имён ещё нет:

SELECT department, COUNT(*) AS staffFROM employeesGROUP BY departmentHAVING staff >= 4

ERROR: column "staff" does not exist

PostgreSQL ищет колонку staff в таблице employees и не находит. В HAVING повтори выражение: HAVING COUNT(*) >= 4. То же самое с WHERE — пример в карточке where. Повторить выражение в двух местах — обычная практика, а не ошибка стиля.

А вот ORDER BY идёт после SELECT, поэтому имя из AS там работает, как в первом примере. Ограничение одно: в ORDER BY имя можно написать только само по себе. Внутри выражения, например ORDER BY staff * -1, оно снова «не существует»: внутри выражения PostgreSQL ищет имя только среди колонок таблиц из FROM. Пиши выражение целиком: ORDER BY COUNT(*) DESC.

GROUP BY по имени — поблажка PostgreSQL

По порядку шагов GROUP BY тоже не должен знать имён из SELECT. Но PostgreSQL делает исключение и разрешает сгруппировать по имени колонки результата — это удобно, когда группируют по длинному выражению:

SELECT CASE WHEN salary >= 100000 THEN 'от 100 тыс.' ELSE 'до 100 тыс.' END AS salary_band,       COUNT(*) AS staffFROM employeesGROUP BY salary_bandORDER BY salary_band

 salary_band | staff
-------------+-------
 до 100 тыс. |    10
 от 100 тыс. |     5
(2 rows)

GROUP BY salary_band работает, хотя salary_band — не колонка таблицы, а имя выражения CASE (о нём — карточка case). Это расширение PostgreSQL: стандарт SQL так не разрешает, и в некоторых других СУБД выражение приходится повторять в GROUP BY целиком. Если имя из AS совпадёт с настоящей колонкой таблицы, GROUP BY возьмёт колонку таблицы, а ORDER BY — колонку результата; поэтому лучше не называть вычисляемые колонки так же, как колонки таблицы.

Где живут оконные функции

Оконные функции (OVER, карточка over) считаются на шаге SELECT — после WHERE, GROUP BY и HAVING, но до ORDER BY. Значит, окно видит только строки, которые пережили фильтры, а сортировка результата может отличаться от порядка внутри окна:

SELECT name, hired_at, ROW_NUMBER() OVER (ORDER BY hired_at) AS hire_orderFROM employeesWHERE department = 'Склад'ORDER BY name

      name      |  hired_at  | hire_order
----------------+------------+------------
 Егор Тихонов   | 2022-12-31 |          3
 Марк Осипов    | 2024-09-15 |          4
 Полина Егорова | 2022-03-14 |          2
 Роман Зайцев   | 2019-09-01 |          1
(4 rows)

Номера идут от 1 до 4, а не до 15: к моменту подсчёта WHERE уже оставил только склад. Номер выдан по дате найма (порядок внутри OVER), а строки выведены по имени (ORDER BY запроса) — это два разных порядка, и ORDER BY запроса применяется последним.

Раз оконные функции считаются после WHERE, отфильтровать по ним в том же запросе нельзя:

SELECT name, hired_atFROM employeesWHERE ROW_NUMBER() OVER (ORDER BY hired_at) <= 3

ERROR: window functions are not allowed in WHERE

Чтобы отобрать строки по результату оконной функции, посчитай её в подзапросе и фильтруй снаружи: SELECT … FROM (SELECT …, ROW_NUMBER() OVER (…) AS n FROM employees) t WHERE n <= 3. Так же нельзя написать агрегат в WHERE — для условий на группы есть HAVING. Как это выглядит на полном запросе, разобрано в статье «Оконные функции против GROUP BY».

Как пользоваться этим порядком

• Ошибка column "…" does not exist про имя, которое ты сам дал в AS, почти всегда значит: имя использовано на шаге раньше SELECT.
• Условие на отдельные строки — в WHERE, на группы и агрегаты — в HAVING (карточки where, having, group-by).
• Если результат «странный», прочитай запрос по шагам и на каждом шаге представь, какие строки остались. Статья «Агрегаты и группировка по шагам» делает это на одном запросе от начала до конца.