Какой тип JOIN использовать для поиска заказов с пустым user_id и отсутствующим пользователем?

Что проверяют

Умеете ли вы искать «сирот» в данных, то есть записи со ссылкой в никуда, и понимаете ли, как ведёт себя NULL в условии соединения. Уточнение обычно такое: что вернёт INNER JOIN и почему NOT IN здесь опасен.

Как отвечать

Нужен LEFT JOIN от заказов к пользователям с фильтром по отсутствию пары:

SELECT o.* FROM orders o LEFT JOIN users u ON u.id = o.user_id WHERE u.id IS NULL;

Этот запрос закрывает оба случая сразу. Если user_id пустой, условие u.id = o.user_id даёт NULL, пары нет, и u.id в результате тоже NULL. Если user_id заполнен, но такого пользователя нет, итог тот же. Условие o.user_id IS NULL OR u.id IS NULL не ошибка, но первая половина в нём лишняя, её оставляют ради читаемости.

Для отчёта по багу полезно разделить случаи:

• пустой user_id: заказ создан без привязки, ищите ошибку в API или форме оформления;
• битая ссылка: пользователь удалён или не создался, а внешнего ключа на orders.user_id нет или он отключён.

Разметить строки можно через CASE WHEN o.user_id IS NULL THEN ... ELSE ... END в списке полей.

Альтернатива с тем же смыслом: WHERE NOT EXISTS (SELECT 1 FROM users u WHERE u.id = o.user_id). PostgreSQL обычно выполняет и её, и LEFT JOIN ... IS NULL одним и тем же anti join.

Хорошо закончить выводом тестировщика: сам факт таких строк это дефект целостности, и стоит проверить, есть ли на колонке FOREIGN KEY и NOT NULL.

Где спотыкаются

Пишут INNER JOIN. Он отбрасывает как раз те заказы, которые нужно найти, и запрос честно возвращает ноль строк.

Используют WHERE o.user_id NOT IN (SELECT id FROM users). Заказ с пустым user_id в результат не попадёт: сравнение с NULL даёт не истину, а неизвестность.

Проверяют отсутствие пары по необязательной колонке, например u.email IS NULL. Такое условие ловит и существующих пользователей без почты, и отчёт оказывается завышенным. Проверять нужно первичный ключ правой таблицы.