JOIN в SQL: виды соединений с примерами

JOIN соединяет строки двух таблиц по условию — обычно по равенству ключей: o.user_id = u.id. Видов пять: INNER, LEFT, RIGHT, FULL и CROSS. Разница между ними ровно одна: что делать со строками, которым не нашлось пары. Дальше все пять — на одном примере, который целиком помещается в голову.

Соединения вообще нужны потому, что данные заранее разложены по отдельным таблицам: имя клиента хранится один раз, а не в каждом его заказе. Откуда берётся такое разбиение, разобрано в статье про нормализацию — там одна плоская таблица за три шага превращается ровно в users и orders из примера ниже.

Две таблицы. В users три пользователя, в orders четыре заказа:

Обе «проблемные» строки заложены заранее: у Веры нет заказов, а заказ 13 ссылается на пользователя 7, которого нет — аккаунт удалили. Именно на этих двух строках видна разница между видами JOIN.

Что возвращает INNER JOIN

Только строки, у которых пара нашлась в обеих таблицах. Всё, что осталось без пары, молча отбрасывается.

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

Вера пропала: заказов нет. Заказ 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;

Типовое применение — найти строки без пары. Пользователи, которые ни разу не заказывали:

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;

Любой 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;

В выдаче и Вера, и заказ 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

На чём ловят на собеседовании

Условие по правой таблице в 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:

LEFT JOIN orders o ON o.user_id = u.id AND o.amount > 1000

Дубли при связи один-ко-многим ломают агрегаты. У Ани два заказа, после JOIN её строка размножилась. Присоедини к этому набору ещё одну таблицу «один-ко-многим» — например, платежи — и SUM(o.amount) посчитает каждый заказ столько раз, сколько у него платежей. Агрегируй в подзапросе до соединения или считай 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 с решениями — вторая и третья как раз про соединения.