День семьсот семьдесят девятый. #ЗаметкиНаПолях #SQL
Выражение WITH и Сложные Запросы
В разработке ПО обычной практикой является инкапсуляция инструкций в небольшие и легко понятные единицы - функции или методы. В SQL мы оперируем не инструкциями, а запросами. Чтобы сделать запросы многоразовыми, в SQL-92 были введены представления (view). После создания представление получает имя в схеме базы данных, чтобы другие запросы могли использовать его как таблицу. В SQL-99 добавлено предложение WITH для определения «представлений в области действия оператора». Они не хранятся в схеме базы данных, а живут только в рамках текущего запроса. Это позволяет улучшить структуру запроса, не загрязняя глобальное пространство имен. Предложение WITH также известно как общее табличное выражение (CTE).

Синтаксис

WITH query1 (column1, …) AS
  (SELECT … FROM table …)
SELECT * FROM query1;

WITH не является самостоятельной командой, за ним должен следовать SELECT. Запрос SELECT (и содержащиеся в нем подзапросы) могут ссылаться в своём блоке FROM на определённое в предложении WITH имя подзапроса.

Одно предложение WITH может определять несколько имён подзапросов, разделяя их запятыми. Каждый из этих подзапросов может ссылаться на ранее определённые имена подзапросов:

WITH 
  query1 AS (SELECT …), 
  query2 AS (SELECT … FROM query1 …)
SELECT …

ВАЖНО! Имена подзапросов, определённые в предложении WITH скрывают таблицы или представления с тем же именем.

СУБД
Базовая функциональность предложения WITH доступна во всех современных СУБД.

Производительность
- Большинство баз данных обрабатывают WITH-запросы так же, как и представления: они заменяют ссылку на запрос его определением и оптимизируют общий запрос.
*PostgreSQL до версии 12 оптимизировала каждый подзапрос и главный запрос независимо друг от друга.
- Если WITH-запрос упоминается несколько раз, некоторые базы данных кешируют его результат, чтобы предотвратить двойное выполнение.

Полезные расширения
1. WITH как префикс DML запроса (PostgreSQL, SQL Server, SQLite)
Некоторые СУБД позволяют использовать WITH не только с запросами SELECT, но и с запросами на манипуляцию с данными (INSERT, UPDATE, DELETE).

2. WITH как цель DML (SQL Server)
SQL Server также позволяет использовать WITH-запрос как цель DML запроса, то есть создавать обновляемые представления.

3. Функции в WITH (Oracle)
Oracle, начиная с версии 12cR1 позволяет определять функции и процедуры в предложении WITH.

4. DML в WITH (PostgreSQL)
Начиная с версии 9.1 PostgreSQL поддерживает использование DML запросов внутри предложения WITH. А при использовании выражения RETURNING, предложение WITH возвращает данные в основной запрос. Таким образом, например, можно сделать запрос к только что вставленным в таблицу записям:

WITH added AS (
  INSERT INTO table1 …
  RETURNING *
)
SELECT * FROM added;

Источник: https://modern-sql.com/feature/with