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

Почему LEFT JOIN возвращает NULL

LEFT JOIN сохраняет строки слева, даже если справа не нашлось пары. Этот запрос можно выполнить в SQLite без подготовки таблиц:

WITH customers(id, name) AS (VALUES (1, 'Anna'), (2, 'Boris')),
orders(id, customer_id) AS (VALUES (101, 1), (102, 1))
SELECT c.name, o.id AS order_id
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
ORDER BY c.id, o.id;

Результат: Anna | 101, Anna | 102, Boris | NULL. Два заказа дают две строки для Анны; отсутствие заказа не удаляет Бориса. INNER JOIN вернул бы только первые две строки. Ниже разбираем поиск клиентов без заказов и влияние фильтра WHERE.

В прошлом уроке 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. Только он даёт корректный результат в этой ситуации.

Фильтр в ON и WHERE меняет LEFT JOIN

У клиента A есть оплаченный заказ, у B — только отменённый, у C заказов нет. Нужно сохранить всех клиентов и показать оплаченные заказы, если они есть. Условие статуса поставим в ON, чтобы клиент без подходящей пары остался в результате.

import sqlite3
with sqlite3.connect(":memory:") as db:
    db.execute("CREATE TABLE customers (id TEXT)")
    db.execute("CREATE TABLE orders (customer TEXT, status TEXT)")
    db.executemany("INSERT INTO customers VALUES (?)", [("A",),("B",),("C",)])
    db.executemany("INSERT INTO orders VALUES (?,?)", [("A","paid"),("B","cancelled")])
    result = db.execute("SELECT c.id, o.status FROM customers c LEFT JOIN orders o ON c.id=o.customer AND o.status='paid' ORDER BY c.id").fetchall()
    print(result)
    assert result == [("A","paid"),("B",None),("C",None)]

Теперь перенесите условие статуса в WHERE и проверьте, исчезли ли B и C. Это не косметическая перестановка. Даже при правильном LEFT JOIN несколько подходящих заказов дадут несколько строк клиента: перед расчётом метрик проверяйте кардинальность соединения.

Продолжите разбор: Соединение трёх и более таблиц.