Подзапросы в WHERE

Подзапросы в WHERE

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

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

Подзапрос — это обычный SELECT, заключённый в скобки и помещённый внутрь другого SQL-выражения. Сначала выполняется внутренний запрос, его результат передаётся во внешний.

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

Внутренний запрос (SELECT AVG(salary) FROM employees) вычисляет среднюю зарплату. Внешний запрос использует это значение как порог. Всё в одном выражении, без временных переменных.

Подзапрос, возвращающий одно значение (scalar subquery)

Когда подзапрос возвращает ровно одно значение — это скалярный подзапрос. Его можно использовать везде, где допустимо скалярное выражение: в WHERE, SELECT, HAVING.

-- Сотрудники с зарплатой выше средней по компании
SELECT name, department, salary
FROM   employees
WHERE  salary > (SELECT AVG(salary) FROM employees)
ORDER BY salary DESC;

Если подзапрос вернёт больше одной строки или больше одной колонки — PostgreSQL выдаст ошибку: «more than one row returned by a subquery used as an expression».

Проверь себя: что произойдёт, если убрать AVG и написать (SELECT salary FROM employees)?

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

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

-- Клиенты, которые сделали хотя бы один заказ
SELECT name, email
FROM   customers
WHERE  id IN (SELECT customer_id FROM orders);

Подзапрос (SELECT customer_id FROM orders) возвращает все customer_id из таблицы orders. IN проверяет вхождение id клиента в этот список. Результат — только клиенты, у которых есть заказы.

Та же логика через JOIN:

SELECT DISTINCT c.name, c.email
FROM   customers c JOIN orders o ON c.id = o.customer_id;

Оба варианта дают одинаковый результат. Какой выбрать — вопрос читаемости и контекста.

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

NOT IN с подзапросом находит строки, которых нет в другой таблице:

-- Клиенты без заказов
SELECT name, email
FROM   customers
WHERE  id NOT IN (SELECT customer_id FROM orders WHERE customer_id IS NOT NULL);

Важный нюанс: если подзапрос возвращает хотя бы один NULL, NOT IN вернёт пустой результат. Это классическая ловушка — подробнее о ней в модуле 4 (урок о NULL). Поэтому в NOT IN подзапросе всегда добавляйте WHERE col IS NOT NULL.

Альтернатива — NOT EXISTS (следующий урок), которая безопаснее работает с NULL.

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

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

-- Сотрудники, зарабатывающие больше средней зарплаты в своём отделе
SELECT name, department, salary
FROM   employees e1
WHERE  salary > (
    SELECT AVG(salary)
    FROM   employees e2
    WHERE  e2.department = e1.department   -- ссылка на внешнюю таблицу
);

e2.department = e1.department — это корреляция: для каждой строки внешнего запроса (сотрудника e1) подзапрос вычисляет среднюю зарплату именно по его отделу.

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

Вложенность подзапросов

Подзапросы могут вкладываться многократно:

-- Отделы, где хотя бы один сотрудник зарабатывает больше средней по компании
SELECT DISTINCT department
FROM   employees
WHERE  department IN (
    SELECT department
    FROM   employees
    WHERE  salary > (SELECT AVG(salary) FROM employees)
);

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

Глубокая вложенность быстро становится трудночитаемой. В следующих уроках увидим CTE — способ именовать промежуточные результаты и избегать излишней вложенности.

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

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

Подзапрос удобнее:

• Когда промежуточный результат — одно значение (агрегат)
• Когда логика читается как «найди строки, у которых условие выполняется в другой таблице»
• NOT IN / NOT EXISTS часто выразительнее, чем LEFT JOIN ... WHERE IS NULL

JOIN удобнее:

• Когда нужны колонки из обеих таблиц в результате
• Когда связь простая и прямая (один к одному или один ко многим)
• JOIN обычно быстрее коррелированного подзапроса на больших данных

-- Подзапрос: читается как «клиенты, у которых есть завершённые заказы»
SELECT name FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE status = 'completed');

-- JOIN: нужны колонки из обеих таблиц
SELECT c.name, o.id, o.amount
FROM customers c
JOIN orders o ON c.id = o.customer_id AND o.status = 'completed';

Практический пример: топ товаров по продажам

Задача: найти товары, которые продавались чаще, чем в среднем по всем товарам:

SELECT p.title,
       COUNT(oi.id) AS times_ordered
FROM   products    p
JOIN   order_items oi ON oi.product_id = p.id
GROUP BY p.id, p.title
HAVING COUNT(oi.id) > (
    SELECT AVG(order_count)
    FROM (
        SELECT COUNT(id) AS order_count
        FROM   order_items
        GROUP BY product_id
    ) sub
)
ORDER BY times_ordered DESC;

Подзапрос в HAVING вычисляет среднее количество заказов на товар — сначала группирует order_items по product_id, потом усредняет. Внешний запрос оставляет только товары выше среднего.

Подзапрос в HAVING

Подзапросы в HAVING работают так же, как в WHERE — часто для сравнения агрегата с агрегатом:

-- Отделы, где средняя зарплата выше средней по всей компании
SELECT department, AVG(salary) AS dept_avg
FROM   employees
GROUP BY department
HAVING AVG(salary) > (SELECT AVG(salary) FROM employees);

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

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

Некоррелированный подзапрос выполняется один раз и его результат используется повторно. Коррелированный выполняется для каждой строки внешнего запроса — N выполнений при N строках.

-- Некоррелированный: AVG считается один раз
WHERE salary > (SELECT AVG(salary) FROM employees)

-- Коррелированный: для каждого сотрудника — отдельный AVG по его отделу
WHERE salary > (SELECT AVG(salary) FROM employees WHERE department = e.department)

На таблицах с тысячами строк разница существенная. PostgreSQL иногда оптимизирует коррелированные подзапросы, но JOIN с GROUP BY обычно надёжнее для производительности.

Краткий итог

• Подзапрос — SELECT в скобках внутри другого запроса; сначала выполняется внутренний
• Скалярный подзапрос возвращает одно значение; используется в WHERE, HAVING, SELECT
• Подзапрос с IN / NOT IN возвращает список значений
• NOT IN + NULL в подзапросе даёт пустой результат — всегда добавляйте IS NOT NULL
• Коррелированный подзапрос ссылается на внешний запрос; выполняется для каждой строки
• Подзапрос или JOIN — оба варианта часто эквивалентны; выбор за читаемостью

Типичная ошибка: подзапрос возвращает несколько строк

-- Ошибка: подзапрос вернёт несколько строк, а нужно одно значение
SELECT name FROM employees
WHERE salary = (SELECT salary FROM employees WHERE department = 'IT');
-- ERROR: more than one row returned by a subquery used as an expression

Если несколько сотрудников в IT-отделе имеют разные зарплаты, подзапрос вернёт несколько строк. Скалярный подзапрос ожидает ровно одно значение. Решение: использовать агрегат (MAX, MIN, AVG) или оператор IN вместо =:

-- Правильно: найти сотрудников с зарплатой как у самого высокооплачиваемого в IT
SELECT name FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees WHERE department = 'IT');

-- Или: все сотрудники с зарплатой, встречающейся в IT-отделе
SELECT name FROM employees
WHERE salary IN (SELECT salary FROM employees WHERE department = 'IT');

Что дальше

Вы научились использовать подзапросы в WHERE. В следующем уроке — подзапросы в SELECT и FROM: вложенные запросы как производные таблицы и вычисляемые значения в каждой строке.