JOIN в SQL: виды соединений таблиц и примеры запросов в MySQL

Что делает JOIN и как он работает

Данные в реляционной базе раскладывают по разным таблицам: клиенты отдельно, заказы отдельно, а в заказе хранится только номер клиента customer_id. Оператор JOIN в SQL собирает их обратно в одну выборку: для каждой строки одной таблицы ищет подходящие строки другой по условию после ON и склеивает их в одну строку результата.

Общий синтаксис JOIN SQL выглядит так:

SELECT столбцы
FROM таблица_1
[INNER | LEFT | RIGHT | CROSS] JOIN таблица_2
    ON условие_соединения
WHERE фильтр
ORDER BY сортировка;

Слово JOIN без уточнения означает INNER JOIN. Слово OUTER необязательное: LEFT JOIN и LEFT OUTER JOIN - одно и то же.

Таблицы для примеров

Все запросы ниже выполняются на двух маленьких таблицах. Скопируйте скрипт в консоль MySQL или во вкладку "SQL" в phpMyAdmin, чтобы повторять примеры.

CREATE TABLE customers (
    id   INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    city VARCHAR(50) NOT NULL
);

CREATE TABLE orders (
    id          INT PRIMARY KEY,
    customer_id INT NOT NULL,
    total       DECIMAL(10, 2) NOT NULL,
    INDEX idx_customer (customer_id)
);

INSERT INTO customers VALUES
    (1, 'Анна', 'Москва'), (2, 'Борис', 'Казань'),
    (3, 'Вера', 'Москва'), (4, 'Глеб', 'Пермь');

-- Заказ 104 ссылается на клиента 7, которого нет
INSERT INTO orders VALUES
    (101, 1, 2500.00), (102, 1, 900.00),
    (103, 3, 4100.00), (104, 7, 1500.00);

Обратите внимание: у Бориса и Глеба заказов нет, а у заказа 104 нет клиента. Именно на таких "непарных" строках виды JOIN и отличаются друг от друга.

INNER JOIN: только совпадающие строки

INNER JOIN возвращает строки, для которых условие ON выполнилось в обеих таблицах. Клиенты без заказов и заказы без клиента в результат не попадают.

SELECT c.name, o.id AS order_id, o.total
FROM customers AS c
INNER JOIN orders AS o ON o.customer_id = c.id
ORDER BY o.id;

+------+----------+---------+
| name | order_id | total   |
+------+----------+---------+
| Анна |      101 | 2500.00 |
| Анна |      102 |  900.00 |
| Вера |      103 | 4100.00 |
+------+----------+---------+

Анна встречается дважды, потому что у неё два заказа: JOIN выдаёт по строке на каждую найденную пару. Псевдонимы c и o сокращают запись; когда столбец с одинаковым именем есть в обеих таблицах (здесь id), указывать таблицу обязательно, иначе MySQL ответит ошибкой "Column 'id' in field list is ambiguous".

LEFT JOIN: все строки левой таблицы

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

SELECT c.name, o.id AS order_id, o.total
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
ORDER BY c.id, o.id;

+-------+----------+---------+
| name  | order_id | total   |
+-------+----------+---------+
| Анна  |      101 | 2500.00 |
| Анна  |      102 |  900.00 |
| Борис |     NULL |    NULL |
| Вера  |      103 | 4100.00 |
| Глеб  |     NULL |    NULL |
+-------+----------+---------+

Как найти строки без пары

Самое полезное применение LEFT JOIN - найти "сирот": клиентов без заказов, товары без продаж, статьи без категорий. Для этого оставляют только строки, где правая часть пустая:

-- Клиенты, которые ещё ничего не заказали
SELECT c.id, c.name
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
WHERE o.id IS NULL;

Результат - Борис и Глеб. Проверяйте на IS NULL столбец, который в правой таблице никогда не бывает пустым, лучше всего первичный ключ.

Разница между LEFT JOIN и INNER JOIN

Условие в ON или в WHERE: частая ошибка с LEFT JOIN

Для INNER JOIN неважно, куда записать дополнительное условие - в ON или в WHERE. Для LEFT JOIN разница принципиальная. Сравните два запроса "все клиенты и их крупные заказы":

-- 1. Условие в WHERE: LEFT JOIN превратился в INNER
SELECT c.name, o.total
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
WHERE o.total > 2000;
-- Анна 2500, Вера 4100 - Борис и Глеб пропали

-- 2. Условие в ON: клиенты сохраняются
SELECT c.name, o.total
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id AND o.total > 2000;
-- Анна 2500, Борис NULL, Вера 4100, Глеб NULL

WHERE выполняется после соединения, и сравнение NULL > 2000 не даёт истины, поэтому строки без заказов отсеиваются. Правило простое: фильтр по правой таблице LEFT JOIN пишите в ON, фильтр по левой - в WHERE.

RIGHT JOIN: все строки правой таблицы

RIGHT JOIN - зеркальная копия LEFT JOIN: сохраняются все строки таблицы, указанной после JOIN.

SELECT c.name, o.id AS order_id, o.total
FROM customers AS c
RIGHT JOIN orders AS o ON o.customer_id = c.id;

+------+----------+---------+
| name | order_id | total   |
+------+----------+---------+
| Анна |      101 | 2500.00 |
| Анна |      102 |  900.00 |
| Вера |      103 | 4100.00 |
| NULL |      104 | 1500.00 |
+------+----------+---------+

Заказ 104 попал в выборку без имени - так находят записи с битыми ссылками. На практике RIGHT JOIN используют редко: тот же запрос понятнее записать через LEFT JOIN, поменяв таблицы местами (FROM orders LEFT JOIN customers).

FULL OUTER JOIN и как сделать его в MySQL

FULL JOIN возвращает все строки обеих таблиц: пары соединяются, а непарные строки с любой стороны дополняются NULL. В PostgreSQL, SQL Server и Oracle он пишется напрямую - FULL OUTER JOIN. В MySQL (включая 8.4 и 9.x) такого оператора нет, поэтому FULL JOIN собирают из LEFT и RIGHT через UNION:

-- FULL OUTER JOIN для MySQL
SELECT c.name, o.id AS order_id, o.total
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
UNION
SELECT c.name, o.id, o.total
FROM customers AS c
RIGHT JOIN orders AS o ON o.customer_id = c.id;

Результат - шесть строк: три пары, Борис и Глеб без заказов и заказ 104 без клиента. UNION убирает дубли, которые дают обе половины для совпавших строк. Если в данных бывают полностью одинаковые строки, которые нужно сохранить, используйте UNION ALL, а во второй половине оставьте только непарные строки условием WHERE c.id IS NULL.

CROSS JOIN: все комбинации строк

CROSS JOIN не требует условия и соединяет каждую строку первой таблицы с каждой строкой второй - получается декартово произведение. У нас 4 клиента и 4 заказа, значит, 16 строк.

-- Все сочетания размеров и цветов для карточек товара
SELECT s.size, c.color
FROM sizes AS s
CROSS JOIN colors AS c
ORDER BY s.size, c.color;

Полезен CROSS JOIN для генерации комбинаций: размеры и цвета, даты и сотрудники для графика смен, пустая сетка отчёта, которую потом заполняют через LEFT JOIN. Осторожно с большими таблицами: 10 000 × 10 000 строк - это уже 100 миллионов строк результата. В MySQL запись FROM a, b без условия и JOIN без ON работают как CROSS JOIN.

Self join: соединение таблицы с самой собой

Self join нужен, когда строки ссылаются на другие строки той же таблицы: сотрудник и его руководитель, категория и родительская категория, комментарий и ответ на него. Таблица указывается дважды под разными псевдонимами:

-- employees: id, name, manager_id (id руководителя или NULL)
SELECT e.name AS employee,
       m.name AS manager
FROM employees AS e
LEFT JOIN employees AS m ON m.id = e.manager_id
ORDER BY e.name;

LEFT JOIN здесь нужен, чтобы в списке остался директор, у которого руководителя нет. Для деревьев произвольной глубины одного self join мало - их обходят рекурсивным CTE WITH RECURSIVE (MySQL 8+). Как хранить и выводить дерево категорий на PHP, показано в статье про jsTree, PHP и MySQL.

JOIN USING и NATURAL JOIN

Если столбцы для соединения называются одинаково в обеих таблицах, условие можно сократить словом USING:

-- В обеих таблицах есть столбец customer_id
SELECT customer_id, p.phone, o.total
FROM profiles AS p
JOIN orders AS o USING (customer_id);

Плюс USING - общий столбец выводится один раз и без указания таблицы. NATURAL JOIN идёт дальше и сам соединяет по всем одноимённым столбцам. В рабочем коде его лучше избегать: добавите в обе таблицы столбец created_at - и запрос молча начнёт сравнивать ещё и даты.

JOIN нескольких таблиц и подсчёты

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

SELECT o.id, c.name, p.title, i.qty
FROM orders AS o
JOIN customers   AS c ON c.id = o.customer_id
JOIN order_items AS i ON i.order_id = o.id
JOIN products    AS p ON p.id = i.product_id
WHERE o.id = 101;

Подсчёт с GROUP BY без размножения строк

Сумма и количество заказов по каждому клиенту, включая тех, у кого заказов нет:

SELECT c.name,
       COUNT(o.id)              AS orders_count,
       COALESCE(SUM(o.total), 0) AS orders_sum
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
GROUP BY c.id, c.name;

Считайте COUNT(o.id), а не COUNT(*): иначе клиент без заказов получит 1 вместо 0. Если же присоединить к клиентам сразу и заказы, и, например, обращения в поддержку, строки перемножатся (2 заказа × 3 обращения = 6 строк), и суммы окажутся завышены. В таких случаях сначала агрегируйте каждую таблицу в подзапросе, а потом соединяйте готовые итоги.

UPDATE и DELETE с JOIN в MySQL

MySQL позволяет изменять и удалять строки с учётом данных другой таблицы:

-- Пометить VIP всех клиентов с заказами больше 4000
UPDATE customers AS c
JOIN orders AS o ON o.customer_id = c.id
SET c.is_vip = 1
WHERE o.total > 4000;

-- Удалить заказы, у которых нет клиента
DELETE o
FROM orders AS o
LEFT JOIN customers AS c ON c.id = o.customer_id
WHERE c.id IS NULL;

Перед UPDATE или DELETE с JOIN выполните тот же запрос как SELECT и посмотрите, какие строки он затронет. В DELETE после слова DELETE указывается псевдоним таблицы, из которой удалять, иначе можно стереть строки из обеих.

В PostgreSQL синтаксис другой: UPDATE ... FROM ... и DELETE ... USING .... Там же есть FULL JOIN и LEFT JOIN LATERAL для подзапросов, зависящих от строки; как поставить PostgreSQL для экспериментов, описано в статье об установке PostgreSQL.

Как ускорить запросы с JOIN

• Индекс на столбце соединения во второй таблице обязателен: без INDEX (customer_id) MySQL перебирает все заказы для каждого клиента.
• Типы столбцов в условии должны совпадать: INT с INT, строки с одинаковой кодировкой и сравнением, иначе индекс не используется.
• Выбирайте только нужные столбцы вместо SELECT *: меньше данных - быстрее ответ и меньше путаницы с одноимёнными полями.
• Проверяйте план запроса: EXPLAIN SELECT .... Значение ALL в столбце type для большой таблицы означает полный перебор.

В Laravel те же соединения пишутся через построитель запросов - ->join(), ->leftJoin(), ->crossJoin(); примеры есть в статье про Eloquent join. В чистом PHP запрос с JOIN выполняется так же, как любой другой, через подготовленные выражения PDO.

Шпаргалка по видам JOIN