Соединения (JOIN) в MySQL: руководство и примеры

Одна из главных выгод реляционных баз данных, таких как MySQL — способность хранить связанные данные в разных таблицах и объединять их в запросах. Правильно составленные JOIN-ы позволяют получить ровно те данные, которые нужны, не дублируя и не копируя информацию вручную.

Инициализация примерной базы данных

Этот шаг не обязателен, но если вы хотите повторить примеры локально, инициализируйте тестовую базу командами в терминале:

git clone https://github.com/mdizak/sample-select-db.git  
cd sample-select-db  
sudo mysql < store.sql  
sudo mysql sampledb  
mysql> SELECT COUNT(*) FROM customers;

Вы должны увидеть результат, указывающий, что в таблице customers 2000 строк.

INNER JOIN — соединение по умолчанию

INNER JOIN — самый распространённый тип соединения. Он возвращает только те записи, для которых есть совпадающие строки в обеих таблицах. Все остальные строки отбрасываются.

Например, чтобы получить имена клиентов и сумму заказа только для заказов больше $1000, можно использовать запрос:

  
SELECT  
c.id, c.first_name, c.last_name, o.amount, o.created_at  
FROM  
customers c, orders o  
WHERE  
o.customer_id = c.id AND o.amount >= 1000;  

Несколько пояснений к запросу выше:

• Мы выбираем пять столбцов: три из таблицы customers и два из orders.
• В части FROM две таблицы названы с псевдонимами «c» и «o». Это просто сокращения, которые упрощают чтение запросов.
• Выражение o.customer_id = c.id — это условие соединения, обеспечивающее соответствие заказов и клиентов.

Технически корректнее и читабельнее записать то же самое с явным INNER JOIN:

  
SELECT  
c.id, c.first_name, c.last_name, o.amount, o.created_at  
FROM  
customers c INNER JOIN orders o  
ON  
customer_id = c.id  
WHERE  
o.amount >= 1000;  

Визуально такой формат сразу показывает, какие таблицы и по каким столбцам связаны.

LEFT JOIN

LEFT JOIN возвращает все строки из левой таблицы и совпадающие строки из правой. Если совпадение не найдено — значения из правой таблицы будут NULL.

Пример: подсчитать общую сумму продаж по каждому продукту, включая те продукты, которые встречаются в заказах:

  
SELECT  
p.name, sum(item.amount) AS tamount  
FROM  
orders_items item LEFT JOIN products p  
ON  
item.product_id = p.id  
GROUP BY item.product_id ORDER BY tamount DESC  

Этот запрос пройдётся по таблице orders_items и для каждого product_id попытается найти запись в products. Если продукт в заказах встречается — он появится с суммой продаж.

RIGHT JOIN

RIGHT JOIN — зеркальная версия LEFT JOIN: он возвращает все строки из правой таблицы и совпадающие из левой. В примере выше LEFT JOIN вернул 19 строк, тогда как в базе 22 продукта — это потому, что некоторые продукты никогда не появлялись в orders_items.

Если нужно получить список всех продуктов с суммами продаж, включая те, которые не заказывали, используйте RIGHT JOIN:

  
SELECT  
p.name, sum(item.amount) AS tamount  
FROM  
orders_items item RIGHT JOIN products p  
ON  
item.product_id = p.id  
GROUP BY p.id ORDER BY tamount DESC  

В результате вернётся 22 продукта, у трёх из них сумма будет null — это нормальное поведение для неприсутствующих значений.

Соединение нескольких таблиц

Иногда нужно объединить три и более таблиц. Пример: найти всех клиентов, которые купили микроволновку (product_id = 1) с указанием имени и даты заказа. Это можно сделать двумя JOIN-ами:

  
SELECT  
c.first_name, c.last_name, o.amount, o.created_at  
FROM  
customers c INNER JOIN orders o  
ON  
c.id = o.customer_id INNER JOIN orders_items item  
ON  
item.order_id = o.id  
WHERE  
item.product_id = 1 ORDER BY o.created_at;  

Запрос сначала сопоставляет клиентов и их заказы, затем фильтрует эти заказы по товарам, которые содержат микроволновку.

Не используйте подзапросы с IN

Подзапросы с IN часто работают медленно и потребляют много ресурсов. Вместо них лучше использовать JOIN или EXISTS.

Плохой пример (избегайте):

SELECT first_name,last_name FROM customers WHERE id IN (SELECT customer_id FROM orders WHERE status = 'approved' AND amount < 100);

Эквивалент через JOIN (рекомендация):

SELECT c.first_name, c.last_name FROM customers c LEFT JOIN orders o ON o.customer_id = c.id WHERE o.status = 'approved' AND o.amount < 100;

LEFT JOIN здесь гарантирует, что мы начинаем с таблицы customers и при желании можем увидеть клиентов без заказов (если условия WHERE скорректировать). Если нужно только тех, у кого есть заказ — используйте INNER JOIN.

Когда JOIN дают неверные или неожиданные результаты

• Пересечения по неправильным колонкам. Убедитесь, что в ON вы используете корректные столбцы (частая ошибка — забыть префиксы таблиц).
• Картезианское произведение. Если забыть условие ON или WHERE — получите умножение строк (очень большой результат).
• Дубликаты. JOIN может продублировать строки, если в связанной таблице есть несколько совпадающих записей. Для агрегатов используйте GROUP BY или DISTINCT при необходимости.
• NULL в столбцах. LEFT/RIGHT JOIN возвращают NULL, и агрегации без COALESCE могут вести себя неожиданно.
• Отсутствие индексов. JOIN по неиндексированным колонкам приводит к полным сканированиям таблиц и значительному падению производительности.

Альтернативы и приёмы

• EXISTS вместо IN: часто быстрее и семантически точнее при проверке существования строк.
• Подзапросы в FROM (derived tables) — удобны, когда нужно предварительно агрегировать данные.
• Оконные функции (WINDOW) для аналитических задач вместо сложных JOIN-ов.
• Материализованные представления (materialized views) или регулярная денормализация для очень горячих отчётов.

Пример использования EXISTS:

SELECT c.first_name, c.last_name FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.status = 'approved');

Оптимизация и лучшие практики

• Индексируйте колонки, по которым производится JOIN (особенно внешние ключи).
• Используйте EXPLAIN для анализа плана выполнения и поиска узких мест.
• Избегайте SELECT * — перечисляйте только нужные столбцы.
• Фильтры в WHERE ставьте до JOIN, если это логически корректно, но помните о семантике LEFT/RIGHT JOIN.
• Ограничивайте размер результирующего набора с помощью LIMIT и пагинации при отладке.
• Для больших таблиц проверяйте cardinality и выбирайте оптимальный порядок объединения (MySQL оптимизатор учитывает это, но подсказки могут помочь).

Шпаргалка: быстрые правила

Короткие советы:

• Если хотите все продукты и их продажи — начните с products и LEFT JOIN к orders_items.
• Для отчётов по транзакциям обычно удобен INNER JOIN между orders и orders_items.
• Если видите много NULL-ов — подумайте, откуда они появляются и нужны ли они.

Контрольные списки по ролям

Для разработчика:

• Указать явные псевдонимы таблиц.
• Тестировать запрос на небольшом наборе данных.
• Использовать EXPLAIN при подозрении на медленный запрос.

Для аналитика:

• Проверять, что агрегаты учитывают NULL (COALESCE).
• Использовать GROUP BY после JOIN только при необходимости.

Для DBA:

• Следить за индексами на полях JOIN.
• Мониторить медленные запросы и предлагать денормализацию, если нужно.

Критерии приёмки

• Запрос возвращает ожидаемые строки для контрольных тестовых данных.
• Время выполнения укладывается в приемлемый SLA для задачи (например, <2 с для интерактивного отчёта).
• План выполнения не содержит полных сканирований больших таблиц без индексов.

Краткий глоссарий

• JOIN — операция объединения таблиц по условию.
• INNER JOIN — возвращает только совпавшие строки.
• LEFT JOIN — сохраняет все строки левой таблицы.
• RIGHT JOIN — сохраняет все строки правой таблицы.
• Псевдоним (alias) — короткое имя таблицы, упрощающее запрос.

Заключение

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

Важно: всегда тестируйте запросы на реальных объёмах данных, смотрите EXPLAIN и следите за индексами. С хорошо продуманными JOIN-ами вы будете быстрее получать корректные отчёты и уменьшите нагрузку на базу.

Замечания:

• Если в результате видите неожиданные NULL-значения — проверьте направление JOIN и условие ON.
• Для очень больших отчётов рассмотрите денормализацию или предварительную агрегацию.