← вся теориятеория к собесу · SQL

Подзапросы и CTE в SQL

Зачем это спрашивают

CTE и подзапросы - про то, как ты собираешь сложный запрос из кусков. Дежурные вопросы: «CTE или подзапрос», «EXISTS или IN», обход дерева рекурсией. Проверяют сразу две вещи - вкус к читаемости и знание ловушек с пропусками.

Типовые формулировки: «перепиши этот запрос читаемо», «найди сотрудников без подчинённых», «выведи всё дерево категорий».

// Мантру «CTE - это барьер оптимизации» до сих пор носят на собесы как непреложную истину. Она устарела несколько лет назад, и это твой шанс блеснуть - если знаешь, чем именно её заменили.

CTE: запрос, который можно читать сверху вниз

WITH даёт подзапросу имя и превращает нагромождение скобок в линейный пайплайн: шаг первый, шаг второй, итог. CTE (common table expression) - это инструмент читаемости и переиспользования, а не ускорения; семантически он равен обычному подзапросу.

Про барьер оптимизации. Раньше Postgres всегда считал CTE отдельно и целиком, что действительно мешало планировщику. С версии 12 он вклеивает CTE в общий план, если тот используется один раз и не помечен явно. Так что старое правило теперь просто неверно.

Материализация - это когда база считает результат CTE целиком, складывает во временную структуру и дальше читает уже её. Иногда это выигрыш (тяжёлый шаг считается один раз вместо трёх), иногда проигрыш (планировщик не может протолкнуть фильтр внутрь и читает лишнее). Управляется словами MATERIALIZED и NOT MATERIALIZED.

// Правило вкуса, по которому легко жить: три уровня вложенных подзапросов - сигнал переписать на CTE. Тот, кто будет ревьюить запрос, скажет спасибо, и с большой вероятностью это будешь ты сам через месяц.

EXISTS против IN: разница, которая ломает отчёты

EXISTS проверяет сам факт наличия и останавливается на первой найденной строке. IN сначала собирает список значений целиком. На больших подзапросах EXISTS обычно быстрее, но это не главное различие.

Главное - поведение с пропусками, и его стоит проверить руками. Возьми список значений 1, 2 и NULL и спроси, каких из чисел 1 и 5 в нём нет. NOT EXISTS честно вернёт пятёрку. NOT IN вернёт пустоту - вообще ничего, при любых данных.

Причина в том самом третьем значении. Условие «5 не входит в список» раскладывается в «5 ≠ 1 И 5 ≠ 2 И 5 ≠ NULL». Первые два условия истинны, третье даёт «неизвестно», и вся цепочка становится «неизвестно». WHERE пропускает только честную истину, поэтому не проходит ни одна строка. Запрос при этом отрабатывает без ошибок и возвращает пустой отчёт.

// Коррелированный подзапрос - тот, что ссылается на строку внешнего запроса и концептуально выполняется для каждой из них. Планировщик часто разворачивает такое в джойн, но не всегда, и тогда на миллионной таблице получается N+1 прямо внутри базы: один проход по внешней таблице плюс отдельный запрос на каждую строку. Лечится джойном с предагрегатом или оконной функцией.

скалярный подзапрос: подзапрос, стоящий на месте значения: обязан вернуть ровно одну строку и одну колонку, иначе база падает с ошибкой

LATERAL и рекурсивный обход

LATERAL - подзапрос в FROM, которому видны колонки уже перечисленных таблиц. Звучит абстрактно, а нужен для очень конкретной вещи: «три последних заказа каждого юзера» без всяких оконок, или вызов табличной функции по одной строке за раз. В SQL Server та же конструкция называется CROSS APPLY и OUTER APPLY.

WITH RECURSIVE обходит иерархии: оргструктуру, дерево категорий, граф зависимостей. Устроен он из двух частей, склеенных через UNION ALL. Первая - база: строки, с которых начинаем, скажем все узлы верхнего уровня. Вторая - шаг: запрос, который ссылается на сам CTE и добывает следующий уровень. База отрабатывает один раз, шаг повторяется, пока приносит новые строки, и на пустом результате рекурсия останавливается.

// Если в графе есть цикл, шаг будет приносить новые строки вечно, и запрос не остановится никогда. Защита - обязательная часть ответа: либо ограничение глубины отдельной колонкой-счётчиком, либо массив уже посещённых узлов, в который на каждом шаге проверяется вхождение.

UNION ALL: склейка результатов двух запросов без удаления дубликатов. Обычный UNION дубликаты убирает и потому заметно дороже

Как отвечать: «Чем EXISTS лучше IN?»

По механике EXISTS проверяет факт существования и останавливается на первой найденной строке, а IN сначала материализует весь список значений, поэтому на больших подзапросах EXISTS обычно дешевле. Но решающее различие не в скорости, а в семантике пропусков: NOT IN с NULL внутри подзапроса возвращает пустой результат при любых данных, потому что сравнение с NULL даёт «неизвестно», и цепочка условий перестаёт быть истинной. NOT EXISTS от этого свободен. Поэтому анти-джойны я пишу через NOT EXISTS или через LEFT JOIN с проверкой на IS NULL, а IN оставляю для коротких списков констант, где NULL взяться неоткуда.

Три слоя в одном ответе: механика, смертельный случай с пропусками и практическое правило выбора. Причём порядок правильный - сначала то, что ломает отчёты, потом то, что их замедляет.

На чём валят

−NOT IN (SELECT …), где в подзапросе есть NULL: пустой ответ без единой ошибки. Пиши NOT EXISTS.
−Коррелированный подзапрос в SELECT на большой таблице - это N+1 внутри базы; переписывается джойном или оконкой.
−Рекурсивный CTE без защиты от циклов на графе, где цикл есть: запрос не остановится.
−«Вынес в CTE - значит оптимизировал»: CTE про читаемость, план может не измениться или стать хуже.
−Скалярный подзапрос, который однажды вернёт две строки: запрос падает в проде, а не на тесте.

Проверьте себя

Пять вопросов из банка по этой подтеме. Всего их 34, остальные разбираются в тренажёре.

#subqueries_cte1 / 5
Посчитать месячный retention: доля юзеров с заказом в месяце M, вернувшихся с заказом в M+1. Какой каркас запроса верный?
A)Свести к парам (user, месяц), self-join когорты M с активностью M+1, поделить
B)COUNT(DISTINCT user_id) с фильтром WHERE сразу по двум соседним месяцам через OR
C)GROUP BY user_id HAVING COUNT(month) = 2 по всем заказам юзера
D)Одной оконной функцией вообще без привязки к конкретному месяцу
показать ответ и разбор
+A)Свести к парам (user, месяц), self-join когорты M с активностью M+1, поделить
// разбор: Ключевые шаги: нормализация к «юзер-месяц» (иначе несколько заказов задвоят когорту), точное соответствие M → M+1 (self-join по user_id и month + interval '1 month' или LAG(month) по юзеру), деление на размер когорты месяца M. Вариант C теряет привязку к соседним месяцам. Это каркас всех когортных отчётов — от ретеншена до LTV.
#subqueries_cte2 / 5
Почему условие WHERE col = NULL не находит строки, где col равен NULL?
A)Потому что значение NULL при сравнении автоматически неявно превращается в пустую строку
B)Потому что оператор = вообще не работает со строковыми колонками
C)Сравнение с NULL даёт UNKNOWN, а не TRUE; для NULL нужен IS NULL
D)Потому что NULL хранится в отдельной системной таблице СУБД
показать ответ и разбор
+C)Сравнение с NULL даёт UNKNOWN, а не TRUE; для NULL нужен IS NULL
// разбор: NULL означает «неизвестно», и любое сравнение с ним (=, <>, даже NULL = NULL) даёт третье логическое значение UNKNOWN, а WHERE пропускает только TRUE. Поэтому строки с NULL ищут через IS NULL / IS NOT NULL, а не через =. Это же — корень бага NOT IN с NULL в списке.
#subqueries_cte3 / 5
Что делает коррелированный подзапрос и чем он отличается от обычного?
A)Ссылается на строку внешнего запроса и логически вычисляется как бы заново для каждой его строки отдельно
B)Это подзапрос, который выполняется строго один раз ещё до внешнего запроса и потом кэширует свой готовый результат
C)Подзапрос, который обязательно должен вернуть ровно одну строку и одну колонку, иначе он вообще не сможет запуститься
D)Это просто синоним CTE: коррелированный подзапрос и WITH-выражение — по сути одно и то же, лишь названные по-разному
показать ответ и разбор
+A)Ссылается на строку внешнего запроса и логически вычисляется как бы заново для каждой его строки отдельно
// разбор: Коррелированный подзапрос ссылается на колонки внешнего запроса и логически вычисляется заново для каждой строки внешнего (например, «заказы, где сумма выше средней ПО ЭТОМУ пользователю»). Обычный (некоррелированный) подзапрос от внешнего не зависит и считается один раз. Коррелированный удобен, но потенциально дорог (оптимизатор часто переписывает его в join); он не обязан возвращать одну строку и не равен CTE.
#subqueries_cte4 / 5
Проверяем «есть ли у юзера заказы» через NOT IN (SELECT user_id FROM orders). Чем безопаснее NOT EXISTS?
A)Ничем: NOT IN и NOT EXISTS эквивалентны между собой на данных
B)NOT EXISTS быстрее потому, что EXISTS использует индекс, а конструкция IN — не использует
C)NOT IN не получится применять к подзапросу — там допустим явный перечисленный список значений
D)Если в подзапросе есть NULL, NOT IN вернёт пусто (NULL-логика); NOT EXISTS обрабатывает NULL корректно
показать ответ и разбор
+D)Если в подзапросе есть NULL, NOT IN вернёт пусто (NULL-логика); NOT EXISTS обрабатывает NULL корректно
// разбор: Если подзапрос NOT IN возвращает хоть один NULL, всё условие превращается в UNKNOWN, и запрос не вернёт НИ одной строки (проверено: 0 против корректных 2) — тихая классическая ловушка. NOT EXISTS работает через проверку наличия совпадений и на NULL ведёт себя корректно (и часто эффективнее). Поэтому для анти-джойна предпочитают NOT EXISTS (или LEFT JOIN ... IS NULL). Эквивалентны они лишь при гарантированно ненулевом подзапросе.
#subqueries_cte5 / 5
Нужно развернуть всю цепочку начальников сотрудника до самого верха (иерархия произвольной глубины). Какой инструмент?
A)Обычный self-join таблицы с собой один раз — он раскроет всю цепочку начальников
B)Рекурсивный CTE (WITH RECURSIVE): якорь плюс шаг, идущий по manager_id вверх до самой вершины
C)GROUP BY manager_id с каким-нибудь агрегатом — группировка сама соберёт всю полную цепочку начальников для сотрудника
D)Оконная функция LAG по manager_id — она аккуратно поднимется по всем уровням иерархии начальников за один проход
показать ответ и разбор
+B)Рекурсивный CTE (WITH RECURSIVE): якорь плюс шаг, идущий по manager_id вверх до самой вершины
// разбор: Иерархию произвольной (заранее неизвестной) глубины разворачивают рекурсивным CTE: WITH RECURSIVE — якорный запрос (сам сотрудник) UNION ALL рекурсивный шаг, присоединяющий начальника текущего уровня по manager_id, пока не упрётся в вершину (проверено: цепочка 3→2→1). Один self-join раскрывает только ОДИН уровень, GROUP BY цепочку не строит, а LAG смотрит на соседа по сортировке, а не вверх по дереву.

дальше

Теорию прочитали. Навык ставится повторением

В Сеньорчике эта подтема идёт в ежедневных сессиях: движок возвращает её, пока ответы не станут уверенными, и ведёт прогресс отдельно по каждой подтеме. Теория внутри тоже бесплатна, лимит только на количество вопросов в день.

Открыть тренажёр Вся тема: SQL