Смотрите также:

План обучения SQLОсновы и базовые выборкиАгрегация и группировкаОбъединение таблицПодзапросы и CTEОконные функцииМодификация данных и структура БД
29/10/2026

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

Диалект — PostgreSQL. Подзапросы в WHERE, FROM, SELECT, коррелированные подзапросы, CTE, рекурсивные CTE.

sqlsubquerycte

0. Что такое подзапрос

Подзапрос — это запрос, вложенный внутрь другого запроса. Он может находиться в SELECT, FROM, WHERE, HAVING.

Зачем нужны подзапросы?

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

1. Подзапросы в WHERE: IN, =, сравнения

Подзапрос с IN: значение из списка

Найдём сотрудников, которые работают в отделах с бюджетом больше 2 500 000.

SELECT name, salary
FROM employees
WHERE department_id IN (
    SELECT id
    FROM departments
    WHERE budget > 2500000
);

Как это работает:

  1. Сначала выполняется внутренний запрос: SELECT id FROM departments WHERE budget > 2500000 → возвращает [1, 2] (IT и Продажи).
  2. Затем внешний запрос: WHERE department_id IN (1, 2) → берёт сотрудников из этих отделов.

→ Анна (120 000), Иван (80 000), Мария (130 000), Елена (90 000), Павел (110 000), Сергей (85 000).

💡 Важно: подзапрос с IN должен возвращать один столбец. Иначе будет ошибка.

Подзапрос с =, >, <: скалярный результат

Найдём сотрудников, которые зарабатывают больше средней зарплаты по компании.

SELECT name, salary
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
);

Внутренний запрос возвращает одно число (95 000), и внешний запрос сравнивает с ним зарплату каждого сотрудника.

→ Анна (120 000), Мария (130 000), Павел (110 000).

⚠️ Осторожно: если подзапрос вернёт больше одной строки, будет ошибка. Используй IN, если ожидаешь несколько значений.

Подзапрос с EXISTS: проверка существования

EXISTS возвращает TRUE, если подзапрос вернул хотя бы одну строку.

Найдём отделы, в которых есть хотя бы один сотрудник с премией.

SELECT d.name
FROM departments d
WHERE EXISTS (
    SELECT 1
    FROM employees e
    WHERE e.department_id = d.id
      AND e.bonus IS NOT NULL
);

→ IT, Продажи, Маркетинг.

💡 EXISTS vs IN:

  • EXISTS быстрее, если подзапрос возвращает много строк (он останавливается, как только нашёл первую).
  • IN удобнее, если подзапрос возвращает мало значений.
  • EXISTS работает с NULL, а IN со списком, содержащим NULL, может дать неожиданный результат.

2. Подзапросы в FROM: производные таблицы

Подзапрос в FROM создаёт временную таблицу, с которой работает внешний запрос.

Пример: средняя зарплата по отделам, но только для отделов с бюджетом > 2 000 000

SELECT dept_avg.department_name, dept_avg.avg_salary
FROM (
    SELECT d.name AS department_name, AVG(e.salary) AS avg_salary
    FROM employees e
    JOIN departments d ON e.department_id = d.id
    GROUP BY d.name
) AS dept_avg
WHERE dept_avg.avg_salary > 80000;

→ IT, 120000.

Как это работает:

  1. Внутренний запрос создаёт временную таблицу dept_avg с двумя столбцами.
  2. Внешний запрос фильтрует эту таблицу по avg_salary > 80000.

⚠️ Важно: производная таблица обязательно должна иметь псевдоним (AS dept_avg), иначе будет ошибка.

💡 Когда использовать: когда нужно сначала агрегировать данные, а потом отфильтровать результат агрегации (то, что нельзя сделать через HAVING, если фильтр зависит от нескольких агрегатов).

3. Подзапросы в SELECT: скалярные значения

Подзапрос в SELECT должен возвращать одно значение для каждой строки.

Пример: сотрудники с указанием средней зарплаты по их отделу

SELECT
    e.name,
    e.salary,
    (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id) AS dept_avg_salary
FROM employees e;

→ Анна (120 000, 120 000), Иван (80 000, 85 000), Мария (130 000, 120 000), Андрей (70 000, 72 500), Елена (90 000, 85 000), Павел (110 000, 120 000), Дарья (75 000, 72 500), Сергей (85 000, 85 000).

💡 Когда использовать: когда нужно добавить к каждой строке агрегированное значение из связанной таблицы.

⚠️ Осторожно: если подзапрос вернёт больше одной строки, будет ошибка.

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

Коррелированный подзапрос — это подзапрос, который ссылается на столбцы из внешнего запроса. Он выполняется для каждой строки внешнего запроса.

Пример: сотрудники, которые зарабатывают больше среднего по их отделу

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

Как это работает:

  1. Для каждой строки внешнего запроса (каждого сотрудника e1) выполняется внутренний запрос.
  2. Внутренний запрос считает среднюю зарплату именно для того отдела, в котором работает текущий сотрудник (e1.department_id).
  3. Сравнивается зарплата сотрудника со средней по его отделу.

→ Анна (120 000, отдел 1), Мария (130 000, отдел 1), Елена (90 000, отдел 2), Дарья (75 000, отдел 3).

💡 Производительность: коррелированные подзапросы могут быть медленными, потому что выполняются для каждой строки. Если можно переписать через JOIN + GROUP BY — делай это.

Альтернатива через JOIN:

SELECT e.name, e.salary, e.department_id
FROM employees e
JOIN (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
) AS dept_avg ON e.department_id = dept_avg.department_id
WHERE e.salary > dept_avg.avg_salary;

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

5. CTE (Common Table Expressions): конструкция WITH

CTE — это именованный временной набор данных, который можно использовать в запросе. Делает код гораздо чище и читаемее.

Базовый синтаксис

WITH cte_name AS (
    SELECT ...
)
SELECT * FROM cte_name;

Пример: средняя зарплата по отделам, затем фильтрация

Вместо подзапроса в FROM:

WITH dept_avg AS (
    SELECT d.name AS department_name, AVG(e.salary) AS avg_salary
    FROM employees e
    JOIN departments d ON e.department_id = d.id
    GROUP BY d.name
)
SELECT department_name, avg_salary
FROM dept_avg
WHERE avg_salary > 80000;

Результат такой же, как в примере с подзапросом в FROM, но код гораздо понятнее.

💡 Преимущества CTE:

  • Код читается сверху вниз, как инструкция.
  • Можно использовать CTE несколько раз в одном запросе.
  • Легче отлаживать (можно выполнить CTE отдельно).

Несколько CTE в одном запросе

WITH
    dept_avg AS (
        SELECT department_id, AVG(salary) AS avg_salary
        FROM employees
        GROUP BY department_id
    ),
    high_earners AS (
        SELECT e.name, e.salary, e.department_id
        FROM employees e
        JOIN dept_avg d ON e.department_id = d.department_id
        WHERE e.salary > d.avg_salary
    )
SELECT name, salary
FROM high_earners;

Здесь мы создали два CTE: dept_avg и high_earners, и использовали dept_avg внутри high_earners.

6. Рекурсивные CTE (упоминаем, но не углубляемся)

Рекурсивные CTE используются для работы с иерархическими данными (например, организационная структура, дерево категорий).

Пример: организационная структура

WITH RECURSIVE org_chart AS (
    -- Базовый случай: топ-менеджер
    SELECT id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Рекурсивный шаг: подчинённые
    SELECT e.id, e.name, e.manager_id, o.level + 1
    FROM employees e
    JOIN org_chart o ON e.manager_id = o.id
)
SELECT * FROM org_chart;

💡 Когда использовать: когда нужно обойти дерево (например, «найди всех подчинённых этого менеджера на всех уровнях»).

⚠️ Осторожно: рекурсивные CTE могут зациклиться, если в данных есть цикл. Всегда добавляй условие остановки.

7. Подзапрос vs CTE vs JOIN: когда что использовать

СитуацияЧто использовать
Нужно один раз получить агрегат и использовать егоПодзапрос в WHERE или SELECT
Нужно агрегировать, а потом отфильтровать результатCTE или подзапрос в FROM
Нужно сравнить строку с агрегатом по группеКоррелированный подзапрос или JOIN + GROUP BY
Сложная логика с несколькими шагамиCTE (чище и понятнее)
Нужно обойти деревоРекурсивный CTE
Простое объединение таблицJOIN (быстрее)

💡 Общее правило: если можешь написать через JOIN — пиши через JOIN. Если логика сложная и требует промежуточных шагов — используй CTE.

8. Практика: 8 задач на этот этап

Попробуй решить самостоятельно, не подглядывая ниже.

  1. Найди сотрудников, которые работают в отделах с бюджетом больше 3 000 000 (используй IN).
  2. Найди сотрудников, которые зарабатывают больше средней зарплаты по компании (используй подзапрос с >).
  3. Найди отделы, в которых есть хотя бы один сотрудник с зарплатой больше 100 000 (используй EXISTS).
  4. Выведи сотрудников с указанием средней зарплаты по их отделу (используй подзапрос в SELECT).
  5. Найди сотрудников, которые зарабатывают больше среднего по их отделу (используй коррелированный подзапрос).
  6. Перепиши задачу 5 через JOIN + GROUP BY (без коррелированного подзапроса).
  7. Используя CTE, найди отделы, где средняя зарплата больше 90 000, и выведи названия этих отделов и количество сотрудников.
  8. Используя несколько CTE, найди сотрудников, которые зарабатывают больше среднего по их отделу, и выведи их имена, зарплаты и названия отделов.
🧐 Ответы для самопроверки
-- 1
SELECT name, salary
FROM employees
WHERE department_id IN (
    SELECT id
    FROM departments
    WHERE budget > 3000000
);

-- 2
SELECT name, salary
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
);

-- 3
SELECT d.name
FROM departments d
WHERE EXISTS (
    SELECT 1
    FROM employees e
    WHERE e.department_id = d.id
      AND e.salary > 100000
);

-- 4
SELECT
    e.name,
    e.salary,
    (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id) AS dept_avg_salary
FROM employees e;

-- 5
SELECT e1.name, e1.salary, e1.department_id
FROM employees e1
WHERE e1.salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.department_id = e1.department_id
);

-- 6
SELECT e.name, e.salary, e.department_id
FROM employees e
JOIN (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
) AS dept_avg ON e.department_id = dept_avg.department_id
WHERE e.salary > dept_avg.avg_salary;

-- 7
WITH dept_avg AS (
    SELECT d.name AS department_name, AVG(e.salary) AS avg_salary, COUNT(e.id) AS employee_count
    FROM employees e
    JOIN departments d ON e.department_id = d.id
    GROUP BY d.name
    HAVING AVG(e.salary) > 90000
)
SELECT department_name, employee_count
FROM dept_avg;

-- 8
WITH dept_avg AS (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
)
SELECT e.name, e.salary, d.name AS department_name
FROM employees e
JOIN dept_avg da ON e.department_id = da.department_id
JOIN departments d ON e.department_id = d.id
WHERE e.salary > da.avg_salary;

Чеклист, что этап освоен

Ты готова к пятому этапу, если можешь без подсказки объяснить:

  • чем коррелированный подзапрос отличается от обычного;
  • почему подзапрос в SELECT должен возвращать одно значение;
  • чем EXISTS отличается от IN;
  • зачем нужны CTE и в чём их преимущество перед подзапросами;
  • когда лучше использовать JOIN вместо подзапроса.

On this page