Что такое CTE в SQL?

А, ну это же про эти ваши CTE! Объяснять, что это такое — это как пытаться рассказать про холодильник человеку, который только огонь изобрёл. Но ладно, попробую, только не зевай.

Представь, что ты пишешь здоровенный, блядь, SQL-запрос, в котором столько подзапросов, что глаза сломаешь. И вот ты сидишь, пялишься в этот пиздопроебибна код, и уже сам не помнишь, где начало, а где конец. Вот именно для таких случаев умные дядьки и придумали CTE, или, по-нашему, обобщённое табличное выражение.

Если по-простому: это как взять кусок этого адского запроса, дать ему нормальное, человеческое имя (типа department_stats) и сказать базе: «Слушай, вот эту хуйню посчитай сначала, запомни результат под таким именем, а потом мы с ним будем работать дальше». И вся эта конструкция живёт ровно столько, сколько выполняется твой большой запрос. Удобно? Ебать как удобно!

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

WITH department_stats AS (
    SELECT 
        department_id, 
        COUNT(*) as emp_count,
        AVG(salary) as avg_salary
    FROM employees
    GROUP BY department_id
)
SELECT 
    d.name,
    ds.emp_count,
    ds.avg_salary
FROM departments d
JOIN department_stats ds ON d.id = ds.department_id
WHERE ds.avg_salary > 100000;

Видишь? Всё аккуратненько. Сначала мы в department_stats посчитали, сколько в каждом отделе народу и какая у них средняя зарплата. А потом просто присоединились к этому результату, как к обычной таблице. Читать в тысячу раз легче, чем если бы этот SELECT с GROUP BY был встроен прямо в JOIN. Удивление пиздец, как всё становится понятно.

А теперь про главные фишки, ради которых это всё затевалось:

1. Читаемость, ёпта! Это основное. Ты разбиваешь монструозный запрос на логические куски, как будто главы в книге. Поддержка такого кода — не пытка, а почти удовольствие. Ну, почти.
2. Рекурсия — вот где магия! Это, блядь, самая мощная штука. Без CTE сделать рекурсивный обход, например, иерархии сотрудников (кто чей начальник) — это хуй с горы. А с CTE — красота.

   WITH RECURSIVE subordination_tree AS (
       -- Это якорь. Берём самого главного папу (CEO).
       SELECT id, name, manager_id
       FROM employees
       WHERE id = 1
       UNION ALL
       -- А это рекурсивная часть. Берём всех, чей начальник уже есть в нашем результате.
       SELECT e.id, e.name, e.manager_id
       FROM employees e
       INNER JOIN subordination_tree st ON e.manager_id = st.id
   )
   SELECT * FROM subordination_tree;

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

3. Временная замена VIEW. Не хочешь плодить вьюхи в базе на постоянку? Сделал CTE внутри запроса, использовал и забыл. Как салфетка.
4. Использовать много раз. Один раз объявил CTE, а потом в основном запросе можешь на него ссылаться несколько раз. Правда, тут как повезёт с оптимизатором — он может его закэшировать, а может и заново вычислять каждый раз, доверия ебать ноль.

Чем не подзапрос? Да всем! Ну, почти. Обычный подзапрос, особенно если он большой, встроенный в SELECT или WHERE, — это манда с ушами. Запутаться в нём — раз плюнуть. CTE же выносит эту логику на отдельный, названный уровень. А про рекурсивные штуки я уже молчу — подзапросом их вообще нихуя не сделать.

Короче, если пишешь что-то сложнее SELECT * FROM users, обязательно смотри в сторону CTE. Сначала будет непривычно, а потом сам от себя охуеешь, как раньше без них жил.