JOIN в MySQL подробное руководство с примерами

Работа с базами данных часто связана с объединением данных из разных таблиц, в предыдущем выпуске мы рассказывали про типы связей в mysql. Теперь разберем на примерах как доставать все данные из разных таблиц обходясь одним запросом, а именно, посредством mysql join. Он позволяет гибко соединять строки по заданным условиям и получать результат, который невозможно получить из одной таблицы.

Что такое MySQL JOIN

MySQL JOIN — это механизм объединения данных двух или более таблиц по определённому условию. Чаще всего это условие — связь через внешний ключ. Для наглядности рассмотрим простой пример:

SELECT *
FROM table1
JOIN table2 ON table1.id = table2.table1_id;

Join может работать с несколькими таблицами одновременно, фильтрами, агрегатами и условиями.

• Условия в ON
• Фильтры в WHERE
• Агрегаты и фильтры в HAVING
• Таблицы table_1, table_2, table_3

Основные типы JOIN в MySQL

Основные типы join являются выдачей разного результата, в зависимости от вашей задачи. Манипуляция данными и их выборка может быть совершенно разной, например, если нужно понять какой товар пользователь заказывал и когда, отфильтровать этот товар по тегам, категориям, дате и т.д. Про работу с данными в массиве мы уже рассказывали в статье про многомерные массивы в php, если выбрать просто все данные из базы и потом работать с ними. Но, именно джойны в mysql дают возможность сделать один запрос к БД и получить нужный результат без каких либо действий на стороне php или другого ЯП.

INNER JOIN в MySQL

Возвращает только те строки, для которых есть совпадения в обеих таблицах.

SELECT users.name, orders.amount
FROM users
INNER JOIN orders ON users.id = orders.user_id;

В результате получим список пользователей у которых есть заказы.

LEFT JOIN (LEFT OUTER JOIN) в MySQL

Возвращает все строки из левой таблицы, даже если в правой нет совпадений.

SELECT users.name, orders.amount
FROM users
LEFT JOIN orders ON users.id = orders.user_id;

Если заказов нет то orders.amount будет равен NULL.

RIGHT JOIN (RIGHT OUTER JOIN) в MySQL

Покажет все заказы даже если пользователь не найден.

SELECT users.name, orders.amount
FROM users
RIGHT JOIN orders ON users.id = orders.user_id;

Работает как и предыдущий пример, только теперь данные пользователя будут NULL.

FULL JOIN (FULL OUTER JOIN) в MySQL

Именно MySQL не поддерживает FULL JOIN напрямую, но его можно сымитировать.

SELECT *
FROM table1
LEFT JOIN table2 ON ...
UNION
SELECT *
FROM table1
RIGHT JOIN table2 ON ...;

JOIN с WHERE в MySQL

Здесь важно понять, что WHERE фильтрует после JOIN, поэтому строки без совпадений (которые равны NULL) будут удалены.

SELECT users.name, orders.amount
FROM users
LEFT JOIN orders ON users.id = orders.user_id
WHERE orders.amount > 100;

JOIN с USING в MySQL

Данный джоин используется, если колонки имеют одинаковое имя.

SELECT *
FROM orders
JOIN order_status USING(status_id);

Практически тоже самое если написать это:

SELECT *
FROM orders
JOIN order_status ON orders.status_id = order_status.status_id;

JOIN с HAVING в MySQL

Джоин хавинг применяется после агрегирования.

SELECT users.id, COUNT(orders.id) AS total
FROM users
LEFT JOIN orders ON users.id = orders.user_id
GROUP BY users.id
HAVING total > 5;

UPDATE JOIN в MySQL

Обновление через join на основе другой таблицы.

UPDATE products p
JOIN categories c ON p.category_id = c.id
SET p.status = 'archived'
WHERE c.is_deleted = 1;

DELETE JOIN в MySQL

Удаление через join по условию объединения.

DELETE p
FROM products p
JOIN categories c ON p.category_id = c.id
WHERE c.is_deleted = 1;

Несколько JOIN одновременно в MySQL

Для более масштабного запроса можно использовать несколько таблиц, пусть это будет 2-3 или 10-20, ограничений нет. Главное понять как это работает и применять постоянно на практике, таким образом будет четкая оптимизация sql запросов в вашем проекте.

SELECT a.title, u.name, c.title
FROM articles a
JOIN users u ON a.user_id = u.id
JOIN categories c ON a.category_id = c.id;

Шпаргалка по JOIN MySQL

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

Тип JOIN	Краткое описание	Поведение с NULL	Когда применять	Пример SQL
INNER JOIN	Возвращает только строки с совпадениями в обеих таблицах	Строки без совпадения отбрасываются	Когда нужны только связанные записи	FROM a INNER JOIN b ON a.id = b.a_id
LEFT JOIN	Все строки из левой таблицы + совпадения справа	Поля правой таблицы = NULL, если совпадения нет	Показать все записи левой таблицы, даже без связи	FROM a LEFT JOIN b ON a.id = b.a_id
RIGHT JOIN	Все строки из правой таблицы + совпадения слева	Поля левой таблицы = NULL при отсутствии совпадений	Аналог LEFT, когда важна правая таблица	FROM a RIGHT JOIN b ON a.id = b.a_id
FULL OUTER JOIN (эмуляция)	Все строки из обеих таблиц (MySQL: UNION LEFT+RIGHT)	NULL в той части, где нет совпадения	Нужен полный набор данных из обеих таблиц	LEFT JOIN ... UNION RIGHT JOIN ...
CROSS JOIN	Декартово произведение (все комбинации строк)	Нет NULL; результат = rowsA * rowsB	Редко; для генерации комбинаций	FROM a CROSS JOIN b
SELF JOIN	Соединение таблицы самой с собой (рекурсивные связи)	Как в JOIN’ах (обычно LEFT для дерева)	Иерархии: сотрудники/менеджеры, категории/подкатегории	FROM e LEFT JOIN e m ON e.manager_id = m.id