JOIN соединяет строки двух таблиц по условию — обычно по равенству ключей: o.user_id = u.id. Видов пять: INNER, LEFT, RIGHT, FULL и CROSS. Разница между ними ровно одна: что делать со строками, которым не нашлось пары. Дальше все пять — на одном примере, который целиком помещается в голову.
Соединения вообще нужны потому, что данные заранее разложены по отдельным таблицам: имя клиента хранится один раз, а не в каждом его заказе. Откуда берётся такое разбиение, разобрано в статье про нормализацию — там одна плоская таблица за три шага превращается ровно в users и orders из примера ниже.
Две таблицы. В users три пользователя, в orders четыре заказа:
| id | name |
|---|---|
| 1 | Аня |
| 2 | Борис |
| 3 | Вера |
| id | user_id | amount |
|---|---|---|
| 10 | 1 | 900 |
| 11 | 1 | 2400 |
| 12 | 2 | 1200 |
| 13 | 7 | 500 |
Обе «проблемные» строки заложены заранее: у Веры нет заказов, а заказ 13 ссылается на пользователя 7, которого нет — аккаунт удалили. Именно на этих двух строках видна разница между видами JOIN.
Чтобы повторить примеры, создай обе таблицы в учебном сеансе. Внешнего ключа у orders.user_id здесь нет намеренно: иначе заказ 13 с несуществующим пользователем нельзя было бы вставить, а на нём держится половина статьи.
CREATE TEMP TABLE users (
id integer PRIMARY KEY,
name text NOT NULL
);
CREATE TEMP TABLE orders (
id integer PRIMARY KEY,
user_id integer NOT NULL,
amount integer NOT NULL
);
INSERT INTO users (id, name) VALUES
(1, 'Аня'),
(2, 'Борис'),
(3, 'Вера');
INSERT INTO orders (id, user_id, amount) VALUES
(10, 1, 900),
(11, 1, 2400),
(12, 2, 1200),
(13, 7, 500);JOIN соединяет ключи, а не номера строк
INNER JOIN создаёт по строке на каждую найденную пару ключей; строки без пары с обеих сторон отбрасываются.Что возвращает INNER JOIN
Только строки, у которых пара нашлась в обеих таблицах. Всё, что осталось без пары, молча отбрасывается.
SELECT u.name, o.amount
FROM users u
JOIN orders o ON o.user_id = u.id
ORDER BY u.id, o.id;| name | amount |
|---|---|
| Аня | 900 |
| Аня | 2400 |
| Борис | 1200 |
Вера пропала: заказов нет. Заказ 13 тоже пропал: не к кому присоединять. JOIN без уточнения — это и есть INNER JOIN, слово INNER можно не писать.
Вторая деталь важнее: Аня в результате дважды. JOIN не «склеивает таблицы», а строит пары строк — сколько пар нашлось, столько строк и получишь.
Когда нужен LEFT JOIN
Когда нужны все строки левой таблицы, даже без пары. Пустые места справа заполняются NULL.
SELECT u.name, o.amount
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
ORDER BY u.id, o.id;| name | amount |
|---|---|
| Аня | 900 |
| Аня | 2400 |
| Борис | 1200 |
| Вера | NULL |
Типовое применение — найти строки без пары. Пользователи, которые ни разу не заказывали:
SELECT u.name
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;LEFT JOIN даёт Вере строку с NULL вместо заказа, а WHERE o.id IS NULL оставляет только такие строки. Этот приём на собеседованиях спрашивают чаще, чем определения всех видов JOIN вместе взятые. Он же порождает большинство ошибок с NULL — они собраны в отдельном разборе LEFT JOIN.
Почему RIGHT JOIN почти не пишут
RIGHT JOIN — зеркало LEFT: все строки правой таблицы плюс совпадения из левой. Он сохранит заказ 13 и отбросит Веру.
SELECT u.name, o.amount
FROM users u
RIGHT JOIN orders o ON o.user_id = u.id
ORDER BY u.id, o.id;| name | amount |
|---|---|
| Аня | 900 |
| Аня | 2400 |
| Борис | 1200 |
| NULL | 500 |
Любой RIGHT JOIN превращается в LEFT JOIN перестановкой таблиц. Запрос читают сверху вниз, поэтому «главную» таблицу принято ставить первой и писать LEFT. Встретил RIGHT JOIN в чужом коде — скорее всего, условие дописывали к уже готовому FROM.
Что делает FULL JOIN
Всё из обеих таблиц: пары — где нашлись, NULL — где нет.
SELECT u.name, o.amount
FROM users u
FULL JOIN orders o ON o.user_id = u.id
ORDER BY u.id, o.id;| name | amount |
|---|---|
| Аня | 900 |
| Аня | 2400 |
| Борис | 1200 |
| Вера | NULL |
| NULL | 500 |
В выдаче и Вера, и заказ 13 — обе стороны без потерь. На практике FULL JOIN нужен редко, почти всегда для сверок: найти расхождения между двумя источниками данных.
Зачем нужен CROSS JOIN
CROSS JOIN строит все комбинации: каждая строка левой таблицы с каждой строкой правой, условия ON у него нет.
SELECT u.name, o.id
FROM users u
CROSS JOIN orders o;3 пользователя × 4 заказа = 12 строк. Осмысленное применение — сгенерировать сетку: все размеры для всех цветов товара, все даты для всех филиалов. Во всех остальных случаях декартово произведение — ошибка, и на реальных объёмах она бьёт сразу: две таблицы по 10 000 строк дают 100 миллионов строк результата.
Три таблицы: цепочка JOIN и ловушка с суммами
Соединений в запросе может быть сколько угодно: каждое со своим ON, а результат предыдущего становится левой стороной для следующего. Добавим платежи — заказ 11 оплачен двумя частями, заказ 12 не оплачен вовсе:
CREATE TEMP TABLE payments (
id integer PRIMARY KEY,
order_id integer NOT NULL,
paid integer NOT NULL
);
INSERT INTO payments (id, order_id, paid) VALUES
(100, 10, 900),
(101, 11, 1000),
(102, 11, 1400);Цепочка из двух INNER JOIN оставит только то, что связано на всех трёх уровнях:
SELECT u.name, o.id AS order_id, p.paid
FROM users u
JOIN orders o ON o.user_id = u.id
JOIN payments p ON p.order_id = o.id
ORDER BY u.id, o.id, p.id;| name | order_id | paid |
|---|---|---|
| Аня | 10 | 900 |
| Аня | 11 | 1000 |
| Аня | 11 | 1400 |
Борис исчез: его заказ 12 не оплачен, и второй INNER JOIN отбросил строку. Виды соединений в цепочке можно смешивать — чтобы сохранить заказы без платежей, второе соединение делают LEFT:
SELECT u.name, o.id AS order_id, p.paid
FROM users u
JOIN orders o ON o.user_id = u.id
LEFT JOIN payments p ON p.order_id = o.id
ORDER BY u.id, o.id, p.id;| name | order_id | paid |
|---|---|---|
| Аня | 10 | 900 |
| Аня | 11 | 1000 |
| Аня | 11 | 1400 |
| Борис | 12 | NULL |
Теперь ловушка. Заказ 11 встречается дважды — по разу на каждый платёж. Пока строки просто смотрят, это безвредно; как только их начинают складывать, сумма заказов удваивается:
SELECT u.name,
sum(o.amount) AS orders_total,
sum(p.paid) AS paid_total
FROM users u
JOIN orders o ON o.user_id = u.id
LEFT JOIN payments p ON p.order_id = o.id
GROUP BY u.name
ORDER BY u.name;| name | orders_total | paid_total |
|---|---|---|
| Аня | 5700 | 3300 |
| Борис | 1200 | NULL |
У Ани заказов на 3300, а в колонке 5700: сумма 2400 за заказ 11 посчитана дважды. Сумма платежей при этом верна — каждый платёж в результате ровно один раз. Лечится это агрегацией до соединения: сначала свернуть платежи по заказу, потом присоединять:
SELECT u.name,
sum(o.amount) AS orders_total,
sum(pp.paid) AS paid_total
FROM users u
JOIN orders o ON o.user_id = u.id
LEFT JOIN (
SELECT order_id, sum(paid) AS paid
FROM payments
GROUP BY order_id
) pp ON pp.order_id = o.id
GROUP BY u.name
ORDER BY u.name;| name | orders_total | paid_total |
|---|---|---|
| Аня | 3300 | 3300 |
| Борис | 1200 | NULL |
Правило простое: перед агрегатом каждая строка левой таблицы должна встречаться столько раз, сколько ты ожидаешь. Если к набору присоединяется ещё одна связь «один ко многим», сворачивай её в подзапросе или считай count(DISTINCT ...).
Как выбрать вид JOIN
| Вид | Что возвращает | Когда брать |
|---|---|---|
| INNER JOIN | только совпавшие пары | нужны записи, у которых точно есть связь |
| LEFT JOIN | всю левую + совпадения | главная таблица слева, связь не обязательна |
| RIGHT JOIN | всю правую + совпадения | почти никогда — перепиши в LEFT |
| FULL JOIN | всё из обеих | сверка двух источников |
| CROSS JOIN | все комбинации | генерация сеток; чаще случается по ошибке |
На чём ловят на собеседовании
Условие по правой таблице в WHERE убивает LEFT JOIN. Запрос «все пользователи и их крупные заказы»:
SELECT u.name, o.amount
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.amount > 1000;У Веры в колонке o.amount стоит NULL, условие NULL > 1000 не выполняется — строка отброшена. LEFT JOIN молча превратился в INNER. Если фильтр относится к правой таблице, а левую терять нельзя, перенеси его в ON:
SELECT u.name, o.amount
FROM users u
LEFT JOIN orders o ON o.user_id = u.id AND o.amount > 1000
ORDER BY u.id, o.id;| name | amount |
|---|---|
| Аня | 2400 |
| Борис | 1200 |
| Вера | NULL |
Теперь строка Веры осталась: условие отсеяло мелкий заказ Ани ещё при поиске пары, а не после него.
Дубли при связи один-ко-многим ломают агрегаты. У Ани два заказа, после JOIN её строка размножилась. Присоедини к этому набору ещё одну таблицу «один-ко-многим» — например, платежи — и SUM(o.amount) посчитает каждый заказ столько раз, сколько у него платежей: в примере с тремя таблицами выше так получилось 5700 вместо 3300. Агрегируй в подзапросе до соединения или считай COUNT(DISTINCT ...).
JOIN через запятую. Старый синтаксис FROM users, orders WHERE ... работает, но условие соединения в нём легко потерять, а FROM users, orders без WHERE — это уже CROSS JOIN на весь объём. Явный JOIN ... ON забыть условие не даст: без ON запрос не выполнится.
Частые вопросы
Чем JOIN отличается от INNER JOIN
Ничем. JOIN — сокращённая запись INNER JOIN. Так же LEFT JOIN равен LEFT OUTER JOIN: слово OUTER опциональное.
Можно ли соединить три таблицы и больше
Да. JOIN пишутся цепочкой, каждый со своим ON: users JOIN orders ON ... JOIN payments ON .... Результат первого соединения становится левой стороной для следующего — пример с платежами разобран выше. Для цепочки из INNER порядок на результат не влияет — оптимизатор переставит соединения сам.
Во всех ли СУБД это работает
INNER, LEFT, RIGHT и CROSS — стандарт: PostgreSQL, MySQL, SQLite, SQL Server. Исключение одно: FULL JOIN не умеет MySQL (его собирают вручную через UNION из LEFT и RIGHT), а SQLite поддерживает его только с версии 3.39.
Что учить следом
Соединения почти всегда идут в паре с расчётами по группам: собрал данные из двух таблиц, считаешь итоги через GROUP BY, а рейтинги и накопительные суммы через оконные функции. Отдельно стоит развести JOIN и UNION: первый добавляет колонки, второй строки, и путают их постоянно. Проверить, что JOIN уложился, можно на задачах по SQL с решениями — вторая и третья как раз про соединения.