Подзапросы и CTE: вопросы для собеседования (часть 3)
CTE (WITH) и подзапросы позволяют разбивать сложные запросы на логические блоки. На собеседованиях проверяют умение использовать коррелированные подзапросы, EXISTS, IN, а также строить цепочки CTE для пошагового решения задач. Хороший аналитик пишет читаемый SQL, а не вложенные подзапросы на пять уровней.
Вопросы 11–15 из 20
11Какое утверждение лучше всего описывает коррелированный подзапрос?
AКоррелированный подзапрос обязан находиться в `FROM`, иначе движок не свяжет его с внешней таблицей при выполнении
BКоррелированный подзапрос обязан использовать `WITH`, чтобы движок построил связь со строками внешнего запроса
CКоррелированный подзапрос ссылается на колонки внешнего запроса (например, `o.user_id`) и оценивается отдельно для каждой внешней строки
DКоррелированный подзапрос обязательно возвращает одну строку результата, иначе SQL выкидывает ошибку выполнения
Ответ: Корреляция появляется, когда подзапрос использует алиасы или колонки из внешнего запроса.
Некоррелированный подзапрос можно выполнить «сам по себе» и получить один результат или набор. Коррелированный зависит от текущей строки внешнего запроса: в нём есть ссылка на внешний алиас (например, `o.user_id`), поэтому логически он пересчитывается для каждой внешней строки. Это важно понимать, чтобы не забыть условие связи и не получить неверный результат.
Подробный разбор → 12Вы выбираете пользователей, у которых есть хотя бы один платёж. В таблице `payments` поле `user_id` иногда бывает `NULL` (например, анонимные платежи). Почему в такой ситуации часто предпочитают `EXISTS`, а не `IN`?
A`IN` и `EXISTS` всегда полностью эквивалентны при любых данных, поэтому выбор между ними не влияет на корректность результата
BЛучше `IN`, потому что он автоматически отбрасывает `NULL` в подзапросе и поэтому не зависит от пропусков в данных
CЛучше `IN`, потому что он возвращает `TRUE`, если в подзапросе есть хотя бы один `NULL` среди значений сравниваемого столбца
DЧасто выбирают `EXISTS`, потому что он проверяет факт существования строк и не зависит от `NULL` в значениях подзапроса
Ответ: `EXISTS` отвечает на вопрос «есть ли подходящая строка», а `IN` сравнивает значения и может дать `UNKNOWN`, если список содержит `NULL`.
Предикат `x IN (subquery)` использует трёхзначную логику: если прямого совпадения нет, но в наборе есть `NULL`, результат может стать `UNKNOWN`, и строка не пройдёт фильтр. `EXISTS` не сравнивает значения и потому не «ломается» из-за `NULL`: он просто проверяет, есть ли хотя бы одна строка, удовлетворяющая условиям корреляции. Утверждения про полную эквивалентность, автоматический отброс `NULL` или возврат `TRUE` от `NULL` неверно описывают семантику `IN`.
Подробный разбор → 13Нужно найти пользователей, чей самый ранний заказ (то есть `MIN(created_at)`) попадает в 2024 год. Какой вариант корректен?
AФильтровать `orders` по диапазону `created_at` за 2024 и сгруппировать по `user_id`: возьмёт всех с любым заказом в 2024 году
BСравнить `o.created_at` с глобальным `(SELECT MIN(created_at) FROM orders)` и добавить условие на 2024 год: фильтр по глобальному минимуму, не по пользователю
CВ `CTE first_order` посчитать `MIN(created_at)` по `user_id`, затем взять пользователей с `first_dt` в диапазоне 2024 года
DВыбрать `user_id` из `users`, где идентификатор лежит в `(SELECT MIN(created_at) FROM orders GROUP BY user_id)`: сравнение идентификатора с датой через `IN`
Ответ: Сначала вычислите `MIN(created_at)` на уровне пользователя, а потом фильтруйте пользователей по году — это удобно делать через CTE.
Если просто отфильтровать `orders` по 2024 году, найдутся пользователи с любым заказом в 2024, но их первый заказ мог быть раньше. Правильная логика: получить по каждому `user_id` дату первого заказа, затем оставить тех, у кого эта дата лежит в нужном диапазоне. CTE делает этот двухшаговый расчёт понятным. Вариант с глобальным `MIN(created_at)` найдёт только пользователей с самой ранней датой во всей таблице, а сравнение `user_id` с датой через `IN` логически некорректно: типы не совпадают.
Подробный разбор → 14Вы разбираете сложный отчёт коллеги: 4 уровня вложенных подзапросов в `FROM` без имён шагов. Решаете переписать через `WITH`. Какое практическое преимущество даёт CTE по сравнению с вложенными подзапросами?
ACTE автоматически делает результаты точнее за счёт пересчёта промежуточных шагов с большей точностью
BCTE запрещает использовать `NULL` в промежуточных шагах, поэтому скрытых ошибок становится меньше
CCTE гарантирует ускорение запроса за счёт материализации и кэширования каждого шага планировщиком
DCTE даёт шагам имена, позволяет переиспользовать их и проверять промежуточные результаты отдельно
Ответ: `WITH` улучшает читаемость: вы именуете шаги и можете запускать их по отдельности для проверки.
CTE превращает сложную логику в последовательность небольших шагов: фильтрация, обогащение, агрегация. Это снижает когнитивную нагрузку и помогает обнаруживать ошибки раньше, например неправильную гранулярность или лишние строки. Кроме того, если один и тот же шаг нужен дважды, его можно переиспользовать без копирования условий. Утверждения про точность, запрет `NULL` или гарантированное ускорение — мифы, которые часто повторяют новички.
Подробный разбор → 15Нужно вывести по каждому заказу признак `has_refund`: есть ли хотя бы одна запись в `refunds` по этому заказу. Таблица `refunds` может иметь несколько строк на один заказ. Какой вариант корректен и не размножает заказы?
A`SELECT o.order_id, COUNT(r.refund_id) AS has_refund FROM orders o LEFT JOIN refunds r ON r.order_id = o.order_id;`
B`SELECT o.order_id, (SELECT r.refund_id FROM refunds r WHERE r.order_id = o.order_id) AS has_refund FROM orders o;`
C`SELECT o.order_id, MAX(r.refund_id) AS has_refund FROM orders o JOIN refunds r ON r.order_id = o.order_id;`
D`SELECT o.order_id, CASE WHEN EXISTS (SELECT 1 FROM refunds r WHERE r.order_id = o.order_id) THEN 1 ELSE 0 END AS has_refund FROM orders o;`
Ответ: `EXISTS` внутри `CASE` даёт один логический ответ на строку `orders` и не создаёт дубликаты строк.
`LEFT JOIN` к таблице `refunds` без агрегации размножает строки заказов, если по заказу несколько возвратов; добавление `COUNT()` без `GROUP BY` приведёт к ошибке. Скалярный подзапрос упадёт при нескольких строках на заказ. `INNER JOIN` с `MAX()` без `GROUP BY` тоже неверен и заодно отбросит заказы без возвратов. Конструкция `CASE WHEN EXISTS (...) THEN 1 ELSE 0 END` явно проверяет наличие связанной строки и всегда возвращает одно значение на заказ.
Подробный разбор →