Краткий ответ

При подсчёте дочерних строк после LEFT JOIN нужно использовать COUNT по ненулевому столбцу дочерней таблицы, например её идентификатору, а не COUNT(*). COUNT(*) считает саму строку результата соединения, включая искусственно добавленную строку с NULL для клиента без заказов.

Исторический контекст

Агрегация появилась как способ получать сводные показатели по отношениям, а внешнее соединение — сохранять строки основной таблицы, даже если соответствий в присоединяемой таблице нет. При совместном использовании этих механизмов SQL должен отличать реальную дочернюю запись от строки, созданной для сохранения родительской записи.

Именно поэтому COUNT(*) и COUNT(столбец) имеют разную семантику: первый считает строки, а второй — только ненулевые значения указанного выражения.

Постановка проблемы

Предположим, нужно вывести всех клиентов и количество их заказов, включая клиентов с нулём заказов. После LEFT JOIN клиент без заказов всё равно присутствует в промежуточном результате, но поля заказа у такой строки имеют значение NULL.

Если применить COUNT(*), эта строка будет посчитана как один заказ. В результате нулевые значения превратятся в единицы, а итоговая статистика окажется завышенной.

Подробное решение

LEFT JOIN сохраняет каждую строку левой таблицы. Если совпадений справа нет, СУБД формирует одну результирующую строку, в которой все столбцы правой таблицы равны NULL.

COUNT(*) считает все строки группы независимо от содержимого столбцов. Поэтому для клиента без заказов он возвращает 1. В отличие от него, COUNT(orders.id) считает только ненулевые значения orders.id, то есть только реально найденные заказы.

SELECT
    c.id,
    COUNT(o.id) AS orders_count
FROM customers AS c
LEFT JOIN orders AS o
    ON o.customer_id = c.id
GROUP BY c.id;

В этом примере строка без заказа остаётся в результате соединения, но o.id равен NULL и не учитывается агрегатом. Для клиента с несколькими заказами соединение создаёт несколько строк, поэтому COUNT(o.id) возвращает их фактическое количество.

Важно выбирать столбец, который гарантированно не равен NULL у существующей дочерней записи, обычно это первичный ключ. Если считать nullable-столбец, реальная запись с NULL в нём ошибочно не попадёт в результат.

Если соединение с дочерней таблицей дополнительно размножает строки из-за другого отношения, одного COUNT(o.id) может быть недостаточно: понадобится предварительная агрегация или COUNT(DISTINCT o.id). DISTINCT устраняет повторное учитывание идентификаторов, но может быть дороже и скрывать ошибочную кардинальность соединения.

Ситуация из практики

В отчёте нужно показать количество обращений каждого клиента за месяц, включая клиентов, которые не обращались. Аналитик использовал LEFT JOIN и COUNT(*); у клиентов без обращений отображалась единица.

Вариант с INNER JOIN убрал ошибочные единицы, но одновременно исключил клиентов без обращений. Вариант с COUNT(DISTINCT обращение.id) корректен при возможном размножении строк, но требует дополнительной обработки и может увеличить стоимость запроса.

Выбранное решение — LEFT JOIN и COUNT по обязательному идентификатору обращения. Оно сохраняет всех клиентов и даёт ноль для отсутствующих обращений. Если в схеме есть дополнительные соединения, обращения сначала агрегируют до одного ряда на клиента либо применяют COUNT(DISTINCT ...) после проверки плана выполнения.

Что кандидаты часто упускают

Можно ли заменить COUNT(o.id) на SUM(1) после LEFT JOIN?

Нет, без дополнительной логики это приведёт к той же ошибке, что и COUNT(*): строка клиента без заказа всё равно будет просуммирована. Для получения нуля пришлось бы суммировать условное выражение, которое даёт единицу только при наличии заказа, но такой вариант менее прямолинеен, чем COUNT по обязательному идентификатору.
Почему COUNT(o.status) может вернуть меньше заказов, чем COUNT(o.id)?

COUNT игнорирует NULL. Если у существующего заказа status допускает NULL, такой заказ не будет посчитан через COUNT(o.status). Для подсчёта строк нужно выбирать столбец, гарантированно заполненный для каждой записи, обычно первичный ключ.
Когда COUNT(DISTINCT o.id) необходим вместо COUNT(o.id)?

Он нужен, если последующие соединения создают несколько строк для одного заказа, например заказ соединяется с несколькими позициями или тегами. COUNT(o.id) посчитает заказ столько раз, сколько строк породило соединение, а COUNT(DISTINCT o.id) — один раз. Однако это не всегда лучший способ исправления: предварительная агрегация дочерних таблиц может быть понятнее и эффективнее.