Полное руководство по подзапросам в SQL: типы, применение и оптимизация

Подзапросы — один из самых мощных инструментов SQL, но их разнообразие часто вызывает вопросы: какой тип использовать, когда применять CTE вместо обычного подзапроса, и чем LATERAL отличается от коррелированного подзапроса? В этой статье разберем все основные виды подзапросов, их особенности и типичные сценарии использования.

Классификация по месту использования

Подзапрос в FROM (табличное выражение)

Табличные выражения, также известные как derived tables, создают временную таблицу, с которой можно работать как с обычной.

SELECT dept, avg_salary
FROM (
    SELECT department AS dept, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
) subquery
WHERE avg_salary > 50000;

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

Необходима предварительная агрегация или трансформация данных
Нужно применить фильтрацию после группировки
Требуется разбить сложный запрос на логические шаги для улучшения читаемости

Важная особенность: такой подзапрос не имеет доступа к другим таблицам из основного запроса — он полностью независим.

Скалярный подзапрос в SELECT

Скалярный подзапрос возвращает ровно одно значение (одна строка, один столбец) и может использоваться везде, где ожидается единичное значение.

SELECT 
    name,
    salary,
    (SELECT AVG(salary) FROM employees) AS company_avg,
    salary - (SELECT AVG(salary) FROM employees) AS diff_from_avg
FROM employees;

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

Нужно добавить единственное вычисленное значение к каждой строке результата
Требуется простая агрегация для сравнения
Выполняется вычисление константы для всего результата

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

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

Самый распространенный вид — фильтрация результатов на основе данных из других таблиц или агрегаций.

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

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

Подзапрос с оператором IN

SELECT name, department
FROM employees
WHERE department_id IN (
    SELECT id FROM departments WHERE location = 'New York'
);

Подзапрос с оператором EXISTS

SELECT name, email
FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.customer_id = c.id 
    AND o.created_at > '2024-01-01'
);

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

Фильтрация по условиям из другой таблицы
Проверка существования связанных записей (EXISTS)
Сравнение со множеством значений (IN)
Исключение записей (NOT EXISTS, NOT IN)

Классификация по зависимости от внешнего запроса

Некоррелированные (независимые) подзапросы

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

SELECT name, salary, department
FROM employees
WHERE department_id IN (
    SELECT id FROM departments WHERE location = 'New York'
);

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

Выполняются только один раз
Обычно быстрее коррелированных
Легче оптимизируются планировщиком запросов

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

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

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

Здесь подзапрос ссылается на e1.department из внешнего запроса, поэтому выполняется заново для каждого сотрудника.

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

Необходимо сравнение с данными из той же группы
Вычисления специфичны для каждой строки
Требуется построчная зависимость

Недостаток: может быть медленным на больших объемах данных, так как выполняется N раз.

LATERAL — коррелированные подзапросы в FROM

LATERAL позволяет использовать коррелированные подзапросы в секции FROM, что открывает новые возможности.

SELECT c.name, recent_orders.order_id, recent_orders.amount
FROM customers c
CROSS JOIN LATERAL (
    SELECT order_id, amount, created_at
    FROM orders
    WHERE customer_id = c.id
    ORDER BY created_at DESC
    LIMIT 5
) recent_orders;

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

Получение TOP-N записей для каждой группы
Нужно вернуть несколько строк и столбцов для каждой записи
Применение табличных функций с параметрами из внешнего запроса
Сложная логика с использованием LIMIT и ORDER BY

Отличие от обычного JOIN: LATERAL может ссылаться на таблицы, указанные левее в FROM, и поддерживает LIMIT внутри подзапроса.

CTE (Common Table Expressions) — WITH запросы

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

Простой CTE

WITH high_earners AS (
    SELECT id, name, salary, department
    FROM employees 
    WHERE salary > 100000
),
department_stats AS (
    SELECT 
        department, 
        COUNT(*) as high_earner_count,
        AVG(salary) as avg_high_salary
    FROM high_earners
    GROUP BY department
)
SELECT * FROM department_stats
WHERE high_earner_count > 5;

Рекурсивный CTE

Рекурсивные CTE незаменимы для работы с иерархическими данными.

WITH RECURSIVE subordinates 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, s.level + 1
    FROM employees e
    JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates
ORDER BY level, name;

Когда использовать CTE:

Улучшение читаемости за счет именования подзапросов
Переиспользование одного подзапроса несколько раз в одном запросе
Рекурсивные запросы для иерархий, графов, путей
Сложная многошаговая логика, где каждый шаг логически отделен

Оконные функции как альтернатива подзапросам

Хотя оконные функции технически не являются подзапросами, они часто решают те же задачи, но более эффективно.

SELECT 
    name,
    salary,
    department,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg,
    salary - AVG(salary) OVER (PARTITION BY department) AS diff_from_avg,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_in_dept
FROM employees;

Когда использовать вместо подзапросов:

Агрегация без группировки — каждая строка сохраняется в результате
Ранжирование и нумерация строк
Скользящие окна и накопительные суммы
Обычно быстрее коррелированных подзапросов

Сравним коррелированный подзапрос и оконную функцию:

-- Коррелированный подзапрос (медленнее)
SELECT name, salary,
    (SELECT AVG(salary) FROM employees e2 
     WHERE e2.department = e1.department) AS dept_avg
FROM employees e1;

-- Оконная функция (быстрее)
SELECT name, salary,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;

Сравнение типов подзапросов

Табличные выражения в FROM располагаются в секции FROM и возвращают полноценную таблицу. Они не имеют доступа к внешним таблицам запроса, что делает их полностью независимыми и обеспечивает хорошую производительность благодаря однократному выполнению.

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

Подзапросы в WHERE могут возвращать как одно значение, так и множество для операторов IN или ANY. Коррелированные варианты видят внешние таблицы и выполняются для каждой строки, что влияет на производительность при работе с большими датасетами.

EXISTS — специализированный подзапрос в WHERE, возвращающий булево значение. Всегда коррелированный и имеет доступ к внешним таблицам. Отличается хорошей производительностью, так как прерывает выполнение после первого найденного совпадения.

LATERAL используется в FROM и возвращает таблицу, но в отличие от обычных табличных выражений может ссылаться на таблицы, расположенные левее в той же секции FROM. Это делает его коррелированным, что отражается на средней производительности, но открывает уникальные возможности для TOP-N запросов.

CTE (WITH-запросы) определяются в начале запроса и возвращают таблицу. Обычные CTE не имеют доступа к внешним таблицам, но рекурсивные CTE могут ссылаться на сами себя. Современные СУБД хорошо оптимизируют CTE, обеспечивая хорошую производительность.

Оконные функции располагаются в SELECT и возвращают значение для каждой строки без группировки. Они имеют доступ к текущей строке и соседним строкам в окне. Демонстрируют отличную производительность и часто являются лучшей альтернативой коррелированным подзапросам для задач агрегации и ранжирования.

Практические рекомендации

Предобработка данных перед JOIN

-- CTE для читаемости
WITH active_users AS (
    SELECT * FROM users 
    WHERE status = 'active' AND last_login > CURRENT_DATE - 30
)
SELECT u.name, o.order_count
FROM active_users u
JOIN (
    SELECT user_id, COUNT(*) as order_count
    FROM orders
    GROUP BY user_id
) o ON u.id = o.user_id;

TOP-N для каждой группы

-- Вариант 1: LATERAL (поддерживает LIMIT)
SELECT d.name, top_emp.*
FROM departments d
CROSS JOIN LATERAL (
    SELECT name, salary 
    FROM employees 
    WHERE department_id = d.id 
    ORDER BY salary DESC 
    LIMIT 3
) top_emp;

-- Вариант 2: Оконная функция
SELECT * FROM (
    SELECT 
        name, 
        salary, 
        department_id,
        ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) as rn
    FROM employees
) ranked
WHERE rn <= 3;

Проверка существования

-- EXISTS (оптимально!)
SELECT c.name, c.email
FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

-- IN (может быть медленнее на больших данных)
SELECT c.name, c.email
FROM customers c
WHERE c.id IN (SELECT customer_id FROM orders);

EXISTS обычно эффективнее, так как прекращает проверку после первого совпадения.

Иерархические данные

Для работы с деревьями и графами единственный стандартный вариант — рекурсивный CTE:

WITH RECURSIVE category_tree AS (
    SELECT id, name, parent_id, 1 as depth, ARRAY[id] as path
    FROM categories
    WHERE parent_id IS NULL
    
    UNION ALL
    
    SELECT c.id, c.name, c.parent_id, ct.depth + 1, ct.path || c.id
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.id
    WHERE NOT c.id = ANY(ct.path) -- защита от циклов
)
SELECT * FROM category_tree;

Приоритет выбора решения

Оконные функции — если задача связана с агрегацией или ранжированием без группировки
CTE (WITH) — для улучшения читаемости и переиспользования логики
EXISTS — для проверки существования связанных записей
JOIN — часто предпочтительнее подзапросов в WHERE
LATERAL — когда нужен TOP-N или табличные функции с параметрами
Коррелированные подзапросы — когда другие способы не подходят

Заключение

Понимание различных типов подзапросов и их особенностей позволяет писать не только функциональный, но и эффективный SQL-код. Ключевые принципы:

Начинайте с простоты и читаемости кода
Используйте EXPLAIN для анализа производительности
Оптимизируйте только когда есть реальная проблема
Предпочитайте оконные функции коррелированным подзапросам для агрегаций
Не бойтесь CTE — современные СУБД хорошо их оптимизируют

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

Полное руководство по подзапросам в SQL: типы, применение и оптимизация

Классификация по месту использования

Подзапрос в FROM (табличное выражение)

Скалярный подзапрос в SELECT

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

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

Подзапрос с оператором IN

Подзапрос с оператором EXISTS

Классификация по зависимости от внешнего запроса

Некоррелированные (независимые) подзапросы

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

LATERAL — коррелированные подзапросы в FROM

CTE (Common Table Expressions) — WITH запросы

Простой CTE

Рекурсивный CTE

Оконные функции как альтернатива подзапросам

Сравнение типов подзапросов

Практические рекомендации

Предобработка данных перед JOIN

TOP-N для каждой группы

Проверка существования

Иерархические данные

Приоритет выбора решения

Заключение

Читайте также