Урок 5.8: Практические сценарии и методы использования JOIN

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

1. Соединение нескольких таблиц (3 и более)

В сложных базах данных нужная вам информация часто распределена по трем или более таблицам, соединенным через промежуточные таблицы (junction tables).

Сценарий: Мы хотим увидеть список актеров и названия фильмов, в которых они снимались. Для этого потребуются три таблицы: actor, film_actor (связующая таблица) и film.

SELECT
    a.first_name,
    a.last_name,
    f.title
FROM
    actor AS a
INNER JOIN
    film_actor AS fa ON a.actor_id = fa.actor_id
INNER JOIN
    film AS f ON fa.film_id = f.film_id
ORDER BY
    a.last_name
LIMIT 10;

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

• Каждый JOIN создает новую виртуальную таблицу, которую может использовать следующий JOIN.
• Порядок соединений обычно следует пути связей в ER-диаграмме (схеме базы данных).

2. Использование агрегатных функций с JOIN

Один из самых мощных способов использования соединений - это расчет статистики по связанным таблицам. Вы можете использовать функции COUNT, SUM и AVG после соединения.

Сценарий: Рассчитать общую сумму, потраченную каждым клиентом.

SELECT
    c.first_name,
    c.last_name,
    SUM(p.amount) AS total_spent
FROM
    customer AS c
INNER JOIN
    payment AS p ON c.customer_id = p.customer_id
GROUP BY
    c.customer_id, c.first_name, c.last_name
ORDER BY
    total_spent DESC;

Примечание: При использовании GROUP BY с соединениями всегда включайте первичный ключ (customer_id), чтобы гарантировать уникальность результатов, если у двух клиентов одинаковые имена.

3. Поиск недостающих данных ("Anti-Join")

Мы можем использовать LEFT JOIN в сочетании с предложением WHERE, чтобы найти записи, для которых нет соответствующей записи в другой таблице.

Сценарий: Найти все фильмы, которых на данный момент НЕТ в нашем инвентаре (то есть запись о фильме есть, но физических копий нет).

SELECT
    f.title
FROM
    film AS f
LEFT JOIN
    inventory AS i ON f.film_id = i.film_id
WHERE
    i.inventory_id IS NULL;

4. Ловушка фильтрации: WHERE против ON

Распространенная ошибка - помещать условие фильтрации в предложение WHERE при использовании LEFT JOIN, что случайно превращает его обратно в INNER JOIN.

Неправильно:

-- Это удалит клиентов без платежей, так как p.payment_date проверяется ПОСЛЕ соединения
SELECT c.last_name, p.amount
FROM customer c
LEFT JOIN payment p ON c.customer_id = p.customer_id
WHERE p.payment_date > '2005-08-01';

Правильно (сохраняя всех клиентов):

-- Это сохранит всех клиентов, но присоединит только те данные о платежах, которые соответствуют дате
SELECT c.last_name, p.amount
FROM customer c
LEFT JOIN payment p ON c.customer_id = p.customer_id 
    AND p.payment_date > '2005-08-01';

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

• Цепочки соединений: Вы можете соединять столько таблиц, сколько необходимо, добавляя дополнительные операторы JOIN.
• Отчетность: Сочетание JOIN с GROUP BY позволяет создавать сложные отчеты по различным бизнес-сущностям.
• Аудит данных: Используйте LEFT JOIN ... WHERE ... IS NULL для поиска пробелов в данных.
• Логическая точность: Будьте внимательны к тому, где вы размещаете фильтры (в ON или в WHERE) при работе с внешними соединениями.