Куда пропали клиенты без покупок? Ловушка WHERE после LEFT JOIN

Нужно вывести всех клиентов и их оплаченные заказы. Даже если оплаченных заказов нет, клиент должен остаться в отчёте.

Допустим, у нас три клиента:

  • Анна оплатила заказ №101.

  • Борис создал заказ №102, но ещё не оплатил его.

  • У Веры заказов пока нет.

Начнём с такого запроса:

SELECT
    c.name,
    o.id AS order_id
FROM customers AS c
LEFT JOIN orders AS o
    ON o.customer_id = c.id
WHERE o.status = 'paid';

В результате останется только Анна с заказом №101. Борис и Вера исчезнут из отчёта.

Почему так получилось? LEFT JOIN сохраняет строки всех клиентов, но затем WHERE отбирает только те строки, где o.status = 'paid'. Заказ Бориса этому условию не соответствует. У Веры заказа нет, поэтому в присоединённых полях стоит NULL — такая строка тоже не проходит проверку.

Если нужны все клиенты, а из заказов — только оплаченные, условие можно перенести в ON:

SELECT
    c.name,
    o.id AS order_id
FROM customers AS c
LEFT JOIN orders AS o
    ON o.customer_id = c.id
   AND o.status = 'paid';

Теперь в результате будут:

  • Анна — заказ №101;

  • Борис — NULL вместо номера заказа;

  • Вера — NULL вместо номера заказа.

У Бориса есть неоплаченный заказ, но он не соответствует условию соединения. Поэтому из результата нельзя понять, нет ли у клиента заказов вообще или среди них просто нет оплаченных.

Ещё один нюанс: если у Анны два оплаченных заказа, запрос вернёт две строки с её именем. Для отчёта с одной строкой на клиента понадобится дополнительная обработка, например агрегация.

Вывод: условие в ON определяет, какие заказы присоединить к клиенту; условие в WHERE отбирает строки полученного результата. Для LEFT JOIN это может изменить состав клиентов в отчёте.

Уважаемые хабрчане, а как вы проверяете, что при соединении таблиц из отчёта не пропали нужные строки?