Соединение таблиц: JOIN и LEFT JOIN

Зачем данные лежат в разных таблицах

В таблице заказов нет имён клиентов и названий книг — только числа:

customer_id ссылается на id в таблице клиентов, book_id — на id в таблице книг. Такой столбец-ссылку называют внешним ключом.

Зачем так сложно? Клиент описан один раз, в своей таблице. Если Анна сменит город, правка нужна в одной строке, а не в каждом её заказе. Опечатка в имени тоже не размножится по сотне заказов. Платим за этот порядок одним: чтобы увидеть имя рядом с заказом, таблицы нужно соединить.

JOIN: соединяем по ключу

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

id | name  | amount
1  | Анна  | 590
2  | Анна  | 720
3  | Борис | 1200
4  | Вера  | 650
5  | Борис | 890
6  | Глеб  | 450
7  | Борис | 540
8  | Вера  | 480
9  | Дина  | 650

Разберём запись:

• FROM orders AS o — основная таблица и её короткое имя o;
• JOIN customers AS c — вторая таблица с именем c;
• ON c.id = o.customer_id — правило: строка заказа соединяется с клиентом, у которого id совпадает с customer_id.

Короткие имена таблиц — псевдонимы — избавляют от длинных orders.customer_id. А когда в обеих таблицах есть столбец с одинаковым именем, как id, без них не обойтись: o.id и c.id — разные столбцы.

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

SELECT c.name, b.title, o.amount
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
JOIN books AS b ON b.id = o.book_id
WHERE o.status = 'в пути';

name  | title  | amount
Борис | Хоббит | 540
Вера  | Идиот  | 480

Такой JOIN ещё называют INNER JOIN — слово INNER можно не писать. Он оставляет только найденные пары: если бы в заказе стоял несуществующий customer_id, заказ бы в результат не попал.

LEFT JOIN: сохранить всех

Посчитаем, сколько заказов у каждого клиента, — включая тех, у кого их нет. Обычный JOIN потеряет Егора: пары для него не найдётся. LEFT JOIN сохраняет все строки левой таблицы, а там, где пары нет, подставляет NULL:

SELECT c.name, COUNT(o.id) AS orders_count
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY orders_count DESC, c.name;

name  | orders_count
Борис | 3
Анна  | 2
Вера  | 2
Глеб  | 1
Дина  | 1
Егор  | 0

Обратите внимание на COUNT(o.id). У Егора в результате соединения одна строка, где все поля заказа — NULL. COUNT(*) посчитал бы эту строку и показал бы у Егора 1 заказ. COUNT(o.id) пропускает NULL и показывает честный ноль.

Группируем по c.id, c.name, а не только по имени: два разных клиента могут быть тёзками, а id у них разный.

Тот же приём находит записи без пары — например, клиентов, которые ещё ничего не купили:

SELECT c.name
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
WHERE o.id IS NULL;

name
Егор

Как не размножить строки

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

SELECT COUNT(*) AS rows_after
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id;

rows_after
9

А вот что бывает при ошибке в ON — например, если случайно сравнить столбец клиента с самим собой:

SELECT COUNT(*) AS rows_after
FROM orders AS o
JOIN customers AS c ON c.id = c.id;

rows_after
54

Условие всегда истинно, и каждый из 9 заказов соединился с каждым из 6 клиентов: 9 × 6 = 54. Если после такого соединения посчитать выручку, она вырастет вшестеро. Поэтому правило простое: перед тем как считать деньги, сверьте количество строк.

SELECT COALESCE(c.city, 'не указан') AS city, SUM(o.amount) AS revenue
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
WHERE o.status <> 'отменён'
GROUP BY c.city
ORDER BY revenue DESC;

city      | revenue
Москва    | 2440
Казань    | 1740
не указан | 650
Пермь     | 450

SELECT b.genre, SUM(o.amount) AS revenue
FROM orders AS o
JOIN books AS b ON b.id = o.book_id
WHERE o.status <> 'отменён'
GROUP BY b.genre
ORDER BY revenue DESC;

genre   | revenue
фэнтези | 1840
роман   | 1520
IT      | 1200
наука   | 720