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

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

Что такое коррелированный подзапрос?

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

Как это работает:

1. База данных берет строку из внешнего запроса.
2. Выполняется внутренний запрос с использованием значений из этой конкретной строки.
3. Результат внутреннего запроса используется для выполнения условия WHERE (или заполнения SELECT).
4. База переходит к следующей строке и повторяет процесс.

Заметка о производительности: Поскольку коррелированный подзапрос потенциально выполняется один раз для каждой строки внешнего запроса, он может работать медленнее, чем JOIN или обычный подзапрос, на очень больших объемах данных.

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

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

Сценарий: Найти все фильмы, стоимость замены которых выше средней стоимости замены фильмов в той же категории рейтинга (например, G, PG, R).

SELECT
    title,
    rating,
    replacement_cost
FROM
    film AS f1
WHERE
    replacement_cost > (
        SELECT AVG(replacement_cost)
        FROM film AS f2
        WHERE f1.rating = f2.rating
    );

• Корреляция: f1.rating = f2.rating связывает внутренний запрос с текущей строкой внешнего.
• Логика: Для каждого фильма база данных рассчитывает среднюю стоимость именно для его рейтинга и проверяет, стоит ли данный фильм дороже.

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

Вы можете использовать коррелированные подзапросы для получения описательных данных или агрегатов для каждой строки без использования GROUP BY.

Сценарий: Показать список категорий и название самого длинного фильма в каждой из них.

SELECT
    c.name AS category_name,
    (
        SELECT f.title
        FROM film f
        JOIN film_category fc ON f.film_id = fc.film_id
        WHERE fc.category_id = c.category_id
        ORDER BY f.length DESC
        LIMIT 1) AS longest_film_title
FROM
    category AS c;

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

Мы рассматривали оператор EXISTS в предыдущем уроке. EXISTS почти всегда используется именно с коррелированным подзапросом.

Сценарий: Найти клиентов, которые арендовали хотя бы один фильм в конкретном магазине (Store 1).

SELECT
    first_name,
    last_name
FROM
    customer AS c
WHERE
    EXISTS (
        SELECT 1
        FROM rental AS r
        INNER JOIN inventory AS i ON r.inventory_id = i.inventory_id
        WHERE r.customer_id = c.customer_id
        AND i.store_id = 1
    );

Ключевые выводы урока

• Коррелированный подзапрос зависит от внешнего запроса для получения своих значений.
• Он выполняется построчно (один раз для каждой подходящей строки).
• Псевдонимы (Aliases) обязательны, чтобы различать экземпляры внешней и внутренней таблиц.
• Они незаменимы для сравнений внутри групп (сравнение строки с её собственной группой).
• Будьте осторожны с производительностью при использовании на миллионах записей.