COALESCE в SQL: замена NULL, NULLIF и деление на ноль

Коротко: что такое NULL и что делает COALESCE

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

SELECT
  COALESCE(country, 'не указана') AS country_label,
  COUNT(*) AS users
FROM users
GROUP BY COALESCE(country, 'не указана')
ORDER BY users DESC;

-- RU 2715, KZ 912, AM 517, BY 414, не указана 55

• Проверка на пропуск — только IS NULL и IS NOT NULL. Условие country = NULL не находит ни одной строки.
• Сравнение с NULL даёт «неизвестно», и WHERE такие строки отбрасывает.
• SUM, AVG, MIN, MAX и COUNT(столбец) пропускают NULL, COUNT(*) считает все строки. Поэтому AVG(x) и AVG(COALESCE(x, 0)) — две разные метрики.
• x / NULLIF(y, 0) возвращает NULL там, где иначе было бы деление на ноль.
• Положение NULL при сортировке зависит от базы. В PostgreSQL и DuckDB пишите NULLS LAST явно.

COALESCE: синтаксис, порядок аргументов и типы

COALESCE читает аргументы слева направо и останавливается на первом заполненном: COALESCE(NULL, NULL, 'c', 'd') возвращает 'c'. Так строят цепочки с приоритетом источников, а замену ставят последней. Если перепутать порядок и написать COALESCE('не указана', country), метку получат все 4 613 пользователей вместо 55: первый аргумент никогда не пуст.

Аргументы должны приводиться к одному типу. COALESCE(country, 0) не выполняется: PostgreSQL отвечает COALESCE types text and integer cannot be matched, DuckDB — Cannot mix values of type VARCHAR and INTEGER_LITERAL in COALESCE operator. На текстовой замене для числа движки расходятся. COALESCE(amount, 'нет') PostgreSQL отклоняет сразу: invalid input syntax for type numeric: "нет". DuckDB выполняет запрос, пока замена не понадобилась ни в одной строке результата, и останавливается с Conversion Error на первой строке с пропуском. Тип результата при этом числовой, так что текст в нём не появится в любом случае. Переносимый вариант — явно привести столбец к тексту: COALESCE(CAST(amount AS VARCHAR), 'нет').

У подстановки есть цена: замена попадает в отчёт как настоящее значение. С меткой «не указана» COUNT(DISTINCT …) насчитает пять стран вместо четырёх. А COALESCE(country, 'RU') — «по умолчанию считаем Россией» — поднимет долю России с 58,9% до 60,0%, и отличить настоящих российских пользователей от приписанных будет уже нельзя. Чем заменять пропуск, зависит от того, что он означает; три случая собраны в таблице.

Когда пропуску нужна отдельная ветка логики, пишут CASE WHEN country IS NULL THEN … END. COALESCE — короткая запись частного случая: CASE WHEN a IS NOT NULL THEN a ELSE b END. Сегменты, порядок веток и ELSE — в статье про CASE WHEN.

SELECT
  COALESCE(NULL, NULL, 'c', 'd') AS first_not_null,
  COALESCE(NULL, NULL) IS NULL   AS all_null,
  SUM(CASE WHEN COALESCE(country, 'не указана') = 'не указана'
           THEN 1 ELSE 0 END)    AS label_last,
  SUM(CASE WHEN COALESCE('не указана', country) = 'не указана'
           THEN 1 ELSE 0 END)    AS label_first
FROM users;

-- c | true | 55 | 4613

Почему NULL не равен NULL и как WHERE и NOT IN теряют строки

Сравнение в SQL возвращает один из трёх ответов: TRUE, FALSE или NULL, то есть «неизвестно». Если неизвестен хотя бы один операнд, неизвестен и результат: NULL = NULL, NULL <> NULL и country = NULL дают NULL. WHERE оставляет только строки, где условие истинно, так что «неизвестно» для него равносильно «нет».

На базе это выглядит так. Из России 2 715 пользователей, условие country <> 'RU' возвращает 1 843. В сумме 4 558, а пользователей 4 613: 55 строк не попали ни в одну половину. Отчёт «Россия и остальные» выглядит цельным, и недостачу видно только при сверке с общим числом.

Вернуть эти строки можно двумя способами: дописать OR country IS NULL или взять IS DISTINCT FROM — сравнение, для которого NULL считается обычным значением. country IS DISTINCT FROM 'RU' возвращает 1 898 строк. Оператор работает в PostgreSQL и DuckDB. В MySQL его нет, но есть <=> — равенство, для которого NULL равен NULL (по документации MySQL 8.4 это IS NOT DISTINCT FROM); то же условие там пишут как NOT (country <=> 'RU'). Как неизвестное значение проходит через AND, OR и NOT, разобрано в статье про операторы сравнения и логику.

То же правило ломает NOT IN. Тариф team в июне купили 18 человек, двое из них без страны. Вопрос «сколько пользователей живёт в странах, где team в июне не купил никто» через country NOT IN (страны покупателей) возвращает 0 строк, хотя в Беларуси 414 пользователей и ни одной такой покупки. В списке оказался NULL, условие развернулось в country <> 'RU' AND country <> 'KZ' AND country <> 'AM' AND country <> NULL, и последнее сравнение не даёт цепочке стать истинной ни для одной строки.

Исправлений два. Фильтр WHERE country IS NOT NULL внутри подзапроса даёт 414. NOT EXISTS даёт 469: к Беларуси добавились 55 пользователей без страны, для которых совпадения в списке тоже не нашлось. Какое число нужно, зависит от того, считать ли неизвестную страну страной без покупок. Механика и планы запросов — в статье про EXISTS и IN.

SELECT
  SUM(CASE WHEN country = 'RU'  THEN 1 ELSE 0 END) AS is_ru,
  SUM(CASE WHEN country <> 'RU' THEN 1 ELSE 0 END) AS not_ru,
  SUM(CASE WHEN country = NULL  THEN 1 ELSE 0 END) AS equals_null,
  SUM(CASE WHEN country IS NULL THEN 1 ELSE 0 END) AS is_null,
  SUM(CASE WHEN country IS DISTINCT FROM 'RU' THEN 1 ELSE 0 END) AS distinct_from_ru
FROM users;

-- 2715 | 1843 | 0 | 55 | 1898

WITH team_june AS (
  SELECT u.country
  FROM payments p
  JOIN users u ON u.user_id = p.user_id
  WHERE p.plan = 'team'
    AND p.paid_at < DATE '2026-07-01'
)
SELECT
  (SELECT COUNT(*) FROM users
    WHERE country NOT IN (SELECT country FROM team_june)) AS not_in,
  (SELECT COUNT(*) FROM users
    WHERE country NOT IN (SELECT country FROM team_june
                          WHERE country IS NOT NULL)) AS not_in_filtered,
  (SELECT COUNT(*) FROM users u
    WHERE NOT EXISTS (SELECT 1 FROM team_june t
                      WHERE t.country = u.country)) AS not_exists;

-- 0 | 414 | 469

Как COUNT, SUM и AVG считают NULL

COUNT(*) считает строки, COUNT(country) — строки с заполненной страной: 4 613 и 4 558. Разница равна числу пропусков, и это самая короткая проверка столбца. COUNT(DISTINCT country) возвращает 4: NULL в число разных значений не входит.

SUM, AVG, MIN и MAX пропуски обходят. Сумме это не вредит, а у среднего и у долей меняется знаменатель. Доля России — 58,9% от всех пользователей и 59,6% от тех, у кого страна известна. Оба числа верны; в отчёте должно быть сказано, какое из них показано.

Отдельный случай — пустой набор строк. Платный поиск запустили 1 июля, и июньских оплат у канала нет. COUNT(*) по ним возвращает 0, а SUM(amount) — NULL. В дашборде это пустая ячейка, и любое выражение с ней, например «выручка минус расходы», тоже станет пустым. Здесь COALESCE(SUM(amount), 0) уместен: оплат действительно не было.

С GROUP BY пустой набор выглядит иначе: строки для группы просто нет, и COALESCE подставить некуда. Тот же запрос с GROUP BY u.channel возвращает ноль строк. Чтобы в отчёте стоял ноль, начинайте со списка групп и присоединяйте данные через LEFT JOIN.

SELECT
  COUNT(*)                AS all_rows,
  COUNT(country)          AS known_country,
  COUNT(DISTINCT country) AS countries,
  ROUND(100.0 * SUM(CASE WHEN country = 'RU' THEN 1 ELSE 0 END)
        / COUNT(*), 1)       AS ru_of_all,
  ROUND(100.0 * SUM(CASE WHEN country = 'RU' THEN 1 ELSE 0 END)
        / COUNT(country), 1) AS ru_of_known
FROM users;

-- 4613 | 4558 | 4 | 58.9 | 59.6

SELECT
  COUNT(*)                   AS payments,
  SUM(p.amount)              AS revenue,
  COALESCE(SUM(p.amount), 0) AS revenue_or_zero
FROM payments p
JOIN users u ON u.user_id = p.user_id
WHERE u.channel = 'paid_search'
  AND p.paid_at < DATE '2026-07-01';

-- 0 | NULL | 0

Что оставляет LEFT JOIN и когда нужен COALESCE(…, 0)

LEFT JOIN сохраняет всех пользователей, а там, где оплат не нашлось, ставит NULL. Посчитаем выручку на пользователя: соединяем users с payments и складываем суммы. У 912 человек получается число, у 3 701 — NULL.

Дальше одна подстановка решает, какую метрику вы считаете. AVG(revenue) обходит NULL и делит 30 639 долларов на 912 платящих: 33,60. AVG(COALESCE(revenue, 0)) превращает пропуски в нули и делит ту же сумму на 4 613 пользователей: 6,64. Первое — выручка на платящего, ARPPU, второе — на зарегистрированного, ARPU. Сумма при этом не меняется, так что сверка выручки подмену не поймает.

Правило выбора: ноль подставляют, когда пропуск означает «события не было». Нет оплат — выручка ноль, это факт. Если же NULL стоит в самой строке оплаты, сумма неизвестна, и ноль занизит среднее. Отличить одно от другого помогает счётчик по ключу: COUNT(p.payment_id) = 0 — оплат нет, счётчик больше нуля при пустой сумме — оплата есть, сумма не записана. В учебной базе второго случая нет: amount заполнен во всех 1 251 оплатах.

WITH user_revenue AS (
  SELECT u.user_id, SUM(p.amount) AS revenue
  FROM users u
  LEFT JOIN payments p ON p.user_id = u.user_id
  GROUP BY u.user_id
)
SELECT
  COUNT(*)                            AS users,
  COUNT(revenue)                      AS payers,
  COUNT(*) - COUNT(revenue)           AS no_payments,
  SUM(revenue)                        AS revenue,
  ROUND(AVG(revenue), 2)              AS avg_per_payer,
  ROUND(AVG(COALESCE(revenue, 0)), 2) AS avg_per_user
FROM user_revenue;

-- 4613 | 912 | 3701 | 30639 | 33.60 | 6.64

NULLIF: деление на ноль и пустые строки

NULLIF(a, b) возвращает NULL, если аргументы равны, иначе — первый аргумент. Главное применение — знаменатель: x / NULLIF(y, 0). Когда y равен нулю, деление идёт на NULL и даёт NULL.

Пример: доля мобильных среди регистраций из платного поиска по месяцам. В июне канала ещё не было, знаменатель равен нулю. Без NULLIF PostgreSQL останавливает весь запрос с division by zero, и вы не получаете даже июль с августом. DuckDB запрос выполняет и ставит в июнь NaN, потому что там 0 / 0; при ненулевом числителе получилась бы Infinity. Так DuckDB ведёт себя по умолчанию с версии 1.1; с SET ieee_floating_point_ops = false деление на ноль возвращает NULL. NaN и Infinity проходят в отчёт и ломают средние по столбцу. С NULLIF движки возвращают одно и то же: в июне NULL, в июле 65,1%, в августе 66,8%.

NULL здесь — честный ответ: доли нет, потому что делить не на что. Оборачивать результат в COALESCE(…, 0) не стоит: «ноль процентов мобильных» и «канала не было» — разные утверждения. Выбор знаменателя и целочисленное деление разобраны в статье про проценты и доли.

Второе применение NULLIF — пустые строки. В PostgreSQL и DuckDB пустая строка — обычное значение длины ноль: '' IS NULL ложно, а COALESCE('', 'не указана') вернёт пустую строку. В выгрузках из CSV и веб-форм пустые строки встречаются постоянно, часто вперемешку с пробелами; в учебной базе их нет. Чтобы COALESCE сработал, их сначала превращают в NULL: NULLIF(TRIM(x), ''). Во втором запросе четыре варианта одного поля: без нормализации метку получает только настоящий NULL, с ней — три строки из четырёх.

Исключение — Oracle: по документации (SQL Language Reference, раздел Nulls) строка нулевой длины там считается NULL, и сама Oracle советует на это не полагаться.

SELECT
  EXTRACT(month FROM signup_date) AS signup_month,
  SUM(CASE WHEN channel = 'paid_search' THEN 1 ELSE 0 END) AS paid_search,
  ROUND(
    100.0 * SUM(CASE WHEN channel = 'paid_search' AND device = 'mobile'
                     THEN 1 ELSE 0 END)
      / NULLIF(SUM(CASE WHEN channel = 'paid_search' THEN 1 ELSE 0 END), 0),
    1
  ) AS mobile_pct
FROM users
GROUP BY EXTRACT(month FROM signup_date)
ORDER BY signup_month;

-- 6 | 0 | NULL
-- 7 | 482 | 65.1
-- 8 | 533 | 66.8

SELECT
  id,
  LENGTH(raw)                                   AS len,
  raw IS NULL                                   AS is_null,
  COALESCE(raw, 'не указана')                   AS coalesce_only,
  COALESCE(NULLIF(TRIM(raw), ''), 'не указана') AS normalized
FROM (VALUES (1, 'RU'), (2, ''), (3, '  '), (4, NULL)) AS t(id, raw)
ORDER BY id;

-- 1 | 2    | false | RU         | RU
-- 2 | 0    | false |            | не указана
-- 3 | 2    | false |            | не указана
-- 4 | NULL | true  | не указана | не указана

NULL в сортировке, группировке и склейке строк

В GROUP BY и DISTINCT все NULL собираются в одну группу, хотя между собой они «не равны». Группировка по стране даёт пять строк, пятая — 55 пользователей без страны.

С сортировкой движки расходятся. PostgreSQL считает NULL больше любого значения: при ASC пропуски в конце, при DESC — в начале. DuckDB ставит NULL в конец при любом направлении. Возьмите трёх пользователей с наибольшей выручкой через ORDER BY revenue DESC, user_id LIMIT 3. DuckDB вернёт троих с выручкой 117 долларов: 320, 444 и 532. PostgreSQL вернёт пользователей 1, 2 и 4 с пустой выручкой — тех, кто не платил вовсе.

Положение пропусков пишите явно: ORDER BY revenue DESC NULLS LAST. MySQL ведёт себя наоборот: по документации 8.4 NULL стоит первым при ASC и последним при DESC. NULLS LAST в синтаксисе ORDER BY там не описан, принятый приём — сортировать сначала по признаку пропуска: ORDER BY revenue IS NULL, revenue DESC. Таблица сочетаний направления и NULLS для PostgreSQL и DuckDB — в статье про ORDER BY и LIMIT.

Арифметика с NULL даёт NULL: NULL + 1, NULL * 0. Оператор склейки || ведёт себя так же: метка channel || ' / ' || country пуста у 55 пользователей, заполнено 4 558 строк из 4 613. Функция CONCAT в PostgreSQL и DuckDB пропуск обходит и возвращает «partner / » с оборванным хвостом; в MySQL по документации CONCAT возвращает NULL, если хотя бы один аргумент NULL. Предсказуемее подставить замену самому. Остальное о склейке — в статье про строковые функции.

Оконные функции создают NULL сами: LAG на первой строке окна возвращает NULL, потому что предыдущей строки нет. Третий аргумент в LAG(x, 1, 0) заменит его нулём, и у июня, с которого начинается выгрузка, появится «рост на 3 983 доллара» — вся июньская выручка. Подробности — в статье про LAG и LEAD.

WITH user_revenue AS (
  SELECT u.user_id, SUM(p.amount) AS revenue
  FROM users u
  LEFT JOIN payments p ON p.user_id = u.user_id
  GROUP BY u.user_id
)
SELECT user_id, revenue
FROM user_revenue
ORDER BY revenue DESC NULLS LAST, user_id
LIMIT 3;

-- 320 | 117
-- 444 | 117
-- 532 | 117

SELECT
  COUNT(*)                                   AS users,
  COUNT(channel || ' / ' || country)         AS glued_pipes,
  COUNT(CONCAT(channel, ' / ', country))     AS glued_concat,
  COUNT(channel || ' / ' || COALESCE(country, 'не указана')) AS glued_coalesce
FROM users;

-- 4613 | 4558 | 4613 | 4613

COALESCE, IFNULL, ISNULL и NVL: что работает в какой СУБД

COALESCE входит в стандарт SQL и работает везде. У двухаргументных замен названия в каждой базе свои, и запрос с ними не переносится. IFNULL(country, 'x') в PostgreSQL заканчивается ошибкой function ifnull(text, unknown) does not exist, в DuckDB выполняется. NVL не знают ни PostgreSQL, ни DuckDB.

То же в pandas: isna, fillna и NaN

В pandas пропуск — это NaN (в старых строковых столбцах — ещё и None), у дат — NaT. Проверяют его через isna(), заменяют через fillna().

В SQL NULL = NULL неизвестно. В pandas np.nan == np.nan — ложь, а np.nan != np.nan — истина, третьего ответа нет. Отсюда первое отличие, и оно меняет число: country != 'RU' для пропуска истинно, фильтр возвращает 1 898 строк, а SQL — 1 843. Исключение — nullable-типы (string, Int64, boolean): там сравнение с пропуском даёт <NA>, и тот же фильтр вернёт 1 843, как в SQL.

groupby по умолчанию выбрасывает группу с пропуском: четыре группы вместо пяти, пока не написан dropna=False. count() считает заполненные значения, size — все строки: 4 558 и 4 613, как COUNT(country) и COUNT(*). Сумма из одних пропусков в pandas равна 0, в SQL — NULL.

Блок выполнен в pandas 2.3 и 3.0, вывод одинаковый. В 3.0 строки по умолчанию получают тип str, и None в нём превращается в NaN; в 2.x в столбце object остаётся None. isna() видит оба варианта. Очистка выгрузок с пропусками — в статье про pandas и пропуски.

import numpy as np
import pandas as pd

# страна 4 613 пользователей учебной базы: значение -> число строк
counts = {'RU': 2715, 'KZ': 912, 'AM': 517, 'BY': 414, None: 55}
users = pd.DataFrame({'country': [c for c, n in counts.items() for _ in range(n)]})
country = users['country']

print(country.isna().sum())                           # 55
print(np.nan == np.nan, np.nan != np.nan)             # False True
print((country != 'RU').sum())                        # 1898 — в SQL было 1843
print(((country != 'RU') & country.notna()).sum())    # 1843
print((country.astype('string') != 'RU').sum())       # 1843 — nullable-тип, как в SQL
print(country.count(), country.size)                  # 4558 4613
print(len(users.groupby('country').size()))                # 4
print(len(users.groupby('country', dropna=False).size()))  # 5
print((country.fillna('не указана') == 'не указана').sum())  # 55
print(pd.Series([np.nan]).sum())                      # 0.0

Где NULL и подстановка портят отчёт или замедляют запрос

Пять первых пунктов списка — итог разделов выше. Шестой — отдельный случай, его показывает запрос перед списком.

Ещё одна ловушка число не меняет, но замедляет запрос. COALESCE(country, 'BY') = 'BY' в WHERE или JOIN — функция над столбцом: индекс по country для неё не используется, а планировщик PostgreSQL ошибается в оценке числа строк — 23 вместо 469 на копии учебной таблицы. Запишите country = 'BY' OR country IS NULL: PostgreSQL прочитает индекс дважды и объединит результаты. Для значения, которое есть в большей части строк, индекс не поможет в любой записи. Как читать такие планы, показано в статье про индексы.

SELECT
  (SELECT COUNT(*) FROM users a
     JOIN users b ON a.country = b.country
    WHERE a.country IS NULL) AS plain_equality,
  (SELECT COUNT(*) FROM users a
     JOIN users b ON COALESCE(a.country, '') = COALESCE(b.country, '')
    WHERE a.country IS NULL) AS coalesce_key,
  (SELECT COUNT(*) FROM users a
     JOIN users b ON a.country IS NOT DISTINCT FROM b.country
    WHERE a.country IS NULL) AS not_distinct;

-- 0 | 3025 | 3025

• AVG(COALESCE(revenue, 0)) вместо AVG(revenue): 6,64 доллара вместо 33,60.
• WHERE country <> 'RU': 1 843 строки вместо 1 898.
• NOT IN по подзапросу с NULL: 0 строк вместо 414.
• COALESCE(country, 'RU'): доля России 60,0% вместо 58,9%.
• ORDER BY revenue DESC LIMIT 3 без NULLS LAST в PostgreSQL: трое неплативших.
• COALESCE в ключе соединения. Если соединить users с собой по стране через ON COALESCE(a.country, '') = COALESCE(b.country, ''), каждый из 55 пользователей без страны получит в пару всех 55, включая себя: 3 025 строк. Одинаковая заглушка не делает неизвестные значения одним значением. Если пустые ключи нужно сопоставить намеренно, пишите a.country IS NOT DISTINCT FROM b.country.

Частые вопросы

Практика на учебной базе и что читать дальше

Две задачи на той же базе. Первая: сколько строк вернёт WHERE country NOT IN ('KZ', 'AM', 'BY') и сколько пользователей, у которых в столбце не записана ни одна из этих трёх стран, считая пропуски? Ответ: 2 715 и 2 770. Разница — 55 строк, про которые NOT IN ответить не может. Вторая: для 55 пользователей без страны посчитайте среднюю выручку на платящего и на пользователя. Ответ: платили семеро, 43,00 и 5,47 доллара.

Перед тем как отдать отчёт, проверьте три вещи: сходится ли сумма групп с COUNT(*), какой знаменатель у каждого среднего и где в сортировке стоят пропуски.