CTE в SQL: WITH вместо вложенных подзапросов

Запрос, вложенный в запрос, вложенный ещё в один, превращается в кашу. Пять уровней подзапросов друг в друге читаются один раз, автором, и никем больше, включая самого автора через неделю.

WITH называет каждый шаг своим именем — ровно как доска расследования называет каждую карточку. Шаг решён — переходите к следующему, не теряя ниточку.

WITH частые AS (
  SELECT person_id, COUNT(*) AS c
  FROM receipts GROUP BY person_id
  HAVING COUNT(*) >= 5
),
с_вечера AS (
  SELECT person_id FROM receipts WHERE hour >= 22
)
SELECT p.name
FROM contacts p
JOIN частые ч ON ч.person_id = p.id
JOIN с_вечера в ON в.person_id = p.id;

«Каждый WITH — своя карточка на доске. Снимите одну — видно, на чём держится остальное дело.»

А если длину цепочки никто не знает заранее?

Второе применение — рекурсия: когда шаг ссылается сам на себя, чтобы пройти по иерархии. Оргструктура, цепочка родитель-потомок, дерево связей.

WITH RECURSIVE цепочка AS (
  SELECT id, boss_id, 1 AS уровень FROM staff WHERE id = 900
  UNION ALL
  SELECT s.id, s.boss_id, ц.уровень + 1
  FROM staff s JOIN цепочка ц ON s.boss_id = ц.id
)
SELECT * FROM цепочка;

Капкан у рекурсии один, зато абсолютный: без условия остановки шаг зовёт сам себя бесконечно. Первая строка — якорь, база без ссылки на саму себя; каждая следующая обязана когда-нибудь дойти до конца цепочки, а не заворачивать по кругу.

«Расследование без конца — не расследование, а бесконечный отчёт, который никто не дочитает.»

На работе так разбирают сложную аналитику на шаги, которые можно проверить по одному, и так же строят иерархии — от сотрудника до самого верха, от узла до корня дерева.

Дело, разбитое на шаги, можно прочитать вслух. Разберите так любое из архива — и увидите, на каком шаге рвётся ниточка.