4 базовых типа JOIN в SQL
Объединение таблиц с помощью JOIN — одна из фундаментальных операций в SQL, вопросы про которую практически всегда встречаются на собеседованиях.
В статье разберем 4 базовых типа JOIN в SQL:
Для примеров используем две таблицы: authors и books.
CREATE TABLE authors (
id INT PRIMARY KEY,
name TEXT NOT NULL
);
CREATE TABLE books (
id INT PRIMARY KEY,
author_id INT,
title TEXT NOT NULL,
FOREIGN KEY (author_id) REFERENCES authors(id)
);
-- наполнение справочника авторов
INSERT INTO authors (id, name) VALUES
(1, 'Лев Толстой'),
(2, 'Федор Достоевский'),
(3, 'Антон Чехов'),
(4, 'Александр Пушкин'),
(5, 'Марина Цветаева'); -- без книг
-- наполнение книг (две книги без автора)
INSERT INTO books (id, author_id, title) VALUES
(101, 1, 'Война и мир'),
(102, 1, 'Анна Каренина'),
(103, 2, 'Преступление и наказание'),
(104, 2, 'Идиот'),
(105, 3, 'Вишневый сад'),
(106, 3, 'Чайка'),
(107, NULL, 'Неизвестный роман'),
(108, NULL, 'Черновая версия книги');
Отметим, что для примера специально смоделированы следующие ситуации:
- Александр Пушкин присутствует в справочнике, но без произведений.
- В таблице books два произведения не связаны ни с одним автором.
Далее мы покажем, как разные виды JOIN ведут себя с отсутствующими данными и как извлекать строки, у которых нет пары в связанной таблице.
INNER JOIN: пересечение двух таблиц
INNER JOIN — самый часто используемый и интуитивно понятный тип соединения: он возвращает только те строки, для которых нашлась пара в обеих таблицах. Другими словами, это пересечение множеств по условию соединения.
SELECT <список столбцов>
FROM таблица_A [AS] a
INNER JOIN таблица_B [AS] b
ON a.ключ = b.ключ;Ключевое слово INNER можно опустить: JOIN без уточнения по умолчанию будет интерпретирован как INNER JOIN.
Задача: Получить список книг вместе с именами их авторов (только у тех книг, где автор действительно указан).
SELECT
b.title AS книга,
a.name AS автор
FROM books AS b
INNER JOIN authors AS a
ON a.id = b.author_id;
Две книги с author_id = NULL в выборку не попали, как и Александр Пушкин, произведения которого отсутствуют во второй таблице.
Фильтр в ON и фильтр в WHERE
Ошибка: смешивать логику соединения и пост-фильтрацию так, что результат неожиданно меняется.
Пример 1. Условие в ON, фильтрация книг по идентификатору до объединения: например, оставим только книги с id > 103.
SELECT a.name, b.title
FROM authors a
INNER JOIN books b
ON a.id = b.author_id
AND b.id > 103; Сначала ограничиваем таблицу books, оставляя лишь те книги, идентификатор которых больше 103, а затем — объединяем с authors.
Пример 2. Условие в WHERE с фильтрацией после объединения.
SELECT a.name, b.title
FROM authors a
JOIN books b
ON a.id = b.author_id
WHERE b.id > 103; В случае INNER JOIN оба запроса вернут одинаковый набор строк, но СУБД может построить разный план: во втором варианте соединяются все пары, а затем лишние отбрасываются. При небольших объемах разница будет не столь заметна, однако на больших таблицах порядок фильтрации влияет на скорость.
LEFT (OUTER) JOIN: все строки из левой таблицы + совпадения справа
LEFT JOIN (полное название — LEFT OUTER JOIN) сохраняет все строки левой таблицы, даже если в правой парной строки нет. Для отсутствующих совпадений поля правой стороны заполняются NULL.
SELECT <столбцы>
FROM таблица_A AS a
LEFT JOIN таблица_B AS b
ON a.ключ = b.ключ;Ключевое слово OUTER опционально: LEFT JOIN и LEFT OUTER JOIN эквивалентны.
Пример: авторы и их книги, включая тех, у кого книг нет.
SELECT
a.name AS автор,
b.title AS книга
FROM authors a
LEFT JOIN books b
ON a.id = b.author_id
ORDER BY a.id, b.id;
Два последних автора появились благодаря LEFT JOIN: хотя совпадений в books нет, строки из authors сохранены.
Фильтр в ON и фильтр в WHERE
LEFT JOIN наиболее чувствителен к тому, куда вы поместите фильтр. Фильтр в ON сохранит левую строку, даже если условие не выполнено.
SELECT a.name, b.title
FROM authors a
LEFT JOIN books b
ON a.id = b.author_id
AND b.title LIKE '%мир%';
Как получаем такой результат: начинаем обходить левую таблицу (authors), рассматриваем первую строчку — Лев Толстой. В правой таблице ищем все строки, которые удовлетворяют условию в ON. Если такие строки есть, то соединяем их с текущим автором и добавляем в итоговую таблицу. У Льва Толстого «Война и мир» удовлетворяет LIKE, получаем одну строку. Если ни одна книга автора не прошла фильтр (LIKE '%мир%' вернул 0 совпадений) LEFT JOIN все равно обязан вернуть автора, но с NULL во всех полях правой стороны.
Так происходит с Федором Достоевским, Антоном Чеховым, Александром Пушкиным и Мариной Цветаевой: поиск по условию a.id = b.author_id AND b.title LIKE '%мир%' не находит книг, поэтому выводится строка автора и NULL в колонках books.
Фильтр в WHERE уберет строки, где правый столбец = NULL:
SELECT a.name, b.title
FROM authors a
LEFT JOIN books b
ON a.id = b.author_id
WHERE b.title LIKE '%мир%'; 
В данном случае авторы, названия произведений которых не содержит «мир», не попадают в итоговую таблицу, а LEFT JOIN фактически превращается в INNER JOIN.
RIGHT (OUTER) JOIN
RIGHT JOIN (или RIGHT OUTER JOIN) возвращает все строки правой таблицы и только те строки левой, для которых нашлось совпадение. По сути, это зеркальное отражение LEFT JOIN.
SELECT <столбцы>
FROM таблица_A AS a
RIGHT JOIN таблица_B AS b
ON a.ключ = b.ключ;Ключевое слово OUTER опционально. Условие соединения записывается точно так же, как и в LEFT/INNER JOIN.
Пример: список книг с именем автора, включая те, у которых автор неизвестен.
SELECT
b.title AS книга,
a.name AS автор
FROM authors a
RIGHT JOIN books b
ON a.id = b.author_id
ORDER BY b.id;
Две безымянные книги с author_id = NULL попали в итоговую таблицу именно благодаря RIGHT JOIN: строки правой таблицы books сохраняются всегда, даже если левый справочник авторов не дал пары.
Зачастую считается верным придерживаться принципа использования только LEFT JOIN, при необходимости меняя порядок таблиц, для сохранения лаконичности и единообразия кода и избавления от путаницы с определением левой или правой таблицы.
-- RIGHT JOIN SELECT ... FROM A RIGHT JOIN B ON A.key = B.key; -- тот же результат через LEFT JOIN SELECT ... FROM B -- меняем порядок LEFT JOIN A ON B.key = A.key;
FULL OUTER JOIN: полное объединение
FULL OUTER JOIN объединяет преимущества левого и правого соединений: он возвращает все строки обеих таблиц, заполняя NULL там, где парной записи нет.
SELECT <столбцы>
FROM таблица_A AS a
FULL OUTER JOIN таблица_B AS b
ON a.ключ = b.ключ;Пример: получаем полный список авторов и книг.
SELECT
COALESCE(a.name , 'Нет автора') AS автор,
COALESCE(b.title, 'Нет книги') AS книга
FROM authors a
FULL OUTER JOIN books b
ON a.id = b.author_id
ORDER BY a.id NULLS LAST, b.id;
Если FULL JOIN не поддерживается (например, в SQLite), то реализовать его можно с помощью LEFT и RIGHT.
SELECT
a.id AS author_id,
a.name AS author_name,
b.id AS book_id,
b.title
FROM authors a
LEFT JOIN books b
ON a.id = b.author_id
UNION ALL
SELECT
a.id AS author_id,
a.name AS author_name,
b.id AS book_id,
b.title
FROM authors a
RIGHT JOIN books b
ON a.id = b.author_id
WHERE a.id IS NULL;
Итог
INNER JOIN обеспечивает строгое пересечение при объединение таблиц и экономит пространство оперативной памяти, LEFT / RIGHT — позволяют сохранить при объединении данные, которые не представлены в парном формате, FULL — склеивать таблицы, сохраняя информацию из каждой.
При выборе определите, какая из таблиц представляет больший интерес и является более важной:
- если ни одна — выбирайте INNER для объединения;
- если одна из — LEFT или RIGHT (зависит, где она стоит в запросе);
- если обе важны — FULL OUTER.
Проверьте фильтры:
Условия, по которым строится связь, держите в секции ON. Пост-фильтрацию — в WHERE. Это особенно критично для LEFT/RIGHT: одно неверное условие в WHERE, и обязательные информативные строки пропадут.