Анатомия коррелированного подзапроса
Узнайте, как внутренний запрос обращается к внешней строке и работает модель выполнения для каждой строки.
«Анатомия коррелированного подзапроса» — бесплатный урок SQL Interview Prep на CoddyKit. Это урок 1 из 4. Ты можешь прочитать весь урок бесплатно ниже — а потом практиковать его прямо в браузере с встроенным редактором кода и ИИ-репетитором 24/7. Это часть пути обучения SQL Interview Prep, и твой прогресс синхронизируется между веб-версией и приложением CoddyKit. Курс SQL Interview Prep содержит 4 уроков всего.
Что делает подзапрос коррелированным
Интервьюеры делят подзапросы на две группы. Обычный (некоррелированный) подзапрос может выполняться самостоятельно. Коррелированный подзапрос обращается к столбцу из внешнего запроса, поэтому не может выполняться отдельно.
- Некоррелированный: вычисляется один раз, а результат повторно используется для каждой внешней строки.
- Коррелированный: вычисляется заново для каждой внешней строки, поскольку зависит от неё.
Характерный признак — столбец внешней таблицы, появляющийся внутри внутреннего запроса. Заметив его, Вы сразу распознаете этот шаблон.
Модель выполнения построчно
Представьте, что СУБД перебирает внешние строки. Для каждой внешней строки она подставляет значения этой строки во внутренний запрос, выполняет его и использует результат, чтобы что-то определить или вычислить.
Это ментальная модель, которую на собеседовании от вас хотят услышать: «внутренний запрос выполняется один раз для каждой внешней строки».
Эта формулировка также подсказывает классический уточняющий вопрос: коррелированные подзапросы могут работать медленно, потому что внутренний запрос может выполняться тысячи раз. Мы исправим это в уроке 4.
Как найти ссылку на внешнюю строку
Здесь таблица сотрудников и внешний псевдоним для сотрудника с окладом e1 управляют внутренним запросом, который читает e1.dept_id. Эта ссылка на внешнюю строку и есть корреляция.
Уберите префикс псевдонима, и внутренний запрос больше не скомпилируется сам по себе. Именно эта зависимость делает его коррелированным.
SELECT e1.name, e1.salary
FROM employees e1
WHERE e1.salary > (
SELECT AVG(e2.salary)
FROM employees e2
WHERE e2.dept_id = e1.dept_id
);Как прочитать этот запрос вслух
Переведите предыдущий запрос на обычный язык так, как вы сделали бы это на собеседовании:
«Для каждого сотрудника e1 найдите среднюю зарплату в его отделе и оставьте сотрудника только в том случае, если он зарабатывает больше этой средней зарплаты».
Условие WHERE e2.dept_id = e1.dept_id во внутреннем запросе связывает среднее значение с отделом этого сотрудника. Без этой строки вы сравнивали бы всех сотрудников со средним значением по всей компании.
Псевдонимы обязательны
Когда внутренний и внешний запрос обращаются к одной и той же таблице, необходимо задать псевдонимы для обоих запросов, чтобы СУБД понимала, к какой строке относится столбец.
e1= внешняя строка, которую проверяют.e2= внутренний проход по таблице.
Уберите псевдонимы, и столбец отдела станет неоднозначным; многие СУБД затем молча привяжут его к внутренней таблице, разрушив корреляцию. Собеседующие намеренно допускают именно эту ошибку.
Коррелированный подзапрос в SELECT
Коррелированные подзапросы не ограничиваются WHERE. В списке SELECT они создают вычисляемый столбец, который снова вычисляется для каждой внешней строки.
Ниже каждый заказ показывает, сколько других заказов оформил тот же клиент. Внутренний подсчет связан с внешним запросом через o.customer_id.
SELECT o.order_id,
o.customer_id,
(SELECT COUNT(*)
FROM orders o2
WHERE o2.customer_id = o.customer_id) AS customer_order_count
FROM orders o;Скалярное значение — ровно одно значение
Коррелированный подзапрос, используемый в SELECT или сравниваемый с =, >, <, должен возвращать для каждой внешней строки одно скалярное значение.
Если он возвращает несколько строк, СУБД выдаёт ошибку, например «подзапрос возвращает больше одной строки».
Агрегаты, такие как COUNT, MAX или AVG, гарантируют одно значение, поэтому их часто используют внутри скалярных коррелированных подзапросов. Знание этого правила помогает избежать частой неожиданности во время выполнения.
Когда подзапрос возвращает NULL
Скалярный коррелированный подзапрос может не найти ни одной внутренней строки. Тогда агрегат возвращает NULL (а COUNT возвращает 0).
Это значение NULL попадает во внешнее выражение. Сравнения с NULL дают UNKNOWN, поэтому внешняя строка может быть молча исключена.
Если нужен запасной вариант, оберните подзапрос в COALESCE. На собеседовании часто спрашивают, что произойдёт, если внутренняя строка не найдена, и ожидают, что вы упомянете поведение NULL.
SELECT c.customer_id,
COALESCE((SELECT MAX(o.amount)
FROM orders o
WHERE o.customer_id = c.customer_id), 0) AS biggest_order
FROM customers c;Пример: дата последнего заказа
Распространённая задача: показать каждого клиента вместе с датой его самого последнего заказа. Коррелированный подзапрос в SELECT решает её напрямую.
Для каждой строки клиента внутренний запрос находит максимальную дату заказа с помощью MAX для этого клиента через o.customer_id = c.customer_id.
SELECT c.customer_id,
c.name,
(SELECT MAX(o.order_date)
FROM orders o
WHERE o.customer_id = c.customer_id) AS last_order_date
FROM customers c;Почему это может работать медленно
Поскольку внутренний запрос выполняется один раз для каждой внешней строки, коррелированный подзапрос над большой внешней таблицей может запустить миллионы внутренних выполнений.
- Индекс по связанному столбцу — здесь
orders.customer_id— позволяет быстро завершать каждый внутренний запуск. - Без индекса каждый запуск может просматривать всю таблицу, что даёт примерно O(n*m) работы.
На собеседовании всегда упоминайте индекс и переписывание запроса с помощью соединения как способы повысить производительность.
Коррелированный и некоррелированный варианты рядом
Разница всего в одной строке. Некоррелированный вариант сравнивает всех со средним значением по компании, а коррелированный — каждого сотрудника со средним значением в его отделе.
Прочитайте оба варианта и обратите внимание, как единственная строка WHERE e2.dept_id = e1.dept_id меняет весь смысл.
-- Uncorrelated: one global average, computed once
SELECT name FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
-- Correlated: per-department average, recomputed per row
SELECT e1.name FROM employees e1
WHERE e1.salary > (
SELECT AVG(e2.salary) FROM employees e2
WHERE e2.dept_id = e1.dept_id
);Быстрая проверка
Проверьте, насколько хорошо вы поняли, что определяет коррелированный подзапрос.
Итоги: строение коррелированного подзапроса
Главное:
- Коррелированный подзапрос ссылается на внешнюю строку и выполняется один раз для каждой внешней строки.
- Если обе таблицы одинаковы, задавайте псевдонимы для обеих, чтобы корреляция оставалась однозначной.
- При скалярном использовании подзапрос должен возвращать ровно одно значение; отсутствие совпадений даёт NULL, поэтому используйте
COALESCE. - Подзапрос может находиться в SELECT или WHERE, а производительность зависит от наличия индекса по связанному столбцу.
Скажите на собеседовании «выполняется один раз для каждой внешней строки» — и вы точно выразите суть концепции.
Часто задаваемые вопросы
Урок «Анатомия коррелированного подзапроса» бесплатный?
Да — полный текст урока «Анатомия коррелированного подзапроса» бесплатно доступен здесь в веб-версии. Чтобы практиковать его интерактивно (встроенный редактор кода и ИИ-репетитор 24/7) и разблокировать остальной курс SQL Interview Prep, подпишись на CoddyKit PRO. Курс SQL Interview Prep содержит 4 уроков всего.
Чему я научусь в уроке «Анатомия коррелированного подзапроса»?
Узнайте, как внутренний запрос обращается к внешней строке и работает модель выполнения для каждой строки. Ты практикуешь SQL Interview Prep с помощью реального кода, который запускаешь прямо в браузере, и ИИ-репетитор 24/7 отвечает на твои вопросы во время урока.
Нужен ли мне опыт, чтобы начать SQL Interview Prep?
Предыдущий опыт не требуется. SQL Interview Prep на CoddyKit структурирован для всех уровней — от новичков до продвинутых, поэтому ты можешь начать отсюда или с самого начала и учиться в своем темпе. Это урок 1 из 4.
Сколько времени занимает урок «Анатомия коррелированного подзапроса»?
Большинство уроков CoddyKit занимают около 5–10 минут. Каждый из них компактный и интерактивный, поэтому ты постоянно делаешь прогресс и продолжаешь с того же места в веб-версии и приложении.
Можно ли писать и запускать код в этом уроке SQL Interview Prep?
Да. Каждый урок SQL Interview Prep включает встроенный редактор кода, поэтому ты пишешь и запускаешь реальный код прямо в браузере и получаешь моментальную обратную связь от AI — локальная установка не требуется.
Все уроки этого курса
- Анатомия коррелированного подзапроса
- Агрегаты по группам без GROUP BY
- Коррелированные EXISTS и NOT EXISTS
- Переписывание коррелированных подзапросов через JOIN