Виды JOIN: INNER, LEFT, RIGHT, FULL, CROSS

Вопрос про виды соединений звучит почти на каждом собеседовании, где есть SQL. Его любят за то, что за минуту видно, понимает ли кандидат, куда деваются строки без пары. Теорию соединений ты уже видел в уроке про соединения и деление, здесь отвечаем на вопрос так, как его задают, и проверяем каждое слово на учебной базе блока.

Как спрашивают

Какие бывают JOIN и чем они отличаются? Сколько строк вернёт каждый для таблиц сотрудников и отделов?

Иногда просят нарисовать круги Эйлера, иногда сразу дают две таблицы и просят посчитать строки.

Короткий ответ

INNER JOIN оставляет только пары, нашедшие совпадение. LEFT JOIN сохраняет все строки левой таблицы и дописывает NULL там, где пары нет, RIGHT JOIN делает то же для правой. FULL JOIN сохраняет строки без пары с обеих сторон. CROSS JOIN склеивает каждую строку с каждой без условия, строк получается произведение размеров таблиц.

Разбор: рассадка на свадьбе

Представь два списка: гости и столы. Распорядитель сажает гостей за столы по номеру в приглашении.

• INNER. В зале фотографируют только занятые столы с их гостями. Гость без стола и пустой стол на фото не попадают.
• LEFT. Фотографируют каждого гостя. У кого нет стола, стоит с табличкой «без стола».
• RIGHT. Фотографируют каждый стол. Пустой стол попадает в кадр без гостей.
• FULL. В кадре все: и гости без стола, и пустые столы.
• CROSS. Каждого гостя фотографируют за каждым столом, без всякой рассадки.

На учебной базе роль «гостя без стола» играет Жанна: у неё нет отдела. «Пустой стол» это отдел «Маркетинг», где никто не работает.

INNER JOIN

SELECT e.name, d.name AS department
FROM employees e
JOIN departments d ON d.id = e.department_id
ORDER BY e.id;

name  | department
------+-----------
Анна  | Разработка
Борис | Разработка
Вера  | Разработка
Глеб  | Продажи
Дина  | Продажи
Егор  | Поддержка

Шесть строк. Жанна пропала: у неё department_id равен NULL, а NULL не равен ничему. Маркетинг тоже пропал: ни один сотрудник на него не ссылается. Слово INNER можно не писать, JOIN без уточнения означает внутреннее соединение.

LEFT JOIN

SELECT e.name, d.name AS department
FROM employees e
LEFT JOIN departments d ON d.id = e.department_id
ORDER BY e.id;

Те же шесть строк плюс седьмая: Жанна | NULL. Левая таблица здесь employees, и внешнее соединение сохраняет каждую её строку.

RIGHT JOIN

SELECT e.name, d.name AS department
FROM employees e
RIGHT JOIN departments d ON d.id = e.department_id
ORDER BY d.id, e.id;

Шесть пар и строка NULL | Маркетинг. Теперь сохраняется правая таблица, отделы, и пустой отдел попадает в ответ. Жанны здесь нет. RIGHT JOIN всегда можно переписать как LEFT JOIN, поменяв таблицы местами, поэтому в рабочем коде его встречают редко: читать запрос, где главная таблица слева, проще.

FULL JOIN

SELECT e.name, d.name AS department
FROM employees e
FULL JOIN departments d ON d.id = e.department_id
ORDER BY d.id NULLS LAST, e.id;

name  | department
------+-----------
Анна  | Разработка
Борис | Разработка
Вера  | Разработка
Глеб  | Продажи
Дина  | Продажи
Егор  | Поддержка
NULL  | Маркетинг
Жанна | NULL

Восемь строк: шесть пар, Маркетинг без сотрудников и Жанна без отдела. FULL JOIN нужен, когда сверяешь два списка и хочешь увидеть расхождения с обеих сторон.

CROSS JOIN

SELECT count(*) FROM employees CROSS JOIN departments;

Ответ 28: семь сотрудников на четыре отдела. Условия нет, каждая строка склеена с каждой. Это декартово произведение из урока про операции над множествами.

Вид	Строк на учебной базе	Кто попал без пары
INNER	6	никто
LEFT	7	Жанна
RIGHT	7	Маркетинг
FULL	8	Жанна и Маркетинг
CROSS	28	условия нет