9 апреля 2026 г.

COALESCE в SQL — как заменить NULL на значение

Коротко

COALESCE -- функция SQL, которая возвращает первое не-NULL значение из списка аргументов. Принимает любое количество аргументов (от двух), перебирает их слева направо и возвращает первый, который не NULL. Если все аргументы NULL -- возвращает NULL. Это часть стандарта ANSI SQL и работает во всех СУБД.

Синтаксис

COALESCE(value1, value2, ..., valueN)

Аргументов может быть сколько угодно. COALESCE проверяет их по порядку и останавливается на первом не-NULL:

SELECT COALESCE(NULL, NULL, 'третий', 'четвёртый');
-- Результат: 'третий'

SELECT COALESCE(NULL, NULL, NULL);
-- Результат: NULL

Тип результата определяется по аргументу с наивысшим приоритетом типа. Все аргументы должны быть приводимы к совместимому типу -- иначе будет ошибка.

Цепочка фолбэков

Главная сила COALESCE -- выстраивание цепочки запасных значений. Если первый вариант NULL, берётся второй. Если и он NULL -- третий. И так далее.

-- Источник трафика: utm_source → referrer → 'direct'
SELECT
    user_id,
    COALESCE(utm_source, referrer, 'direct') AS traffic_source
FROM visits;

-- Имя для отображения: nickname → first_name → 'Аноним'
SELECT
    user_id,
    COALESCE(nickname, first_name, 'Аноним') AS display_name
FROM users;

-- Телефон для связи: мобильный → рабочий → домашний → 'нет телефона'
SELECT
    COALESCE(mobile_phone, work_phone, home_phone, 'нет телефона') AS contact_phone
FROM contacts;

Без COALESCE пришлось бы писать громоздкий CASE WHEN с цепочкой IS NOT NULL проверок.

COALESCE в SELECT

Самый частый сценарий -- подставить значение по умолчанию при выводе данных. Полезно для дашбордов и отчётов, где NULL выглядит как пустая ячейка.

-- Вместо NULL в отчёте -- понятные значения
SELECT
    user_id,
    COALESCE(city, 'Не указан') AS city,
    COALESCE(company, 'Фриланс') AS company,
    COALESCE(experience_years, 0) AS experience_years
FROM candidates;

COALESCE не меняет данные в таблице -- только подставляет значение на лету при чтении.

COALESCE в WHERE

-- Найти пользователей, у которых хотя бы один контакт заполнен
SELECT *
FROM users
WHERE COALESCE(email, phone, telegram) IS NOT NULL;

Важный нюанс: COALESCE в WHERE на индексированном столбце убивает индекс. СУБД не может использовать B-tree индекс, если столбец обёрнут в функцию:

-- Индекс по status НЕ используется
WHERE COALESCE(status, 'unknown') = 'active'

-- Индекс по status используется
WHERE (status = 'active' OR status IS NULL)

Если фильтрация по COALESCE нужна часто -- создайте функциональный индекс или перепишите условие без обёртки.

COALESCE с агрегатами

Агрегатные функции игнорируют NULL. Иногда это нежелательно -- COALESCE помогает контролировать поведение.

-- SUM пустого набора возвращает NULL, не 0
-- COALESCE превращает NULL в 0
SELECT
    department,
    COALESCE(SUM(bonus), 0) AS total_bonus
FROM employees
GROUP BY department;

-- AVG игнорирует NULL -- а вам нужно считать их как 0
SELECT
    team_id,
    AVG(COALESCE(score, 0)) AS avg_score_with_zeros,
    AVG(score) AS avg_score_without_nulls
FROM reviews
GROUP BY team_id;

Обратите внимание на разницу: COALESCE(SUM(...), 0) оборачивает результат агрегата, а AVG(COALESCE(..., 0)) оборачивает каждое значение до агрегации. Результат будет разным.

Заполнение пропусков в отчётах

Классическая задача: в таблице есть данные не за все дни, а в отчёте нужна непрерывная шкала.

-- Календарь дат LEFT JOIN с фактами
-- Пропущенные дни получают 0 вместо NULL
SELECT
    d.dt AS report_date,
    COALESCE(SUM(o.amount), 0) AS revenue,
    COALESCE(COUNT(o.order_id), 0) AS orders
FROM calendar d
LEFT JOIN orders o ON o.order_date = d.dt
WHERE d.dt BETWEEN '2025-01-01' AND '2025-01-31'
GROUP BY d.dt
ORDER BY d.dt;

Без COALESCE дни без заказов покажут NULL вместо нулей -- а это ломает графики.

COALESCE vs CASE WHEN

COALESCE(a, b) -- сокращение для CASE WHEN a IS NOT NULL THEN a ELSE b END. Но COALESCE удобнее:

-- COALESCE -- коротко и понятно
SELECT COALESCE(nickname, first_name, 'Аноним') AS name
FROM users;

-- CASE WHEN -- то же самое, но многословно
SELECT
    CASE
        WHEN nickname IS NOT NULL THEN nickname
        WHEN first_name IS NOT NULL THEN first_name
        ELSE 'Аноним'
    END AS name
FROM users;

Если проверка сложнее, чем «не NULL» (например, не пустая строка, не ноль) -- нужен CASE WHEN. Для замены NULL -- COALESCE.

COALESCE vs ISNULL

ISNULL -- функция SQL Server, принимает ровно два аргумента. COALESCE -- стандарт SQL, работает везде и принимает любое количество аргументов. Подробное сравнение -- в отдельном посте.

Если вы работаете в PostgreSQL, ClickHouse, MySQL или BigQuery -- ISNULL вам недоступен. Используйте COALESCE.

Типичные ошибки

  1. COALESCE на индексированном столбце. WHERE COALESCE(col, 0) > 10 не использует индекс. Перепишите: WHERE col > 10 OR col IS NULL
  2. Несовместимые типы аргументов. COALESCE(123, 'текст') вызовет ошибку приведения типов. Все аргументы должны быть совместимы
  3. Путаница SUM и COALESCE. SUM(COALESCE(x, 0)) и COALESCE(SUM(x), 0) -- разные вещи. Первое заменяет каждый NULL на 0 до суммирования, второе заменяет NULL-результат всей суммы
  4. Забыли про пустую строку. COALESCE не считает пустую строку '' за NULL. COALESCE('', 'fallback') вернёт '', а не 'fallback'. Если нужно обработать и пустые строки -- используйте NULLIF: COALESCE(NULLIF(col, ''), 'fallback')

Вопросы с собеседований

-- Что делает COALESCE? -- Возвращает первое не-NULL значение из списка аргументов. Принимает любое количество аргументов. Если все NULL -- возвращает NULL.

-- Что вернёт COALESCE(NULL, 0, NULL, 5)? -- 0. COALESCE перебирает аргументы слева направо и возвращает первый не-NULL. Ноль -- это не NULL, а конкретное число.

-- Чем COALESCE отличается от CASE WHEN? -- COALESCE(a, b) эквивалентен CASE WHEN a IS NOT NULL THEN a ELSE b END. COALESCE -- частный случай для проверки на NULL. Для более сложных условий (не равно, больше, содержит) нужен CASE WHEN.

-- Как с помощью COALESCE посчитать AVG с учётом NULL как нулей? -- AVG(COALESCE(column, 0)). Без COALESCE агрегатная функция AVG игнорирует NULL-строки, с COALESCE они превращаются в нули и учитываются в знаменателе.

-- Влияет ли COALESCE в WHERE на использование индексов? -- Да. WHERE COALESCE(col, 0) = 1 не сможет использовать индекс по col. СУБД видит функцию вокруг столбца и переключается на полное сканирование. Лучше переписать условие без обёртки.

Потренируйтесь на реальных вопросах с собеседований -- откройте тренажёр с 1500+ вопросами по SQL и другим темам аналитика. Больше примеров вопросов -- по NULL, агрегатам, джойнам и другим темам.

FAQ

COALESCE работает во всех СУБД?

Да. COALESCE -- часть стандарта ANSI SQL. Работает одинаково в PostgreSQL, MySQL, ClickHouse, BigQuery, SQL Server, Oracle и других СУБД. Это самый портируемый способ заменить NULL.

Сколько аргументов можно передать в COALESCE?

Формально ограничений нет (зависит от СУБД, но обычно десятки аргументов не проблема). На практике больше 3--4 аргументов -- редкость. Если цепочка длиннее, возможно, стоит пересмотреть модель данных.

COALESCE и NULLIF -- в чём разница?

Обратные операции. COALESCE превращает NULL в значение: COALESCE(NULL, 0) -> 0. NULLIF превращает значение в NULL: NULLIF(0, 0) -> NULL. Часто используются вместе: COALESCE(NULLIF(col, ''), 'fallback') -- заменяет и NULL, и пустую строку.

Как использовать COALESCE для защиты от деления на ноль?

Для этого лучше подходит NULLIF: revenue / NULLIF(sessions, 0). Если sessions = 0, NULLIF вернёт NULL, и деление даст NULL вместо ошибки. А если нужно вместо NULL получить 0: COALESCE(revenue / NULLIF(sessions, 0), 0). Подробнее о работе с NULL в SQL.

Тренироваться в Telegram