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

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 несколько подходящих заказов дадут несколько строк клиента: перед расчётом метрик проверяйте кардинальность соединения.

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