Как обработать в SQL нулевые значения и избежать ошибок при подсчете уникальных значений?

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

Знаете ли вы, как агрегаты ведут себя с NULL, и умеете ли решить, считать ли пропуск отдельным значением. Следом часто спрашивают, почему цифры разошлись после LEFT JOIN, и как то же самое выглядит в pandas или Spark.

Как отвечать

Базовые правила:

• *`COUNT() и COUNT(col)**: первый считает строки, второй только строки, где col` не NULL;
• COUNT(DISTINCT col): тоже игнорирует NULL, поэтому дописывать WHERE col IS NOT NULL не нужно, результат будет тем же;
• SUM, AVG, MIN, MAX: пропускают NULL. AVG делит на число непустых значений, а SUM по одним NULL вернёт NULL, отсюда COALESCE(SUM(x), 0);
• сравнение: NULL = NULL даёт не истину, а неизвестность, при этом GROUP BY и DISTINCT собирают все NULL в одну группу. Сравнение с учётом NULL в PostgreSQL пишется через IS DISTINCT FROM.

Если пропуск нужно посчитать отдельным значением, надёжнее прибавить его явно: COUNT(DISTINCT col) + MAX(CASE WHEN col IS NULL THEN 1 ELSE 0 END). Замена через COALESCE работает, только если подставленное значение гарантированно не встречается в данных и совпадает по типу с колонкой.

Для деления, где знаменатель бывает нулём, есть x / NULLIF(y, 0): вместо ошибки получится NULL. В многоколоночном COUNT(DISTINCT a, b) в MySQL строка отбрасывается, если NULL хотя бы в одной колонке.

В Python то же поведение: df['col'].nunique() по умолчанию не считает NaN, для учёта нужен nunique(dropna=False). В Spark countDistinct тоже пропускает null.

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

Пишут COALESCE(col, 'default') и получают заниженный счёт, потому что строка default уже есть в данных и сливается с пропусками. Для числовой колонки PostgreSQL такой запрос вообще не выполнит из-за несовпадения типов.

Используют NOT IN (SELECT ...), когда подзапрос может вернуть NULL. Условие тогда не бывает истинным, и результат пустой. Безопаснее NOT EXISTS.

После LEFT JOIN считают COUNT(*) в разрезе клиента, и у клиентов без заказов выходит 1 вместо 0. Считать нужно колонку правой таблицы, например COUNT(o.id).