В чем разница между коррелированным и некоррелированным подзапросом?

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

Подзапрос (Subquery) — это SELECT запрос, вложенный внутри другого SQL запроса. Они бывают двух типов в зависимости от того, ссылаются ли они на таблицы из внешнего запроса.

Некоррелированный подзапрос (Non-Correlated)

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

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

• Выполняется один раз в начале
• Результат одинаковый для всех строк внешнего запроса
• Более быстрый (обычно)
• Можно выполнить отдельно

Пример:

-- Найти всех клиентов, потративших больше средней суммы
SELECT customer_id, name, total_spent
FROM customers
WHERE total_spent > (
    SELECT AVG(total_spent)  -- подзапрос не ссылается на customers
    FROM customers
);

-- Подзапрос выполняется один раз, результат = например 500
-- Затем внешний запрос ищет всех с total_spent > 500

Ещё примеры:

-- Пример 1: Найти заказы дороже максимума из другого месяца
SELECT order_id, amount
FROM orders
WHERE amount > (
    SELECT MAX(amount)
    FROM orders
    WHERE EXTRACT(MONTH FROM created_at) = 3
);

-- Пример 2: Города с количеством пользователей > среднего
SELECT city, COUNT(*) as user_count
FROM users
GROUP BY city
HAVING COUNT(*) > (
    SELECT AVG(city_count)
    FROM (
        SELECT COUNT(*) as city_count
        FROM users
        GROUP BY city
    ) subquery
);

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

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

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

• Выполняется n раз (где n = количество строк внешнего запроса)
• Результат отличается для каждой строки
• Медленнее (может быть очень медленным на больших таблицах)
• Нельзя выполнить отдельно

Пример:

-- Найти заказы, которые больше среднего для каждого клиента
SELECT order_id, customer_id, amount
FROM orders o1
WHERE amount > (
    SELECT AVG(amount)  -- подзапрос ссылается на o1.customer_id
    FROM orders o2
    WHERE o2.customer_id = o1.customer_id  -- КОРРЕЛЯЦИЯ!
);

-- Для каждой строки o1:
-- 1. customer_id = 1 → вычислить AVG для customer_id = 1
-- 2. customer_id = 2 → вычислить AVG для customer_id = 2
-- 3. И так далее...

Более сложный пример:

-- Найти последний заказ каждого клиента
SELECT customer_id, order_id, order_date
FROM orders o1
WHERE order_date = (
    SELECT MAX(order_date)  -- подзапрос коррелирован с o1
    FROM orders o2
    WHERE o2.customer_id = o1.customer_id
);

-- Для customer_id = 1 → ищем MAX(order_date) для customer_id = 1
-- Для customer_id = 2 → ищем MAX(order_date) для customer_id = 2

Сравнение: Производительность

Некоррелированный (Быстро):

-- Подзапрос выполняется один раз
SELECT *
FROM employees
WHERE salary > (
    SELECT AVG(salary) FROM employees  -- 1 раз
);

-- Для таблицы с 1 млн записей:
-- Подзапрос: 1 млн записей
-- Внешний запрос: 1 млн записей
-- Итого: 2 млн операций

Коррелированный (Медленно):

-- Подзапрос выполняется для каждой строки
SELECT *
FROM employees e1
WHERE salary > (
    SELECT AVG(salary)  -- 1 млн раз!
    FROM employees e2
    WHERE e2.department = e1.department
);

-- Для таблицы с 1 млн записей:
-- Подзапрос × 1 млн записей внешнего запроса = 1 млн х 1 млн операций!
-- Итого: 1 триллион операций (очень медленно)

Визуальное объяснение

НЕКОРРЕЛИРОВАННЫЙ:

Внешний запрос:
СТРОКА 1: customer_id = 1, amount = 100
СТРОКА 2: customer_id = 2, amount = 150
СТРОКА 3: customer_id = 1, amount = 200
        ↓
        Один раз вычислить средний расход = 150
        ↓
Фильтр: amount > 150
        ↓
Результат: СТРОКА 3 (200 > 150)


КОРРЕЛИРОВАННЫЙ:

СТРОКА 1: customer_id = 1, amount = 100
        ↓
        Вычислить средний расход для customer_id = 1 = 120
        ↓
        Проверить: 100 > 120? НЕТ
        ↓
СТРОКА 2: customer_id = 2, amount = 150
        ↓
        Вычислить средний расход для customer_id = 2 = 150
        ↓
        Проверить: 150 > 150? НЕТ
        ↓
СТРОКА 3: customer_id = 1, amount = 200
        ↓
        Вычислить средний расход для customer_id = 1 = 120 (ещё раз!)
        ↓
        Проверить: 200 > 120? ДА
        ↓
Результат: СТРОКА 3 (200 > 120)

Когда использовать каждый тип

Некоррелированный — когда:

• Подзапрос вычисляет одно значение для всех строк
• Нет зависимости от внешнего запроса

Коррелированный — когда:

• Нужно вычислить значение для каждой строки отдельно
• Классический case: EXISTS

EXISTS vs IN (коррелированные подзапросы)

-- Найти клиентов, которые совершили хотя бы один заказ
-- Способ 1: EXISTS (коррелированный, обычно быстрее)
SELECT customer_id, name
FROM customers c
WHERE EXISTS (
    SELECT 1  -- не нужен результат, просто проверка существования
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

-- Способ 2: IN (тоже коррелированный, но медленнее на больших данных)
SELECT customer_id, name
FROM customers
WHERE customer_id IN (
    SELECT customer_id FROM orders
);

-- Способ 3: JOIN (обычно быстрее всего)
SELECT DISTINCT c.customer_id, c.name
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id;

Оптимизация: Переделать коррелированный в некоррелированный

Плохо (коррелированный):

SELECT customer_id, name
FROM customers c1
WHERE total_spent > (
    SELECT AVG(total_spent)
    FROM customers c2
    WHERE c2.city = c1.city  -- коррелирован
);

Хорошо (некоррелированный с CTE):

WITH city_averages AS (
    SELECT city, AVG(total_spent) as avg_spent
    FROM customers
    GROUP BY city
)
SELECT c.customer_id, c.name
FROM customers c
JOIN city_averages ca ON c.city = ca.city
WHERE c.total_spent > ca.avg_spent;

Хорошо (с window function):

WITH customer_with_avg AS (
    SELECT
        customer_id,
        name,
        total_spent,
        AVG(total_spent) OVER (PARTITION BY city) as city_avg
    FROM customers
)
SELECT customer_id, name
FROM customer_with_avg
WHERE total_spent > city_avg;

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

Пример 1: Найти работников, зарплата которых выше средней по отделу

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

-- Альтернатива с window function (быстрее)
SELECT employee_id, name, salary, department
FROM (
    SELECT
        employee_id, name, salary, department,
        AVG(salary) OVER (PARTITION BY department) as dept_avg
    FROM employees
) t
WHERE salary > dept_avg
ORDER BY department, salary DESC;

Пример 2: Найти продукты с ценой выше максимума в категории

-- Коррелированный
SELECT product_id, name, price, category
FROM products p1
WHERE price = (
    SELECT MAX(price)
    FROM products p2
    WHERE p2.category = p1.category
);

-- Более эффективно с CTE
WITH category_max_prices AS (
    SELECT category, MAX(price) as max_price
    FROM products
    GROUP BY category
)
SELECT p.product_id, p.name, p.price, p.category
FROM products p
JOIN category_max_prices cm ON p.category = cm.category AND p.price = cm.max_price;

Правило оптимизации

Коррелированный подзапрос работает так:

Для каждой строки внешнего запроса:
    Выполнить подзапрос
    Получить результат
    Применить условие
    Вывести результат (если условие выполнено)

Это может быть ОЧЕНЬ медленным на больших таблицах!

Best Practices

1. Избегай коррелированных подзапросов на таблицах с миллионами строк
2. Используй CTE с JOIN вместо коррелированных подзапросов
3. Используй window functions когда возможно (быстрее)
4. Используй EXISTS вместо IN для коррелированных проверок
5. ВСЕГДА проверяй EXPLAIN перед запуском на большой таблице

Заключение

• Некоррелированный подзапрос выполняется один раз → быстро
• Коррелированный подзапрос выполняется n раз → медленно
• Переделай коррелированные в CTE + JOIN или window functions для лучшей производительности