Что такое оператор WITH и для чего он используется

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

Знаете ли вы, что WITH это именованный подзапрос в рамках одного оператора, и отличаете ли читаемость от скорости. Backend-разработчику вопрос задают не ради определения, а чтобы понять, лезете ли вы в план запроса.

Как отвечать

WITH имя AS (запрос) объявляет обобщённое табличное выражение: временный именованный результат, видимый только внутри текущего оператора. Их можно объявить несколько через запятую, и следующее может ссылаться на предыдущее.

WITH recent AS (
    SELECT order_id, user_id FROM orders WHERE created_at >= now() - interval '7 days'
)
SELECT user_id, count(*) FROM recent GROUP BY user_id;

Зачем это нужно:

• разложить многоэтажный запрос на шаги с понятными именами вместо вложенных подзапросов;
• сослаться на промежуточный результат несколько раз;
• обойти иерархию рекурсивным выражением: якорная выборка, UNION ALL, шаг, ссылающийся на само выражение, и условие остановки;
• в PostgreSQL выполнить внутри одного оператора изменение данных и сразу использовать результат RETURNING.

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

Из практики на Java: такие запросы обычно уходят в нативные, потому что поддержка выражений в JPQL появилась поздно, и на вас остаются маппинг результата и постраничная выдача.

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

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

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

Переписывают запрос ради красоты и не сверяются с планом. Разбор EXPLAIN до и после отличает инженера от исполнителя, а заодно ловит потерянные индексы.