0Pricing
SQL Academy · Урок

Коррелированные подзапросы

Подзапрос, зависящий от внешней строки

«Коррелированные подзапросы» — бесплатный урок SQL Academy на CoddyKit. Это урок 1 из 4. Ты можешь прочитать весь урок бесплатно ниже — а потом практиковать его прямо в браузере с встроенным редактором кода и ИИ-репетитором 24/7. Это часть пути обучения SQL Academy, и твой прогресс синхронизируется между веб-версией и приложением CoddyKit. Курс SQL Academy содержит 4 уроков всего.

Что такое коррелированный подзапрос

Коррелированный подзапрос — это подзапрос, который обращается к столбцу из внешнего (охватывающего) запроса. В отличие от обычного подзапроса, который выполняется один раз и возвращает фиксированный результат, коррелированный подзапрос вычисляется один раз для каждой строки, обрабатываемой внешним запросом.

Это делает такие подзапросы мощным инструментом для построчных сравнений, но они также затратнее простых подзапросов.

Обычный и коррелированный подзапрос

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

В приведённом ниже примере внутренний SELECT обращается к e1.department_id из внешнего запроса, создавая корреляцию.

-- Regular subquery (runs once)
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- Correlated subquery (runs once per outer row)
SELECT name, salary, department_id
FROM employees e1
WHERE salary > (
  SELECT AVG(salary)
  FROM employees e2
  WHERE e2.department_id = e1.department_id
);

Создание примеров таблиц

Создадим две таблицы, которые будем использовать на протяжении всего урока: employees и departments. Они содержат реалистичные данные для демонстрации коррелированных подзапросов в разных сценариях.

CREATE TABLE departments (
  id   INT PRIMARY KEY,
  name VARCHAR(50)
);

CREATE TABLE employees (
  id            INT PRIMARY KEY,
  name          VARCHAR(50),
  department_id INT REFERENCES departments(id),
  salary        NUMERIC(10,2),
  hire_date     DATE
);

INSERT INTO departments VALUES (1,'Engineering'),(2,'Sales'),(3,'HR');

INSERT INTO employees VALUES
  (1,'Alice',  1, 90000, '2020-03-01'),
  (2,'Bob',    1, 75000, '2021-06-15'),
  (3,'Carol',  2, 60000, '2019-01-10'),
  (4,'David',  2, 68000, '2022-09-01'),
  (5,'Eve',    3, 55000, '2020-07-20'),
  (6,'Frank',  1, 95000, '2018-11-05'),
  (7,'Grace',  3, 52000, '2023-02-28'),
  (8,'Henry',  2, 71000, '2021-04-12');

Сотрудники с зарплатой выше средней по отделу

Классический пример использования коррелированных подзапросов: найти каждого сотрудника, чья зарплата превышает среднюю зарплату в его отделе. Внутренний запрос заново вычисляет среднюю зарплату отдела для каждой строки сотрудника во внешнем запросе.

SELECT e1.name, e1.department_id, e1.salary
FROM employees e1
WHERE e1.salary > (
  SELECT AVG(e2.salary)
  FROM employees e2
  WHERE e2.department_id = e1.department_id
)
ORDER BY e1.department_id, e1.salary DESC;

Использование коррелированных подзапросов в SELECT

Коррелированные подзапросы не ограничиваются предложением WHERE — они также могут находиться в списке SELECT, вычисляя значение для каждой строки. Здесь мы получаем зарплату каждого сотрудника вместе со средней зарплатой в его отделе — всё в одном запросе.

SELECT
  e.name,
  e.salary,
  (
    SELECT ROUND(AVG(e2.salary), 2)
    FROM employees e2
    WHERE e2.department_id = e.department_id
  ) AS dept_avg_salary
FROM employees e
ORDER BY e.department_id, e.name;

Поиск самого высокооплачиваемого сотрудника в каждом отделе

С помощью коррелированного подзапроса можно найти сотрудника с максимальной зарплатой в каждом отделе. Внутренний запрос находит максимальную зарплату для отдела текущей строки, а внешний запрос оставляет только соответствующую ей строку.

SELECT e1.name, e1.department_id, e1.salary
FROM employees e1
WHERE e1.salary = (
  SELECT MAX(e2.salary)
  FROM employees e2
  WHERE e2.department_id = e1.department_id
)
ORDER BY e1.department_id;

EXISTS с коррелированным подзапросом

Оператор EXISTS часто используется вместе с коррелированными подзапросами. Он возвращает TRUE, если внутренний запрос создаёт хотя бы одну строку. Здесь мы перечисляем все отделы, в которых есть хотя бы один сотрудник, нанятый до 2021 года.

SELECT d.name AS department
FROM departments d
WHERE EXISTS (
  SELECT 1
  FROM employees e
  WHERE e.department_id = d.id
    AND e.hire_date < '2021-01-01'
);

NOT EXISTS с коррелированным подзапросом

NOT EXISTS действует противоположным образом: он возвращает TRUE, когда коррелированный подзапрос не находит ни одной подходящей строки. Это полезно для поиска родительских записей без дочерних, например отделов без сотрудников.

SELECT d.name AS department_without_employees
FROM departments d
WHERE NOT EXISTS (
  SELECT 1
  FROM employees e
  WHERE e.department_id = d.id
);

Коррелированный подзапрос в UPDATE

Коррелированные подзапросы работают и внутри операторов UPDATE. В следующем примере добавляется столбец dept_avg, а затем коррелированный подзапрос заполняет его средней зарплатой отдела каждого сотрудника.

ALTER TABLE employees ADD COLUMN dept_avg NUMERIC(10,2);

UPDATE employees e1
SET dept_avg = (
  SELECT ROUND(AVG(e2.salary), 2)
  FROM employees e2
  WHERE e2.department_id = e1.department_id
);

SELECT name, salary, dept_avg FROM employees ORDER BY department_id, name;

Коррелированный подзапрос в DELETE

Коррелированный подзапрос также можно использовать в операторе DELETE, чтобы удалять строки на основе данных из связанной таблицы. Приведённый ниже запрос удаляет сотрудников, чья зарплата меньше 60 % от средней зарплаты их отдела, — это шаблон очистки данных.

DELETE FROM employees e1
WHERE e1.salary < (
  SELECT AVG(e2.salary) * 0.60
  FROM employees e2
  WHERE e2.department_id = e1.department_id
);

SELECT name, salary, department_id FROM employees ORDER BY department_id;

Совет по производительности: коррелированный подзапрос и JOIN

Коррелированные подзапросы выполняются один раз для каждой внешней строки, что может быть медленно на больших таблицах. Многие коррелированные подзапросы можно переписать как JOIN с производной таблицей или CTE, чтобы повысить производительность. Изучите оба шаблона и выбирайте между ними с учётом удобства чтения и плана выполнения.

-- Correlated version (potentially slower)
SELECT e1.name, e1.salary
FROM employees e1
WHERE e1.salary > (
  SELECT AVG(e2.salary)
  FROM employees e2
  WHERE e2.department_id = e1.department_id
);

-- Equivalent JOIN + derived table (often faster)
SELECT e.name, e.salary
FROM employees e
JOIN (
  SELECT department_id, AVG(salary) AS avg_sal
  FROM employees
  GROUP BY department_id
) dept_avg ON dept_avg.department_id = e.department_id
WHERE e.salary > dept_avg.avg_sal;

Быстрая проверка

Проверьте, насколько хорошо Вы поняли коррелированные подзапросы.

Повторение: коррелированные подзапросы

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

  • Они могут находиться в SELECT, WHERE, UPDATE и DELETE.
  • EXISTS / NOT EXISTS естественным образом сочетаются с коррелированными подзапросами для проверки связанных строк.
  • Они выразительны, но могут работать медленно — если производительность важна, рассмотрите возможность переписать их как JOIN.
  • Именно псевдоним внешней таблицы внутри подзапроса создаёт корреляцию.

Часто задаваемые вопросы

Урок «Коррелированные подзапросы» бесплатный?

Да — полный текст урока «Коррелированные подзапросы» бесплатно доступен здесь в веб-версии. Чтобы практиковать его интерактивно (встроенный редактор кода и ИИ-репетитор 24/7) и разблокировать остальной курс SQL Academy, подпишись на CoddyKit PRO. Курс SQL Academy содержит 4 уроков всего.

Чему я научусь в уроке «Коррелированные подзапросы»?

Подзапрос, зависящий от внешней строки Ты практикуешь SQL Academy с помощью реального кода, который запускаешь прямо в браузере, и ИИ-репетитор 24/7 отвечает на твои вопросы во время урока.

Нужен ли мне опыт, чтобы начать SQL Academy?

Предыдущий опыт не требуется. SQL Academy на CoddyKit структурирован для всех уровней — от новичков до продвинутых, поэтому ты можешь начать отсюда или с самого начала и учиться в своем темпе. Это урок 1 из 4.

Сколько времени занимает урок «Коррелированные подзапросы»?

Большинство уроков CoddyKit занимают около 5–10 минут. Каждый из них компактный и интерактивный, поэтому ты постоянно делаешь прогресс и продолжаешь с того же места в веб-версии и приложении.

Можно ли писать и запускать код в этом уроке SQL Academy?

Да. Каждый урок SQL Academy включает встроенный редактор кода, поэтому ты пишешь и запускаешь реальный код прямо в браузере и получаешь моментальную обратную связь от AI — локальная установка не требуется.

Все уроки этого курса

  1. Коррелированные подзапросы
  2. EXISTS и NOT EXISTS
  3. IN, ANY и ALL
  4. Производительность EXISTS и JOIN
← Назад к SQL Academy