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

Справочник · SQL · 2 минуты чтения

INNER JOIN (или просто JOIN) выводит только строки, у которых нашлась пара в другой таблице. LEFT JOIN выводит все строки левой таблицы, а если пары нет, ставит NULL в столбцы правой.

Пример — покупатели и заказы. У Веры заказов нет, а заказ 4 оформлен на покупателя 7, которого в таблице нет. В учебной базе курса таблицы customers и orders уже есть, и данные в них другие, поэтому этот пример запускайте в отдельной базе, например sqlite3 join.db:

SQL

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total INTEGER, status TEXT);

INSERT INTO customers (name) VALUES ('Анна'), ('Борис'), ('Вера');
INSERT INTO orders (customer_id, total, status) VALUES
    (1, 20, 'paid'), (1, 85, 'new'), (2, 30, 'paid'), (7, 15, 'paid');

INNER JOIN — только пары

SQL

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

name	order_id	total
Анна	1	20
Анна	2	85
Борис	3	30

Нет ни Веры (у неё нет заказов), ни заказа 4 (у него нет покупателя).

LEFT JOIN — все строки левой таблицы

SQL

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

name	order_id	total
Анна	1	20
Анна	2	85
Борис	3	30
Вера	NULL	NULL

Вера появилась с NULL вместо заказа. «Левая» — таблица, написанная до LEFT JOIN. Если поменять таблицы местами (FROM orders o LEFT JOIN customers c …), в ответ попадут все заказы, в том числе заказ 4 с NULL вместо имени.

LEFT JOIN и LEFT OUTER JOIN — одно и то же, слово OUTER можно не писать.

Строки без пары

Покупатели без заказов — LEFT JOIN и проверка, что пары нет:

SQL

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

Ответ — Вера. Проверяйте столбец, который у настоящей пары никогда не бывает пустым, — лучше всего ключ правой таблицы.

Ошибка 1: условие на правую таблицу в WHERE

Нужны все покупатели и их оплаченные заказы. Условие на статус в WHERE:

SQL

SELECT c.name, o.id AS order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'paid'
ORDER BY c.id;

name	order_id
Анна	1
Борис	3

Вера пропала: у неё o.status — NULL, и условие NULL = 'paid' неверно. LEFT JOIN фактически превратился в INNER JOIN. Условие на правую таблицу переносят в ON:

SQL

SELECT c.name, o.id AS order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'paid'
ORDER BY c.id;

name	order_id
Анна	1
Борис	3
Вера	NULL

Правило: в ON — условия, по которым ищется пара; в WHERE — условия, по которым отбираются строки всего ответа.

Ошибка 2: COUNT(*) с LEFT JOIN

SQL

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

name	orders
Анна	2
Борис	1
Вера	0

С COUNT(*) у Веры получилось бы 1: строка с NULL — тоже строка. COUNT(o.id) пропускает NULL. Так же SUM(o.total) у Веры даст NULL, а не 0 — замените его на COALESCE(SUM(o.total), 0).

Что выбрать

Нужны только связанные данные (заказы с именами покупателей) — INNER JOIN.
Нужны все строки главной таблицы, даже без связанных (все покупатели, сколько бы заказов ни было) — LEFT JOIN.
RIGHT JOIN — то же, что LEFT JOIN с таблицами наоборот. FULL OUTER JOIN — все строки обеих таблиц; его нет в MySQL, а в SQLite он появился в версии 3.39.

Подробнее — урок Объединение таблиц — JOIN.