PrepBro
← Назад к вопросам

Что такое подзапросы в SQL и какие виды подзапросов бывают?

1.0 Junior🔥 121 комментариев
#SQL и базы данных

Комментарии (1)

🐱
claude-haiku-4.5PrepBro AI26 мар. 2026 г.(ред.)

Ответ сгенерирован нейросетью и может содержать ошибки

Подзапросы в SQL: виды и применение

Подзапрос (Subquery) — это запрос внутри другого запроса. Это мощный инструмент для решения сложных задач аналитики. Разберу виды подзапросов, их применение и правильное использование.

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

Подзапрос — это SELECT запрос, встроенный в другой SELECT, INSERT, UPDATE или DELETE. Результат подзапроса используется как данные для основного запроса.

-- Простой пример
SELECT * FROM users
WHERE user_id IN (
    SELECT user_id FROM orders
    WHERE amount > 1000
);
-- Подзапрос в скобках вернёт список user_id, у которых были заказы > 1000

Виды подзапросов по расположению

1. Подзапрос в WHERE

Назначение: Фильтрация строк основного запроса.

-- Найти всех пользователей, которые когда-либо купили что-то
SELECT user_id, name FROM users
WHERE user_id IN (
    SELECT user_id FROM orders
);

-- С использованием EXISTS (более эффективно)
SELECT u.user_id, u.name FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.user_id = u.user_id
);

2. Подзапрос в FROM (Inline View)

Назначение: Создать временную таблицу для использования в основном запросе.

-- Найти пользователей с средним заказом выше среднего
SELECT * FROM (
    SELECT 
        user_id,
        COUNT(*) as order_count,
        AVG(amount) as avg_order
    FROM orders
    GROUP BY user_id
) user_stats
WHERE avg_order > (
    SELECT AVG(amount) FROM orders
);

3. Подзапрос в SELECT (Scalar Subquery)

Назначение: Добавить столбец с результатом подзапроса к каждой строке.

-- Для каждого пользователя показать количество его заказов
SELECT 
    u.user_id,
    u.name,
    (SELECT COUNT(*) FROM orders WHERE user_id = u.user_id) as order_count,
    (SELECT SUM(amount) FROM orders WHERE user_id = u.user_id) as total_spent
FROM users u;

⚠️ Внимание: Скалярные подзапросы медленные для больших таблиц! Лучше использовать JOIN.

4. Подзапрос в HAVING

Назначение: Фильтрация групп по агрегированным значениям.

-- Найти товары, средняя цена которых выше средней цены всех товаров
SELECT 
    product_id,
    AVG(price) as avg_price
FROM products
GROUP BY product_id
HAVING AVG(price) > (
    SELECT AVG(price) FROM products
);

Виды подзапросов по типу результата

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

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

-- Найти пользователя с максимальным заказом
SELECT * FROM users
WHERE user_id = (
    SELECT user_id FROM orders
    ORDER BY amount DESC
    LIMIT 1
);

-- Или через WITH (CTE)
WITH top_order AS (
    SELECT user_id FROM orders
    ORDER BY amount DESC
    LIMIT 1
)
SELECT * FROM users
WHERE user_id = (SELECT user_id FROM top_order);

2. Подзапросы со списком (Row Subquery)

Возвращает: Несколько строк (одна колонка).

-- Найти пользователей из списка конкретных городов
SELECT * FROM users
WHERE city IN (
    SELECT city FROM cities
    WHERE country = 'Russia'
);

-- Эквивалентно с JOIN (обычно быстрее)
SELECT DISTINCT u.* FROM users u
JOIN cities c ON u.city = c.city
WHERE c.country = 'Russia';

3. Подзапросы, возвращающие таблицу

Возвращает: Несколько строк и столбцов.

-- Таблица с несколькими столбцами
SELECT * FROM (
    SELECT 
        user_id,
        order_date,
        amount,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY amount DESC) as rank
    FROM orders
) ranked_orders
WHERE rank = 1;  -- Самый крупный заказ каждого пользователя

Виды подзапросов по зависимости

1. Коррелированные подзапросы (Correlated Subquery)

Описание: Подзапрос ссылается на строку основного запроса. Выполняется для каждой строки основного запроса.

-- Для каждого заказа найти, какой процент это от всех заказов пользователя
SELECT 
    o.order_id,
    o.user_id,
    o.amount,
    -- Коррелированный подзапрос (зависит от текущей строки)
    o.amount * 100.0 / (
        SELECT SUM(amount) FROM orders
        WHERE user_id = o.user_id  -- <- Ссылка на внешний запрос
    ) as percent_of_user_total
FROM orders o;

Производительность: ⚠️ Медленный метод! Выполняется для каждой строки.

Правильный подход через Window Function:

SELECT 
    order_id,
    user_id,
    amount,
    amount * 100.0 / SUM(amount) OVER (PARTITION BY user_id) as percent
FROM orders;

2. Некоррелированные подзапросы (Non-correlated Subquery)

Описание: Подзапрос независим от основного запроса. Выполняется один раз.

-- Найти заказы больше средней
SELECT * FROM orders
WHERE amount > (
    SELECT AVG(amount) FROM orders  -- Выполняется один раз
);

Производительность: ✅ Быстрый метод!

Специальные операторы для подзапросов

IN / NOT IN

-- Пользователи, которые совершали покупки
SELECT * FROM users
WHERE user_id IN (
    SELECT DISTINCT user_id FROM orders
);

-- Пользователи, которые никогда не покупали
SELECT * FROM users
WHERE user_id NOT IN (
    SELECT user_id FROM orders
);

-- Осторожно с NULL!
SELECT * FROM users
WHERE user_id NOT IN (
    SELECT user_id FROM orders
    WHERE user_id IS NOT NULL  -- Нужно исключить NULL!
);

EXISTS / NOT EXISTS

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

-- Более эффективный способ проверки наличия заказов
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.user_id = u.user_id
);

-- Пользователи без заказов
SELECT * FROM users u
WHERE NOT EXISTS (
    SELECT 1 FROM orders o
    WHERE o.user_id = u.user_id
);

EXISTS vs IN:

-- EXISTS (обычно быстрее)
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders WHERE user_id = u.user_id
);

-- IN (может быть медленнее на больших наборах)
SELECT * FROM users
WHERE user_id IN (SELECT user_id FROM orders);

ANY / ALL

-- Заказы больше ЛЮБОго заказа пользователя 5
SELECT * FROM orders
WHERE amount > ANY (
    SELECT amount FROM orders
    WHERE user_id = 5
);

-- Заказы больше ВСЕх заказов пользователя 5 (эквивалент MAX)
SELECT * FROM orders
WHERE amount > ALL (
    SELECT amount FROM orders
    WHERE user_id = 5
);

Common Table Expressions (CTE) — альтернатива подзапросам

Описание: Именованный подзапрос для повышения читаемости.

-- Вместо вложенных подзапросов
WITH user_stats AS (
    SELECT 
        user_id,
        COUNT(*) as order_count,
        SUM(amount) as total_spent,
        AVG(amount) as avg_order
    FROM orders
    GROUP BY user_id
),
active_users AS (
    SELECT * FROM user_stats
    WHERE order_count >= 5
)
SELECT 
    u.user_id,
    u.name,
    au.order_count,
    au.total_spent
FROM users u
JOIN active_users au ON u.user_id = au.user_id;

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

  • ✅ Легче читать
  • ✅ Можно использовать несколько раз
  • ✅ Поддерживает рекурсию

Рекурсивные подзапросы (Recursive CTE)

-- Иерархия организации
WITH RECURSIVE org_hierarchy AS (
    -- Базовый случай: сотрудники без начальника
    SELECT 
        employee_id,
        name,
        manager_id,
        1 as level
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- Рекурсивный случай: подчинённые текущего уровня
    SELECT 
        e.employee_id,
        e.name,
        e.manager_id,
        oh.level + 1
    FROM employees e
    JOIN org_hierarchy oh ON e.manager_id = oh.employee_id
    WHERE oh.level < 5  -- Предотвращение бесконечного цикла
)
SELECT * FROM org_hierarchy
ORDER BY level, employee_id;

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

Пример 1: Когортный анализ

WITH cohorts AS (
    SELECT 
        user_id,
        DATE_TRUNC('month', registration_date) as cohort_month,
        EXTRACT(MONTH FROM first_order_date) - EXTRACT(MONTH FROM registration_date) as months_to_purchase
    FROM users
    WHERE registration_date > '2023-01-01'
)
SELECT 
    cohort_month,
    COUNT(*) as users,
    COUNT(CASE WHEN months_to_purchase <= 0 THEN 1 END) as purchased_month_0,
    COUNT(CASE WHEN months_to_purchase = 1 THEN 1 END) as purchased_month_1,
    COUNT(CASE WHEN months_to_purchase >= 2 THEN 1 END) as purchased_month_2_plus
FROM cohorts
GROUP BY cohort_month;

Пример 2: Ранжирование с пропусками

-- Найти топ-3 товара по выручке в каждой категории
WITH ranked_products AS (
    SELECT 
        category,
        product_id,
        product_name,
        SUM(amount) as revenue,
        RANK() OVER (PARTITION BY category ORDER BY SUM(amount) DESC) as rank
    FROM orders
    GROUP BY category, product_id, product_name
)
SELECT * FROM ranked_products
WHERE rank <= 3;

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

СлучайПодзапросJOIN
Проверка существованияEXISTS✅ Предпочтительнее
Один результатСкалярный✅ Объединить данные
Список значенийINЗависит от оптимизатора
Фильтрация по агрегации✅ УдобноСложнее
Большие наборыIN может быть медленнее✅ Обычно быстрее
ИерархияРекурсивная CTE✅ Очень удобно

Оптимизация подзапросов

-- ❌ Плохо: коррелированный подзапрос
SELECT 
    o.order_id,
    (SELECT u.name FROM users u WHERE u.user_id = o.user_id) as user_name
FROM orders o;

-- ✅ Хорошо: JOIN
SELECT 
    o.order_id,
    u.name
FROM orders o
JOIN users u ON o.user_id = u.user_id;

-- ❌ Плохо: подзапрос в SELECT
SELECT 
    product_id,
    (SELECT AVG(amount) FROM orders WHERE product_id = p.product_id) as avg_price
FROM products p;

-- ✅ Хорошо: Window function
SELECT 
    product_id,
    AVG(amount) OVER (PARTITION BY product_id) as avg_price
FROM orders;

Ключевые выводы

✅ Используй EXISTS вместо IN для проверки наличия

✅ Предпочитай CTE для читаемости сложных запросов

✅ Используй JOIN вместо коррелированных подзапросов

✅ Window Functions часто быстрее, чем подзапросы в SELECT

✅ Всегда проверяй EXPLAIN PLAN для оптимизации

Что такое подзапросы в SQL и какие виды подзапросов бывают? | PrepBro