Что такое подзапросы в SQL и для чего они нужны?

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

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

Подзапрос (subquery) — это SQL-запрос внутри другого запроса. Он выполняется отдельно и результат используется во внешнем запросе. Подзапросы позволяют разложить сложную логику на несколько этапов и часто заменяют JOINы более читаемым способом.

Типы подзапросов

1. Скалярный подзапрос (Scalar Subquery)

Возвращает одно значение (одна строка, один столбец).

-- Найти все заказы, сумма которых больше средней
SELECT order_id, total
FROM orders
WHERE total > (SELECT AVG(total) FROM orders);

-- Получить ФИО сотрудника с максимальной зарплатой
SELECT name, salary
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);

Характеристики:

• Возвращает ровно одно значение
• Используется в WHERE, SELECT, HAVING
• Выполняется один раз (в большинстве СУБД)
• Коррелированный или некоррелированный

2. Строковый подзапрос (Row Subquery)

Возвращает одну строку с несколькими столбцами.

-- Найти заказ с максимальной суммой и максимальным количеством товаров
SELECT order_id, customer_id
FROM orders
WHERE (total, item_count) = (
    SELECT MAX(total), MAX(item_count)
    FROM orders
);

3. Табличный подзапрос (Table Subquery)

Возвращает таблицу (несколько строк и столбцов).

-- Найти заказы клиентов, которые потратили больше 1000
SELECT order_id, customer_id, total
FROM orders
WHERE customer_id IN (
    SELECT customer_id
    FROM orders
    GROUP BY customer_id
    HAVING SUM(total) > 1000
);

Расположение подзапросов

В SELECT (для вычисленного столбца)

-- Показать каждого сотрудника с его зарплатой и средней зарплатой в компании
SELECT 
    name,
    salary,
    (SELECT AVG(salary) FROM employees) AS avg_salary,
    salary - (SELECT AVG(salary) FROM employees) AS diff
FROM employees;

В FROM (подзапрос как таблица)

-- Найти средний заказ по каждому клиенту, затем среднее от этих средних
SELECT 
    AVG(customer_avg) AS overall_avg
FROM (
    SELECT 
        customer_id,
        AVG(total) AS customer_avg
    FROM orders
    GROUP BY customer_id
) AS customer_averages;

В WHERE (фильтрация)

-- Найти клиентов, у которых есть хотя бы один заказ
SELECT name
FROM customers
WHERE customer_id IN (
    SELECT DISTINCT customer_id
    FROM orders
);

В HAVING (фильтрация агрегатов)

-- Найти категории товаров, где средняя цена выше глобальной
SELECT 
    category,
    AVG(price) AS avg_price
FROM products
GROUP BY category
HAVING AVG(price) > (SELECT AVG(price) FROM products);

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

Коррелированный подзапрос ссылается на столбцы из внешнего запроса. Выполняется для каждой строки внешнего запроса (медленнее).

-- Найти все заказы, сумма которых выше средней для этого клиента
SELECT o1.order_id, o1.customer_id, o1.total
FROM orders o1
WHERE o1.total > (
    SELECT AVG(o2.total)
    FROM orders o2
    WHERE o2.customer_id = o1.customer_id  -- Коррелированное условие
);

-- Это медленнее, чем с JOINом:
SELECT o.order_id, o.customer_id, o.total
FROM orders o
JOIN (
    SELECT customer_id, AVG(total) AS avg_total
    FROM orders
    GROUP BY customer_id
) avg_orders ON o.customer_id = avg_orders.customer_id
WHERE o.total > avg_orders.avg_total;

Операторы для подзапросов

IN / NOT IN

-- Найти клиентов, у которых есть заказы
SELECT name FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders);

-- Найти клиентов БЕЗ заказов
SELECT name FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders);

EXISTS / NOT EXISTS

-- Найти клиентов с заказами (более эффективно)
SELECT name FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.customer_id = c.customer_id
);

-- Найти клиентов БЕЗ заказов
SELECT name FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o
    WHERE o.customer_id = c.customer_id
);

EXISTS vs IN:

• EXISTS часто быстрее (останавливается после первого совпадения)
• IN может быть медленнее при большом подзапросе
• Предпочитай EXISTS для больших таблиц

Операторы сравнения (=, >, <, >=, <=, <>)

-- Заказы с суммой больше максимальной средней
SELECT * FROM orders
WHERE total > (SELECT MAX(avg_total) FROM ...)

ANY / ALL

-- Заказы, сумма которых больше ЛЮБОГО заказа Клиента #1
SELECT * FROM orders
WHERE total > ANY (
    SELECT total FROM orders
    WHERE customer_id = 1
);

-- Заказы, сумма которых больше ВСЕ заказов Клиента #1
SELECT * FROM orders
WHERE total > ALL (
    SELECT total FROM orders
    WHERE customer_id = 1
);

Проблемы с подзапросами

1. Производительность

-- МЕДЛЕННО: подзапрос выполняется для каждой строки
SELECT name, salary FROM employees e1
WHERE salary > (
    SELECT AVG(salary) FROM employees e2
    WHERE e2.department_id = e1.department_id
);

-- БЫСТРО: один раз вычисляем и присоединяем
SELECT e.name, e.salary FROM employees e
JOIN (
    SELECT department_id, AVG(salary) AS avg_sal
    FROM employees
    GROUP BY department_id
) avg_dept ON e.department_id = avg_dept.department_id
WHERE e.salary > avg_dept.avg_sal;

2. NULL значения в IN

-- ОШИБКА: если подзапрос содержит NULL, результат всегда NULL
SELECT name FROM customers
WHERE customer_id IN (
    SELECT customer_id FROM orders
    WHERE order_date > "2024-01-01"
    UNION ALL
    SELECT NULL  -- NULL заражает весь результат
);
-- Лучше использовать EXISTS

3. Отсутствие оптимизации

Некоторые СУБД плохо оптимизируют подзапросы, поэтому:

-- Часто лучше переписать как JOIN или CTE
WITH dept_avg AS (
    SELECT department_id, AVG(salary) AS avg_sal
    FROM employees
    GROUP BY department_id
)
SELECT e.name, e.salary, da.avg_sal
FROM employees e
JOIN dept_avg da ON e.department_id = da.department_id;

Common Table Expressions (CTE) vs Subqueries

-- Подзапрос (вложенность растёт)
SELECT * FROM (
    SELECT * FROM (
        SELECT * FROM orders WHERE total > 100
    ) o1 WHERE customer_id IN (SELECT customer_id FROM customers)
) o2;

-- CTE (более читаемо)
WITH large_orders AS (
    SELECT * FROM orders WHERE total > 100
),
valid_customers AS (
    SELECT customer_id FROM customers
)
SELECT * FROM large_orders
WHERE customer_id IN (SELECT customer_id FROM valid_customers);

Когда использовать подзапросы

Используй подзапросы:

• Для простых, одноразовых логик
• Когда результат — одно значение (скалярный)
• В SELECT для вычисленных столбцов
• С EXISTS/NOT EXISTS

Избегай подзапросов:

• Вместо этого используй JOIN для связывания таблиц
• Используй CTE для читаемости и повторного использования
• Используй Window Functions вместо группировки

Практический пример

-- Найти TOP-3 клиентов по сумме заказов и их последний заказ
WITH customer_totals AS (
    SELECT 
        customer_id,
        SUM(total) AS total_spent,
        ROW_NUMBER() OVER (ORDER BY SUM(total) DESC) AS rank
    FROM orders
    GROUP BY customer_id
),
top_customers AS (
    SELECT customer_id FROM customer_totals
    WHERE rank <= 3
),
last_orders AS (
    SELECT 
        customer_id,
        order_id,
        total,
        order_date,
        ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
    FROM orders
    WHERE customer_id IN (SELECT customer_id FROM top_customers)
)
SELECT 
    c.name,
    ct.total_spent,
    lo.order_id,
    lo.order_date
FROM customers c
JOIN customer_totals ct ON c.customer_id = ct.customer_id
LEFT JOIN last_orders lo ON c.customer_id = lo.customer_id AND lo.rn = 1
WHERE ct.rank <= 3
ORDER BY ct.total_spent DESC;

Итого

Подзапросы — мощный инструмент для структурирования SQL логики, но часто лучший выбор — это JOIN для связей, CTE для читаемости, Window Functions для аналитики.