Оператор JOIN: соединение таблиц в SQL

Оператор JOIN в SQL объединяет строки двух и более таблиц в один результат по общему столбцу, например заказы с покупателями по id клиента. В статье на одном наборе данных показано, какие строки возвращают INNER, LEFT, RIGHT, FULL и CROSS JOIN, как соединять несколько таблиц, где чаще всего ошибаются и как выполнить соединение из Python.

Как работает JOIN

В нормализованной базе данные разнесены по разным таблицам: в одной клиенты, в другой заказы со ссылкой на id клиента. Такая связь через внешний ключ описана в статье о структуре таблицы базы данных. Чтобы получить в одном отчете имя клиента и сумму заказа, таблицы соединяют.

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

После FROM указывают первую (левую) таблицу, после JOIN вторую (правую), а после ON условие, по которому строки считаются парой. Короткие псевдонимы o и c избавляют от повторения длинных имен таблиц. Если в обеих таблицах есть столбец с одинаковым именем, его обязательно указывают с префиксом таблицы, иначе СУБД выдаст ошибку о неоднозначном столбце.

Тестовые таблицы

Все примеры ниже выполняются на двух маленьких таблицах. У клиента Вера нет заказов, а заказ 4 ссылается на клиента, которого нет в таблице customers.

Виды JOIN

Типы соединений различаются тем, что происходит со строками, для которых пара не нашлась: они либо отбрасываются, либо остаются в результате с NULL вместо недостающих значений.

INNER JOIN

Внутреннее соединение возвращает только строки, у которых есть пара в обеих таблицах. Слово INNER можно не писать: обычный JOIN работает именно так.

SELECT c.name, o.id, o.amount
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id;
-- Анна  | 1 | 500
-- Анна  | 2 | 1200
-- Борис | 3 | 300

Вера пропала, потому что у нее нет заказов, а заказ 4 пропал, потому что клиента 5 нет. Анна встречается дважды: на каждую строку левой таблицы приходится столько строк, сколько у нее пар справа.

LEFT JOIN

Левое соединение сохраняет все строки левой таблицы. Если пары нет, столбцы правой таблицы заполняются NULL. Это самый частый вид соединения в отчетах: показать всех клиентов, в том числе без покупок.

SELECT c.name, o.id, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;
-- Анна  | 1    | 500
-- Анна  | 2    | 1200
-- Борис | 3    | 300
-- Вера  | NULL | NULL

Добавив условие WHERE o.id IS NULL, получим клиентов, у которых нет ни одного заказа. Это стандартный прием поиска строк без пары.

RIGHT JOIN

Правое соединение зеркально левому: сохраняются все строки правой таблицы. Любой RIGHT JOIN можно переписать как LEFT JOIN, поменяв таблицы местами, поэтому на практике его используют редко. В SQLite RIGHT и FULL JOIN поддерживаются начиная с версии 3.39.

SELECT c.name, o.id, o.amount
FROM customers c
RIGHT JOIN orders o ON o.customer_id = c.id;
-- Анна  | 1 | 500
-- Анна  | 2 | 1200
-- Борис | 3 | 300
-- NULL  | 4 | 700

FULL OUTER JOIN

Полное соединение возвращает все строки обеих таблиц: пары соединяются, а строки без пары с каждой стороны дополняются NULL. Удобно для сверки двух списков, когда нужно увидеть расхождения в обе стороны. В MySQL FULL JOIN нет, его собирают из LEFT JOIN и RIGHT JOIN через UNION.

SELECT c.name, o.id
FROM customers c
FULL OUTER JOIN orders o ON o.customer_id = c.id;
-- Анна | 1,  Анна | 2,  Борис | 3,  Вера | NULL,  NULL | 4

CROSS JOIN

Перекрестное соединение не имеет условия и сочетает каждую строку левой таблицы с каждой строкой правой. Для наших таблиц получится 3 x 4 = 12 строк. Используют его для генерации комбинаций, например всех пар размер-цвет для каталога товаров.

SELECT s.size, col.color
FROM sizes s
CROSS JOIN colors col;

Сравнение видов соединений

Сводная таблица поможет быстро выбрать нужный тип.

Соединение трех таблиц и самосоединение

JOIN можно повторять сколько угодно раз: каждое следующее соединение работает с результатом предыдущих. Например, добавим таблицу products и позиции заказа order_items.

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

Таблицу можно соединить саму с собой, если в ней хранится иерархия. Например, у сотрудника есть столбец manager_id со ссылкой на руководителя из той же таблицы. Псевдонимы тут обязательны, чтобы различать две копии.

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;

Частые ошибки при соединении

• Условие на правую таблицу в WHERE после LEFT JOIN. Запрос LEFT JOIN orders o ... WHERE o.amount > 400 отбросит строки с NULL и фактически превратится в INNER JOIN. Если клиенты без заказов должны остаться, переносите условие в ON: ON o.customer_id = c.id AND o.amount > 400. Подробнее о фильтрации в статье про оператор WHERE.
• Забытое условие ON. Во многих СУБД это синтаксическая ошибка, а в SQLite запрос выполнится как CROSS JOIN и вернет все сочетания строк.
• Неожиданные дубликаты. Если справа несколько пар, строка слева повторяется. При подсчете сумм это завышает итог, поэтому сначала агрегируйте в подзапросе, а потом соединяйте.
• Сравнение NULL в условии. Строки, у которых в ключе NULL, никогда не находят пару, ведь NULL = NULL не является истиной.

Если столбцы связи называются одинаково, вместо ON можно написать USING (customer_id), запись станет короче.

JOIN в Python через sqlite3

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

import sqlite3

conn = sqlite3.connect(':memory:')
conn.executescript('''
CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, amount INTEGER);
INSERT INTO customers VALUES (1, 'Анна'), (2, 'Борис'), (3, 'Вера');
INSERT INTO orders VALUES (1, 1, 500), (2, 1, 1200), (3, 2, 300), (4, 5, 700);
''')
sql = '''
SELECT c.name, COUNT(o.id), COALESCE(SUM(o.amount), 0)
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id
'''
for row in conn.execute(sql):
    print(row)
# ('Анна', 2, 1700)
# ('Борис', 1, 300)
# ('Вера', 0, 0)

COUNT(o.id) не учитывает NULL, поэтому для Веры получается 0, а COALESCE заменяет пустую сумму нулем. Больше о работе с базой из Python рассказано в статье Python sqlite.

Частые вопросы

Короткие ответы на вопросы о соединении таблиц.

Чем JOIN отличается от INNER JOIN?

Ничем. Если тип соединения не указан, используется внутреннее соединение, слово INNER добавляют для наглядности.

Чем LEFT JOIN отличается от LEFT OUTER JOIN?

Это одно и то же, слово OUTER необязательно. То же касается RIGHT OUTER JOIN и FULL OUTER JOIN.

Что работает быстрее, JOIN или подзапрос?

Современные СУБД часто строят для них одинаковый план выполнения. На скорость сильнее влияют индексы по столбцам из условия ON, чем форма записи.

Можно ли соединять таблицы без внешнего ключа?

Да. Внешний ключ лишь гарантирует целостность данных, а для JOIN достаточно столбцов с совместимыми значениями, по которым можно составить условие.