Общие табличные выражения

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

Решить проблему могут общие табличные выражения, или CTE (от англ. common table expressions). Они помогают структурировать подзапросы и выносить их за пределы основного кода.

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

Подзапросов после WITH может быть несколько: их перечисляют через запятую. После последнего подзапроса запятую не ставят — это означает начало основного запроса.

SQL
WITH
-- назначение псевдонимов и формирование подзапросов
псевдоним_1 AS (подзапрос_1),
псевдоним_2 AS (подзапрос_2),
псевдоним_3 AS (подзапрос_3),
....
псевдоним_n AS (подзапрос_n)

SELECT -- основной запрос
-- внутри основного запроса работают с псевдонимами, которые назначили в WITH
FROM псевдоним_1 INNER JOIN псевдоним_2 ... 
...
... 

В основном запросе можно обращаться к общему табличному выражению по псевдониму. Но есть одно ограничение.

❗ К общим табличным выражениям нельзя обращаться после оператора WHERE.

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

SQL
WITH 
-- первый подзапрос с псевдонимом i
i AS (SELECT billing_country AS country,
             COUNT(total) AS total_invoice
      FROM invoice
      GROUP BY billing_country
      ORDER BY total_invoice DESC
      LIMIT 5), -- подзапросы разделяют запятыми
-- второй подзапрос с псевдонимом c
 c AS (SELECT country AS country,
              COUNT(customer_id) AS total_clients
              FROM client
              GROUP BY country)

-- основной запрос, в котором указаны псевдонимы для подзапросов
SELECT i.country,
       i.total_invoice,
       c.total_clients
FROM i LEFT OUTER JOIN c ON i.country=c.country
ORDER BY i.total_invoice DESC; 

country	total_invoice	total_clients
USA	91	13
Canada	56	8
France	35	5
Brazil	35	5
Germany	28	4

Результат не изменился, зато код стал понятным и структурированным.

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

В следующем уроке вы потренируетесь использовать общие табличные выражения самостоятельно.