LEFT JOIN в SQL: примеры, NULL и поиск строк без пары

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

Обзор всех пяти видов соединений — в статье про виды JOIN в SQL. Здесь разбор одного, с ошибками, которые он порождает.

Таблицы те же, что и там:

У Веры заказов нет — на ней всё и держится.

Базовый синтаксис

SELECT u.name, o.amount
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;

LEFT JOIN и LEFT OUTER JOIN — одно и то же, OUTER можно опустить. Слева стоит таблица, которую нельзя терять; порядок здесь смысловой, а не косметический: поменяешь таблицы местами — получишь другой результат.

Как найти строки без пары

Главное применение LEFT JOIN — не «дописать данные справа», а найти тех, у кого справа пусто. Клиенты, которые ни разу не заказывали:

SELECT u.name
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;

Результат — одна строка, «Вера». Приём называют антиджойном: LEFT JOIN даёт Вере строку с NULL вместо заказа, а WHERE o.id IS NULL оставляет только такие строки.

То, что Вера вообще существует в базе без единого заказа, — заслуга разбиения таблиц. В одной плоской таблице заказов её негде было бы записать, и этот случай разобран в статье про нормализацию как аномалия вставки.

Важная деталь, на которой спотыкаются: проверять нужно колонку, которая в правой таблице никогда не бывает пустой — первичный ключ или колонку с NOT NULL. Если написать WHERE o.amount IS NULL, в выдачу попадут и клиенты без заказов, и клиенты с заказом, у которого не проставлена сумма. Два разных факта склеятся в один, а запрос при этом отработает без ошибки.

Фильтр в WHERE ломает LEFT JOIN

Самая частая ошибка с LEFT JOIN, и вылезает она не сообщением об ошибке, а тихо неверными данными. Запрос «все клиенты и их заказы дороже 1000»:

SELECT u.name, o.amount
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.amount > 1000;

Вера исчезла. LEFT JOIN честно выдал ей строку с NULL в o.amount, но WHERE выполняется после соединения, а сравнение NULL > 1000 не истинно и не ложно — оно UNKNOWN, и строка отбрасывается. Соединение фактически выродилось в INNER JOIN.

Если фильтр относится к правой таблице, а левую терять нельзя, условие переносится в ON:

SELECT u.name, o.amount
FROM users u
LEFT JOIN orders o ON o.user_id = u.id AND o.amount > 1000;

Разница между двумя запросами укладывается в одну фразу: ON решает, что присоединять, WHERE решает, что оставить в результате. Условие в ON отсекает неподходящие заказы до соединения, поэтому Вера остаётся со своим NULL. Условие в WHERE режет уже готовый результат.

При этом WHERE не всегда враг. Условие по левой таблице (WHERE u.name LIKE 'А%') работает как ожидается, и WHERE o.id IS NULL из антиджойна — тоже WHERE, причём осознанный: там ловят как раз строки без пары.

COUNT(*) считает пустые строки

Соединение с агрегатом — вторая ловушка того же корня. Сколько заказов у каждого клиента:

SELECT u.name, count(*) AS orders_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.name;

У Веры единица, хотя заказов у неё ноль. count(*) считает строки, а строка у Веры есть — та самая, с NULL вместо заказа. Считать нужно конкретную колонку правой таблицы, потому что count(колонка) пропускает NULL:

SELECT u.name, count(o.id) AS orders_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.name;

Теперь у Веры честный ноль. Та же логика у sum: сумма по пустому набору вернёт NULL, а не 0, — оборачивай в coalesce(sum(o.amount), 0), если ноль важнее пустоты.

Несколько LEFT JOIN подряд

Соединения выстраиваются цепочкой, результат предыдущего становится левой стороной для следующего:

SELECT u.name, o.amount, p.paid_at
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
LEFT JOIN payments p ON p.order_id = o.id;

Ловушка здесь одна, зато обидная: один INNER JOIN в конце цепочки обнуляет все предыдущие LEFT. Заменишь второе соединение на обычный JOIN — и клиенты без заказов исчезнут, потому что у их NULL-строки нет платежа, а INNER строки без пары не пропускает. Начал цепочку с LEFT — держи LEFT до конца.

Второй эффект цепочки — размножение строк. У клиента два заказа, у каждого заказа три платежа: на выходе шесть строк на клиента. sum(o.amount) по такому набору посчитает каждый заказ трижды. Лечится агрегированием в подзапросе до соединения — или count(DISTINCT o.id), если нужен только счётчик.

LEFT JOIN, NOT IN и NOT EXISTS

Ту же задачу «кто без заказов» решают тремя способами, и они не равнозначны.

-- 1. Антиджойн
SELECT u.name FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;

-- 2. NOT EXISTS
SELECT u.name FROM users u
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

-- 3. NOT IN — опасный
SELECT u.name FROM users u
WHERE u.id NOT IN (SELECT user_id FROM orders);

Первые два дают «Веру» и ведут себя предсказуемо. Третий скрывает мину: если в orders.user_id найдётся хоть один NULL, запрос вернёт пустой результат — вообще ни одной строки. Причина та же, что и с фильтром: NOT IN разворачивается в цепочку сравнений u.id <> NULL, каждое из которых UNKNOWN, и условие никогда не становится истинным.

Отсюда практическое правило: NOT IN с подзапросом безопасен, только если колонка объявлена NOT NULL. Во всех остальных случаях бери NOT EXISTS или антиджойн — они на NULL не реагируют. Чем ещё различаются эти три формы и почему коррелированный подзапрос дороже, разобрано в статье про подзапросы.

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

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

Направлением, и только им. RIGHT JOIN сохраняет правую таблицу вместо левой; любой RIGHT JOIN переписывается в LEFT перестановкой таблиц. Запрос читают сверху вниз, поэтому главную таблицу принято ставить первой и писать LEFT — так меньше шансов ошибиться в цепочке из трёх соединений.

Что быстрее — LEFT JOIN или INNER JOIN

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

Почему в результате LEFT JOIN больше строк, чем в левой таблице

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

Где потренироваться

В пути «SQL для аналитиков» на Koddo эти сюжеты вынесены в отдельные задачи с автопроверкой: «Молчаливые клиенты» — тот самый антиджойн, «Июньский зачёт по всем» — условие в ON вместо WHERE, «Сверка сумм заказов» — расхождение двух источников.