Введение в WITH

Определение

WITH позволяет записывать дополнительные операторы для применения в больших запросах. Эти операторы, которые также называют общими табличными выражениями (Common Table Expressions, CTE), можно представить как определения временных таблиц, существующих только для одного запроса.

Дополнительным оператором в предложении WITH может быть SELECT, INSERT, UPDATE или DELETE, а само предложение WITH присоединяется к основному оператору, которым также может быть SELECT, INSERT, UPDATE или DELETE.

Начнем с малого. Посмотрим как с помощью WITH делать подзапросы.

Синтаксис

WITH temp_table_1 AS (
    SELECT ...
),
...
temp_table_N AS (
    SELECT ...
)
SELECT ...
   FROM temp_table_1
             ...            

Пример использования

Возьмем подзапрос определения минимальной и средней цен на товары во фразе FROM:

SELECT *
  FROM (SELECT pp.product_id,
               min (pp.price) AS price_min,
               avg (pp.price) AS price_avg
          FROM product_price pp
         GROUP BY pp.product_id
       ) as ppma

Перепишем с использованием WITH:

WITH product_price_min_avg AS (
  SELECT pp.product_id,
         min (pp.price) AS price_min,
         avg (pp.price) AS price_avg
    FROM product_price pp
   GROUP BY pp.product_id
)
SELECT *
  FROM product_price_min_avg

С помощью WITH большие запросы можно разбивать на части, как бы создавая представления. Например запрос:

SELECT p.product_id,
       p.price_min AS price_min_product,
       i.price_min AS price_min_purchase,
       p.price_avg AS price_avg_product,
       i.price_avg AS price_avg_purchase
  FROM (SELECT pp.product_id,
               min (pp.price) AS price_min,
               avg (pp.price) AS price_avg
          FROM product_price pp
         GROUP BY pp.product_id
       ) p,
       (SELECT pi.product_id,
               min (pi.price) AS price_min,
               avg (pi.price) AS price_avg
          FROM purchase_item pi
         GROUP BY pi.product_id
       ) i
 WHERE i.product_id = p.product_id
 ORDER BY p.product_id

можно переписать так:

WITH product_price_min_avg AS (
  SELECT pp.product_id,
         min (pp.price) AS price_min,
         avg (pp.price) AS price_avg
    FROM product_price pp
   GROUP BY pp.product_id
),
purchase_price_min_avg AS (
  SELECT pi.product_id,
         min (pi.price) AS price_min,
         avg (pi.price) AS price_avg
    FROM purchase_item pi
   GROUP BY pi.product_id
)
SELECT p.product_id,
       p.price_min AS price_min_product,
       i.price_min AS price_min_purchase,
       p.price_avg AS price_avg_product,
       i.price_avg AS price_avg_purchase
  FROM product_price_min_avg p,
       purchase_price_min_avg i
 WHERE i.product_id = p.product_id
 ORDER BY p.product_id

Основная часть запроса выглядит просто

SELECT p.product_id,
       p.price_min AS price_min_product,
       i.price_min AS price_min_purchase,
       p.price_avg AS price_avg_product,
       i.price_avg AS price_avg_purchase
  FROM product_price_min_avg p,
       purchase_price_min_avg i
 WHERE i.product_id = p.product_id
 ORDER BY p.product_id

Логику по вычислению минимальной и средней цены мы спрятали в product_price_min_avg и purchase_price_min_avg.

На product_price_min_avg и purchase_price_min_avg можно смотреть как на представления в SQL, но только для одного запроса. Или как на функции в других языках программирования.