Что такое общее табличное выражение (CTE) в SQL

Что проверяют

Знаете ли вы, что WITH это не только косметика. Для бэкендера вопрос обычно про две вещи: умеете ли вы одним запросом сделать то, что иначе превращается в цикл с походами в базу, и понимаете ли, во что выражение обходится планировщику.

Как отвечать

Общее табличное выражение это именованный подзапрос, объявленный до основного и доступный ему по имени. Стоит назвать три применения.

Читаемость и повторное использование: длинный отчёт разбивается на шаги с говорящими именами вместо вложенных подзапросов в четыре уровня.

Рекурсия: WITH RECURSIVE обходит иерархии, то есть дерево комментариев, структуру подразделений, граф зависимостей. Устроено из якорной части, UNION ALL и рекурсивной части, которая обращается к самому выражению.

Изменяющие данные выражения: в PostgreSQL внутри WITH можно поставить INSERT, UPDATE или DELETE с RETURNING и использовать результат дальше в том же запросе. Отсюда любимый бэкендерами приём выборки задач из очереди одним обращением.

WITH picked AS (
    SELECT id FROM jobs
    WHERE status = 'new'
    ORDER BY created_at
    FOR UPDATE SKIP LOCKED
    LIMIT 10
)
UPDATE jobs SET status = 'running'
FROM picked WHERE jobs.id = picked.id
RETURNING jobs.id, jobs.payload;

Про планировщик скажите отдельно: в старых версиях PostgreSQL выражение всегда материализовалось и работало барьером для оптимизатора, в современных планировщик встраивает его, когда оно используется один раз и не меняет данные, а ключевые слова MATERIALIZED и NOT MATERIALIZED задают поведение явно.

Где спотыкаются

Считают выражение бесплатной абстракцией и заворачивают в него всё подряд. Материализованный промежуточный результат живёт без индексов, и соединение по нему легко превращает быстрый запрос в полный перебор.

Пишут рекурсивную часть без защиты от циклов. На данных с петлёй в иерархии запрос не вернётся никогда, нужны ограничение глубины или накопление пройденных идентификаторов.

Ждут, что части одного запроса увидят изменения друг друга. Все они работают с одним снимком данных, поэтому второе выражение не увидит строк, вставленных первым, а порядок выполнения ветвей не определён.