Имена для подзапросов: WITH

Подзапросы в FROM могут сделать запрос трудночитаемым. Его приходится читать изнутри наружу, и с каждым подзапросом вложенность растёт. WITH позволяет сначала написать подзапрос, дать ему имя, а потом использовать это имя как таблицу:

WITH totals AS (
    SELECT order_id, SUM(quantity * price) AS total
    FROM order_items
    GROUP BY order_id
)
SELECT ROUND(AVG(total), 2) AS average_order
FROM totals;

Это средний заказ из предыдущего упражнения, но теперь запрос читается сверху вниз: сначала вычисляем суммы заказов и называем их totals, затем вычисляем их среднее. Часть WITH имя AS (запрос) называется общим табличным выражением, или CTE (от common table expression). Она принадлежит следующему за ней запросу: вместе они образуют одну инструкцию с одной точкой с запятой в конце, а имя totals существует только внутри этой инструкции.

Именованный запрос можно использовать не один раз. Самый большой заказ — тот, чья сумма равна наибольшей сумме:

WITH totals AS (
    SELECT order_id, SUM(quantity * price) AS total
    FROM order_items
    GROUP BY order_id
)
SELECT order_id, total
FROM totals
WHERE total = (SELECT MAX(total) FROM totals);

Без WITH запрос, вычисляющий суммы, пришлось бы писать дважды: в FROM и в подзапросе в WHERE.

В одном WITH можно назвать несколько запросов, разделив их запятыми; слово WITH пишется только в начале. Каждый из них может использовать предыдущие. Здесь paid оставляет оплаченные заказы, paid_totals вычисляет их суммы, а главный запрос складывает их:

WITH paid AS (
    SELECT *
    FROM orders
    WHERE status = 'paid'
),
paid_totals AS (
    SELECT i.order_id, SUM(i.quantity * i.price) AS total
    FROM order_items AS i
    JOIN paid AS p ON p.id = i.order_id
    GROUP BY i.order_id
)
SELECT COUNT(*) AS paid_orders, SUM(total) AS revenue
FROM paid_totals;

Типичные ошибки: запятая после последнего именованного запроса, прямо перед главным SELECT (near "SELECT": syntax error), и точка с запятой после его закрывающей скобки, которая завершает инструкцию раньше, чем начался главный запрос (near ";": syntax error).