Подзапросы и промежуточные результаты

Подзапрос — запрос, вложенный в другой SQL-запрос. Он может вернуть одно значение, одну строку или целую таблицу. Поэтому перед чтением синтаксиса всегда спрашивайте: сколько строк и столбцов здесь допустимо?

#Подзапрос с одним значением

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

SELECT
    o.id,
    (SELECT max(p.created_at)
     FROM payments AS p
     WHERE p.order_id = o.id) AS last_payment_at
FROM orders AS o;

Для каждого заказа внутренний запрос находит максимальное время платежа. max без GROUP BY всегда возвращает одну строку. Если платежей нет, значение будет NULL. Если обычный скалярный подзапрос вернёт две строки, PostgreSQL остановит запрос с ошибкой.

Если нужны несколько столбцов последней строки, обычно понятнее использовать LEFT JOIN LATERAL ... ORDER BY ... LIMIT 1 из предыдущего урока.

#EXISTS и IN

EXISTS отвечает только на вопрос «есть ли хотя бы одна строка?»:

SELECT o.id
FROM orders AS o
WHERE EXISTS (
    SELECT 1
    FROM payments AS p
    WHERE p.order_id = o.id
      AND p.status = 'captured'
);

Значение после внутреннего SELECT не важно, поэтому принято писать 1. Планировщик может преобразовать такую запись в соединение и не обязан буквально запускать подзапрос отдельно для каждого заказа.

IN проверяет вхождение в набор. Для отрицательной проверки безопаснее NOT EXISTS, если набор может содержать NULL:

SELECT u.id
FROM app_users AS u
WHERE NOT EXISTS (
    SELECT 1 FROM orders AS o WHERE o.user_id = u.id
);

У NOT IN один NULL во внутреннем результате может превратить сравнение в UNKNOWN и убрать все неожидаемые строки.

#ANY и ALL

ANY и ALL применяют сравнение к набору значений:

SELECT id, price
FROM products
WHERE price > ALL (
    SELECT price FROM products WHERE category_id = $1
);

price > ALL (...) означает «цена больше каждого значения в наборе». price > ANY (...) означает «цена больше хотя бы одного значения».

Для пустого набора условие с ALL истинно: не существует значения, которое его нарушает. Условие с ANY ложно: не существует значения, которое его подтверждает. NULL внутри набора снова может дать UNKNOWN.

#WITH и CTE

Конструкция WITH даёт имя промежуточному запросу. Такой запрос называют CTE, или общим табличным выражением (Common Table Expression):

WITH active_orders AS (
    SELECT * FROM orders WHERE status <> 'cancelled'
)
SELECT *
FROM active_orders
WHERE org_id = $1;

Здесь active_orders можно читать как временную именованную таблицу внутри одной SQL-команды. Она не сохраняется после выполнения.

PostgreSQL может встроить простой CTE в основной запрос, чтобы, например, раньше применить org_id = $1. Это называют встраиванием (folding).

MATERIALIZED требует сначала полностью вычислить и сохранить промежуточный результат:

WITH expensive AS MATERIALIZED (
    SELECT product_id, sum(quantity) AS quantity
    FROM order_items
    GROUP BY product_id
)
SELECT a.product_id
FROM expensive AS a
JOIN expensive AS b USING (product_id);

Материализация полезна, если дорогой неизменный результат используется несколько раз. Но она требует памяти или временного файла и мешает протолкнуть фильтр внутрь. NOT MATERIALIZED явно разрешает встраивание. Выбор проверяют планом, а не только видом SQL.

#Изменение данных через WITH

CTE может удалить строки и передать их другой команде через RETURNING:

WITH moved AS (
    DELETE FROM retry_queue
    WHERE available_at <= clock_timestamp()
    RETURNING id, payload
)
INSERT INTO processing_queue (id, payload)
SELECT id, payload FROM moved;

Так подходящие строки удаляются из одной очереди и вставляются в другую одной SQL-командой. Не пытайтесь независимо изменить одну строку дважды внутри такого выражения: порядок не связанных друг с другом изменяющих CTE не стоит угадывать.

#Рекурсивный запрос для дерева

Рекурсия — повторение шага, использующего результат предыдущего шага. Она нужна, например, чтобы пройти дерево категорий любой глубины.

Рекурсивный CTE состоит из начальной части и повторяемой части:

WITH RECURSIVE tree AS (
    SELECT
        c.id,
        c.parent_id,
        c.name,
        ARRAY[c.id]::bigint[] AS visited,
        c.name::text AS path,
        0 AS depth
    FROM categories AS c
    WHERE c.parent_id IS NULL

    UNION ALL

    SELECT
        c.id,
        c.parent_id,
        c.name,
        tree.visited || c.id,
        tree.path || ' / ' || c.name,
        tree.depth + 1
    FROM tree
    JOIN categories AS c ON c.parent_id = tree.id
    WHERE NOT c.id = ANY(tree.visited)
)
SELECT id, path, depth
FROM tree;

Первая часть выбирает корни, у которых нет родителя. Вторая находит детей уже найденных строк. Массив visited хранит пройденные идентификаторы, чтобы ошибочная циклическая ссылка не создала бесконечный обход. Для больших деревьев нужен индекс на parent_id и разумный предел глубины или числа строк.

PostgreSQL также поддерживает конструкции SEARCH и CYCLE. Они решают похожие задачи стандартным синтаксисом SQL; выбирайте форму, которую команде проще читать и проверять.

#Объединение результатов

Операции над множествами соединяют результаты запросов по позициям столбцов:

SELECT user_id FROM newsletter_members
UNION
SELECT user_id FROM event_members;

SELECT user_id FROM newsletter_members
UNION ALL
SELECT user_id FROM event_members;

UNION объединяет и удаляет повторы;
UNION ALL объединяет и сохраняет повторы;
INTERSECT оставляет строки, встречающиеся с обеих сторон;
EXCEPT оставляет строки первого запроса, которых нет во втором.

Версии без ALL тратят дополнительную работу на удаление повторов. Столбцы двух запросов сопоставляются по позиции и должны иметь совместимые типы.

Общий ORDER BY после операции сортирует весь результат. Если сначала нужно ограничить и отсортировать отдельную ветвь, заключите её в скобки.

#Практика

В лаборатории 11 вы построите путь категорий с защитой от цикла и найдёте пользователей без заказов через NOT EXISTS.

Подробнее: выражения с подзапросами, запросы WITH, объединение запросов.