CTE в MySQL/MariaDB

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

Примечание. Минимальные версии, поддерживаемые CTE: MySQL 8, MariaDB 10.2.1

Схема использования CTE в MySQL:

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

WITH
  cte1 AS (SELECT a, b FROM table1),
  cte2 AS (SELECT c, d FROM table2)
SELECT b, d FROM cte1 JOIN cte2
WHERE cte1.a = cte2.c;

Таким образом, создав два представления cte1 и cte2 можно их использовать для дальнейших условий и выборок новых данных.

Ркурсивный CTE

CTE является рекурсивным, если его подзапрос ссылается на его собственное имя. Если планируется использовать рекурсивный CTE то в запрос должен быть включен параметр RECURSIVE.

Рекурсивный CTE полезен для решения задач дерева, например, когда нужно получить все дочерние идентификаторы какой-либо записи, при условии, что структура БД построена по принципу Nested Set.

1. Простой пример

WITH RECURSIVE cte (id) AS
(
  SELECT 1
  UNION ALL
  SELECT id + 1 FROM cte WHERE id < 5
)
SELECT * FROM cte;

Результат:

+------+
| id    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+

2. Более сложный пример

WITH RECURSIVE cte AS
(
  SELECT 1 AS id, 'test' AS txt
  UNION ALL
  SELECT id + 1, CONCAT(txt, txt) FROM cte WHERE id < 3
)
SELECT * FROM cte;

Результат

+------+-------+
| n    | txt   |
+------+-------+
|    1 | test  |
|    2 | test  |
|    3 | test  |
+------+-------+

Рекурсивный CTE-запрос состоит из двух частей, разделенных UNION [ALL] или UNION DISTINCT. 

1. Первая часть - SELECT, который создает начальную строку и не ссылается на имя CTE;
2. Вторая часть -  SELECT, который создает дополнительные строки и рекурсии, ссылаясь на имя CTE в блоке FROM.

Рекурсия заканчивается тогда, когда вторая часть не создает новых строк. Таким образом, рекурсивный CTE состоит из нерекурсивной SELECT части, за которой следует рекурсивная SELECT часть.

Если нерекурсивная и рекурсивная части разделены UNION DISTINCT то повторяющиеся строки будут удалены. Это полезно для запросов, которые выполняют транзитивные замыкания, чтобы избежать бесконечных циклов.