LEFT JOIN и NULL в результате соединения

В прошлом уроке INNER JOIN оставлял в результате только те строки, для которых нашлось совпадение в обеих таблицах. Клиент без заказов просто исчезал. Это нормально, когда нужны только «парные» данные, — но часто задача ровно обратная: показать всех клиентов и отметить, у кого заказов нет. Здесь в игру входит LEFT JOIN.

LEFT JOIN: синтаксис, гарантия сохранения строк левой таблицы и NULL в правой части

Синтаксис LEFT JOIN выглядит почти так же, как INNER JOIN — меняется только ключевое слово:

SELECT c.name, o.order_id, o.total_amount
FROM customers AS c
LEFT JOIN orders AS o ON c.customer_id = o.customer_id;

Предположим, в базе три клиента и два заказа — оба принадлежат Анне:

customer_id	name
1	Анна
2	Борис
3	Вера

order_id	customer_id	order_date	total_amount
101	1	2024-01-10	5000
102	1	2024-02-15	3000

Запрос вернёт четыре строки:

name	order_id	total_amount
Анна	101	5000
Анна	102	3000
Борис	NULL	NULL
Вера	NULL	NULL

Механизм такой: для каждой строки левой таблицы (customers) SQLite ищет строки в правой (orders), где выполняется условие ON. Если совпадение найдено — данные объединяются обычным образом. Если не найдено — строка из customers всё равно попадает в результат, а все столбцы из orders принимают значение NULL.

Для Анны совпадений два, поэтому она появляется в результате дважды. Для Бориса и Веры совпадений ноль — они появляются по одному разу, но order_id и total_amount у них NULL.

NULL здесь не означает, что в таблице orders испорчены данные. Это сигнал: связанной записи не существует. Именно этим NULL в правых столбцах LEFT JOIN отличается от NULL, который мог бы стоять в самой таблице orders как реально пропущенное значение поля.

Добавление слова OUTER (LEFT OUTER JOIN) синтаксически допустимо, но не меняет поведение — в SQLite эти две формы эквивалентны.

LEFT JOIN против INNER JOIN: какие строки попадают в результат

Разница между двумя видами соединения — не в синтаксисе, а в том, что происходит со строками без пары.

Возьмём тот же запрос, но с INNER JOIN:

SELECT c.name, o.order_id, o.total_amount
FROM customers AS c
INNER JOIN orders AS o ON c.customer_id = o.customer_id;

Результат:

name	order_id	total_amount
Анна	101	5000
Анна	102	3000

Бориса и Веры нет вообще. INNER JOIN работает как фильтр: строка левой таблицы попадает в результат тогда и только тогда, когда для неё нашлось хотя бы одно совпадение в правой.

LEFT JOIN снимает это ограничение для левой таблицы: каждая её строка гарантированно присутствует в результате, совпадение или нет.

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

Типичные ситуации для LEFT JOIN: список всех сотрудников с их проектами (включая тех, кто пока не назначен ни на один проект), список всех товаров с продажами (включая товары без продаж), список всех клиентов с заказами (включая клиентов без заказов).

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

IS NULL в WHERE после LEFT JOIN: поиск несовпавших строк и ловушка с = NULL

LEFT JOIN сам по себе возвращает все строки — и совпавшие, и несовпавшие. Чтобы оставить только тех клиентов, у которых заказов нет, нужно отфильтровать строки, где правая часть оказалась NULL. Делается это через WHERE с IS NULL — но важно, по какому столбцу проверять.

Правило: берите столбец из правой таблицы, который в реальных данных никогда не бывает NULL. Первичный ключ — идеальный кандидат.

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

Запрос вернёт:

name
Борис
Вера

Логика: после LEFT JOIN строки Бориса и Веры имеют o.order_id = NULL. Условие IS NULL выполняется — строки проходят фильтр. У Анны o.order_id равен 101 или 102, условие не выполняется — её строки отбрасываются.

Почему нельзя написать WHERE o.order_id = NULL?

В SQL NULL — это не значение, а маркер отсутствия значения. Любое сравнение с NULL через = возвращает не TRUE и не FALSE, а NULL. А WHERE пропускает строку только при TRUE — строки с результатом NULL отбрасываются так же, как строки с FALSE. Поэтому запрос:

SELECT c.name
FROM customers AS c
LEFT JOIN orders AS o ON c.customer_id = o.customer_id
WHERE o.order_id = NULL;  -- всегда пусто!

вернёт ноль строк — всегда, независимо от данных. Ошибки не будет, просто пустой результат, что легко принять за «заказов действительно нет в базе».

IS NULL — специальный оператор, созданный именно для проверки на отсутствие значения. Он не сравнивает, а проверяет статус: является ли значение NULL. Только он даёт корректный результат в этой ситуации.