Потерянные строки: LEFT JOIN, semi и anti join

При соединении таблиц строки могут пропадать. Ошибки при этом нет, и в результате пропажу не видно: строки просто отсутствуют, а итог отчёта выглядит правдоподобно. В уроке разберём, в каких местах запроса строки теряются и как это обнаружить.

INNER JOIN отбрасывает строки без пары

В orders 8000 заказов. У 84 из них customer_id указывает на клиента, которого нет в customers: источник прислал заказ раньше, чем анкету, или клиент был удалён. Внешних ключей в хранилище нет, поэтому база это не запретила.

SELECT count(*) FROM orders o JOIN customers c ON c.customer_id = o.customer_id;   -- 7916

84 заказа на 2 293 752.95 исчезли из любого отчёта, где заказы соединяются с клиентами внутренним соединением. Разбивка выручки по городам просто не будет сходиться с общей выручкой, и найти причину без знания об этих заказах трудно.

LEFT JOIN сохраняет все строки слева, подставляя NULL вместо колонок справа:

SELECT coalesce(c.city, '(клиент не найден)') AS city, count(*) AS orders
FROM orders o
LEFT JOIN customers c ON c.customer_id = o.customer_id
GROUP BY 1;

В таком отчёте появляется строка «клиент не найден» с 322 заказами: 84 сироты плюс заказы клиентов без города. Итог сходится, и проблема видна.

Фильтр в WHERE превращает LEFT JOIN во внутренний

Классическая ошибка. Нужны все клиенты и их заказы за 2026 год, включая тех, у кого заказов не было:

SELECT c.customer_id, count(o.order_id) AS orders_2026
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.created_at >= '2026-01-01'
GROUP BY c.customer_id;

Клиентов в базе 1500, а запрос вернёт заметно меньше. У клиента без заказов в 2026 году после LEFT JOIN в колонке created_at стоит NULL, условие NULL >= '2026-01-01' даёт NULL, и WHERE выбрасывает строку. LEFT JOIN формально остался, но работает как INNER.

Условие на правую таблицу должно стоять в ON:

SELECT c.customer_id, count(o.order_id) AS orders_2026
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id AND o.created_at >= '2026-01-01'
GROUP BY c.customer_id;

Теперь все 1500 клиентов на месте, у 254 из них ноль заказов. Обратите внимание на count(o.order_id), а не count(*): у клиента без заказов одна строка с NULL, и count(*) посчитал бы её за заказ.

Правило: в ON пишется, какие строки справа подходят к строке слева. В WHERE пишется, какие строки результата оставить. Для INNER JOIN разницы нет, для LEFT JOIN она меняет ответ.

Единственный фильтр по правой таблице, который уместен в WHERE после LEFT JOIN, это IS NULL. Он оставляет как раз строки без пары.

Semi join: есть ли хоть одна строка

Клиенты, у которых был хоть один возврат. Через JOIN получится 298 строк, потому что у некоторых клиентов возвратов несколько, а клиентов всего 250. Задача не требует ничего из orders, кроме факта существования, и это признак semi join:

SELECT count(*)
FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.customer_id AND lower(o.status) = 'refunded'
);   -- 250

EXISTS останавливается на первой найденной строке, ничего не размножает и не требует DISTINCT сверху. Вариант customer_id IN (SELECT customer_id FROM orders WHERE ...) тоже работает, пока в подзапросе нет NULL.

Anti join: чего нет во второй таблице

Обратная задача встречается у инженера чаще всего: найти сирот, пропуски, незагруженное.

-- заказы без клиента
SELECT count(*) FROM orders o
WHERE NOT EXISTS (SELECT 1 FROM customers c WHERE c.customer_id = o.customer_id);   -- 84
 
-- позиции с товаром, которого нет в каталоге
SELECT count(*) FROM order_items i
WHERE NOT EXISTS (SELECT 1 FROM products p WHERE p.product_id = i.product_id);      -- 27
 
-- платежи без заказа
SELECT count(*), sum(amount) FROM payments p
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.order_id = p.order_id);           -- 25 | 144 446.35

Тот же результат даёт LEFT JOIN ... WHERE right.key IS NULL. Оба способа равноценны, планировщик обычно строит для них один и тот же план. NOT IN для этого не подходит, причина в уроке про NULL: один NULL в подзапросе, и результат пустой.

FULL JOIN для сверки двух источников

Когда нужно понять, что есть в одной таблице, чего нет в другой, и наоборот, за один проход:

SELECT count(*) FILTER (WHERE o.order_id IS NULL)     AS only_in_payments,   -- 25
       count(*) FILTER (WHERE p.order_id IS NULL)     AS only_in_orders,     -- 1369
       count(*) FILTER (WHERE o.order_id IS NOT NULL
                          AND p.order_id IS NOT NULL) AS in_both             -- 6631
FROM (SELECT DISTINCT order_id FROM orders) o
FULL JOIN (SELECT DISTINCT order_id FROM payments) p USING (order_id);

Ключи заранее сведены к уникальным через DISTINCT: если ключ повторяется, соединение повторит строки и счётчики завысятся. Такой запрос за секунду показывает картину расхождений между двумя системами, дальше остаётся разобраться с каждой группой.

NULL в ключе соединения

Ключ с NULL не соединяется ни с чем, это следствие трёхзначной логики. 46 клиентов без города выпадут из JOIN cities, и это не всегда плохо. Хуже другой вариант: если ключ NULL с обеих сторон и хочется, чтобы такие строки соединились, ON a.city = b.city этого не сделает. Понадобится ON a.city IS NOT DISTINCT FROM b.city, и стоит помнить, что индекс по такому условию не используется.

Отдельный риск: соединение по колонке с низкой уникальностью. orders.delivery_city = customers.city даёт почти два миллиона строк из 8000 заказов, потому что в каждом городе тысячи клиентов. Условие соединения обязано выражать связь между сущностями, а не совпадение атрибутов.

Практика