LEFT JOIN и NULL: находим то, чего не хватает

Сохраняй все строки таблицы с помощью LEFT JOIN, выявляй пропуски через IS NULL и заменяй их с COALESCE.

8 мин · 3 вопросов

INNER JOIN из прошлого урока прячет клиентов без заказов. А ведь самый частый бизнес-вопрос звучит именно так: кого не хватает? Неактивные клиенты, товары, которые ни разу не продали, счета без оплаты... Запусти этот скрипт в sqliteonline.com или sqlite3: он добавляет четвёртого клиента без заказов.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  amount REAL NOT NULL
);

INSERT INTO customers (id, name, city) VALUES
  (1, 'Alice', 'Lyon'),
  (2, 'Karim', 'Paris'),
  (3, 'Mina', 'Nantes'),
  (4, 'Paulo', 'Lille');

INSERT INTO orders (id, customer_id, amount)
VALUES
  (1, 1, 49.90),
  (2, 1, 15.00),
  (3, 2, 120.50);

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

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

Чтобы выделить то, чего не хватает, отфильтруй по этим значениям NULL: это паттерн анти-соединения, обязательный рефлекс в анализе данных. Проверяемый столбец должен быть из правой таблицы и никогда не быть NULL естественным образом - отсюда выбор её первичного ключа o.id.

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

Последний инструмент: COALESCE(a, b) возвращает a, если это не NULL, иначе b. В связке с LEFT JOIN и агрегацией он превращает пропуски в пригодные нули в отчёте или счёте.

SELECT c.name,
  COALESCE(SUM(o.amount), 0) AS total_spent
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.id
GROUP BY c.id
ORDER BY total_spent DESC;

Проверка знаний

Убедись, что запомнил ключевые моменты этого урока.

1. Какая комбинация выводит клиентов, которые ни разу не заказывали?
   • INNER JOIN, затем WHERE o.id IS NULL
   • LEFT JOIN, затем WHERE o.id IS NULL
   • LEFT JOIN, затем WHERE o.id = NULL
2. Что возвращает условие amount = NULL?
   • Истину для строк, где amount равен NULL
   • Никогда не истину: сравнение остаётся неопределённым
   • Синтаксическую ошибку
3. Что возвращает COALESCE(SUM(o.amount), 0) для клиента без заказов?
   • 0
   • NULL
   • Клиент исключается из результата
   • Ошибку агрегации