JOIN: чем INNER отличается от LEFT и где теряются данные

Самый частый вопрос первой минуты собеседования: «Чем INNER JOIN отличается от LEFT JOIN?»

JOIN — это операция, которая для каждой строки одной таблицы ищет подходящие строки в другой по условию ON. Тип JOIN определяет одно: что делать со строками, для которых пары не нашлось.

Вопрос 1. Чем INNER JOIN отличается от LEFT JOIN?

Что на самом деле проверяет интервьюер. Не знание синтаксиса — его гуглят за десять секунд. Проверяют, понимаете ли вы, что выбор типа JOIN напрямую меняет цифры в отчёте. Аналитик, который взял INNER JOIN там, где нужен LEFT, тихо потеряет часть клиентов и покажет бизнесу заниженную выручку. Это ошибка, которую никто не заметит месяцами.

Разберём на данных. Есть клиенты, у части из них есть заказы, а один заказ вообще пришёл без клиента (так бывает: гостевая покупка, битая выгрузка, удалённый профиль).

CREATE TABLE clients (id INTEGER PRIMARY KEY, name TEXT, city TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, client_id INTEGER, amount INTEGER);

INSERT INTO clients VALUES (1,'Анна','Москва'),(2,'Борис','Казань'),(3,'Вера','Пермь'),(4,'Глеб','Москва');
INSERT INTO orders  VALUES (10,1,1500),(11,1,700),(12,2,300),(13,NULL,900);

SELECT c.name, o.amount
FROM clients c
INNER JOIN orders o ON o.client_id = c.id
ORDER BY c.name, o.amount;

Вывод:

Анна   700
Анна   1500
Борис  300

Три строки. Вера и Глеб исчезли — у них нет заказов. Заказ на 900 тоже исчез — у него нет клиента. INNER JOIN оставляет только пересечение: строки, для которых пара нашлась с обеих сторон.

Меняем один оператор:

CREATE TABLE clients (id INTEGER PRIMARY KEY, name TEXT, city TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, client_id INTEGER, amount INTEGER);

INSERT INTO clients VALUES (1,'Анна','Москва'),(2,'Борис','Казань'),(3,'Вера','Пермь'),(4,'Глеб','Москва');
INSERT INTO orders  VALUES (10,1,1500),(11,1,700),(12,2,300),(13,NULL,900);

SELECT c.name, o.amount
FROM clients c
LEFT JOIN orders o ON o.client_id = c.id
ORDER BY c.name, o.amount;

Вывод:

Анна   700
Анна   1500
Борис  300
Вера   NULL
Глеб   NULL

LEFT JOIN гарантирует: каждая строка левой таблицы попадёт в результат хотя бы один раз. Не нашлось пары — поля правой таблицы заполняются NULL. Именно поэтому запрос «сколько заказов у каждого клиента, включая тех, кто ничего не купил» пишется только через LEFT JOIN.

Памятка по типам

Тип	Что оставляет	Когда нужен аналитику
`INNER JOIN`	только совпавшие пары	«выручка по оплаченным заказам»
`LEFT JOIN`	все левые + совпавшие правые	«все клиенты и их заказы, даже нулевые»
`RIGHT JOIN`	зеркало LEFT	почти никогда — переворачивают таблицы и пишут LEFT
`FULL OUTER JOIN`	всё с обеих сторон	сверка двух источников: что есть тут и нет там
`CROSS JOIN`	все пары со всеми	календарь × товары для «нулевых» дней

Вопрос 2. Вы написали LEFT JOIN, но клиенты без заказов всё равно пропали. Почему?

Классическая ловушка уровня middle. Ответ: условие про правую таблицу ушло в WHERE вместо ON.

-- LEFT JOIN превратился в INNER: WHERE отбрасывает строки с NULL
SELECT c.name, o.amount
FROM clients c
LEFT JOIN orders o ON o.client_id = c.id
WHERE o.amount > 500;

-- правильно: фильтр правой таблицы живёт в ON
SELECT c.name, o.amount
FROM clients c
LEFT JOIN orders o ON o.client_id = c.id AND o.amount > 500;

Логика простая: ON отрабатывает во время соединения, WHERE — после него. Строка «Вера, NULL» уже собрана, а потом WHERE o.amount > 500 её выбрасывает, потому что сравнение NULL > 500 не истинно. Правило: любое условие на правую таблицу в LEFT JOIN пишем в ON, иначе JOIN схлопывается в INNER.

Вопрос 3. Почему после JOIN выручка выросла в два раза?

Потому что соединение по неуникальному ключу размножает строки. У Анны два заказа, значит строка Анны в результате появится дважды. Если после такого JOIN сложить, например, её годовой лимит или сумму по клиенту — она удвоится.

Отсюда же вытекает главный подвох с COUNT:

CREATE TABLE clients (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, client_id INTEGER, amount INTEGER);
INSERT INTO clients VALUES (1,'Анна'),(2,'Борис'),(3,'Вера'),(4,'Глеб');
INSERT INTO orders  VALUES (10,1,1500),(11,1,700),(12,2,300);

SELECT c.name,
       COUNT(*)    AS neverno,
       COUNT(o.id) AS verno
FROM clients c
LEFT JOIN orders o ON o.client_id = c.id
GROUP BY c.id, c.name
ORDER BY c.name;

Вывод:

Анна   2  2
Борис  1  1
Вера   1  0
Глеб   1  0

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

Типичные ошибки кандидатов

Объясняют JOIN только кругами Эйлера и не могут привести пример на данных. Круги — иллюстрация, а не ответ.
Говорят «LEFT JOIN берёт всё из левой таблицы» и забывают, что при дублях в правой таблице левые строки размножаются.
Путают ON и WHERE и не замечают, что LEFT JOIN стал INNER.
Используют COUNT(*) после LEFT JOIN и получают единицы вместо нулей.
Пишут RIGHT JOIN — формально верно, но в команде это читается тяжелее, и вас попросят переписать.

Как ответить кратко

«INNER JOIN оставляет только строки, для которых нашлась пара по условию ON. LEFT JOIN оставляет все строки левой таблицы, а для непарных подставляет NULL в поля правой. На практике это выбор между "только клиенты с заказами" и "все клиенты, включая нулевых". Две ловушки: фильтр по правой таблице надо ставить в ON, иначе LEFT схлопнется в INNER; и после LEFT JOIN считать нужно COUNT(o.id), а не COUNT(*), иначе клиенты без заказов получат единицу вместо нуля».