Функция COALESCE в SQL: синтаксис и примеры

COALESCE — это функция, которая принимает список аргументов и возвращает первый из них, не равный NULL. Чаще всего её используют, чтобы подставить значение по умолчанию вместо NULL в результатах запроса.

MySQL 8.1
COALESCE(значение_1, значение_2, ..., значение_N)

Работает она во всех популярных СУБД одинаково: MySQL, PostgreSQL, SQLite, SQL Server, Oracle. Это часть стандарта SQL, поэтому именно COALESCE стоит выбирать вместо нестандартных IFNULL, ISNULL и NVL — о них поговорим ниже.

Как работает COALESCE

Функция проверяет аргументы слева направо и останавливается на первом, который не равен NULL:

MySQL 8.1
SELECT COALESCE(NULL, NULL, 'SQL Academy', 'запасной вариант') AS result;

Первые два аргумента — NULL, поэтому они пропускаются. Третий аргумент не NULL — он и становится результатом, а до строки 'запасной вариант' проверка уже не доходит.

Если все аргументы равны NULL, то и результат будет NULL.

Живой пример: заменяем NULL в отчёте

Посмотрим на таблицу orders из базы данных сервиса доставки. В столбце promo_code хранится промокод заказа, а если заказ оформлен без промокода — там NULL:

MySQL 8.1
SELECT order_id, total_amount, promo_code
FROM orders
WHERE order_id IN (1, 4, 8, 13, 24)
ORDER BY order_id;

В отчёте для менеджера пустые ячейки выглядят как ошибка выгрузки. Подставим вместо NULL понятный текст:

MySQL 8.1
SELECT order_id, total_amount, COALESCE(promo_code, 'без промокода') AS promo
FROM orders
WHERE order_id IN (1, 4, 8, 13, 24)
ORDER BY order_id;

Там, где промокод есть, COALESCE вернул его без изменений. Там, где был NULL, — подставил текст из второго аргумента. Блок выше — живой: поменяйте текст замены на свой или оберните в COALESCE столбец delivery_time_min и посмотрите, что изменится.

Цепочка запасных вариантов

Сила COALESCE в том, что аргументов может быть сколько угодно. Классический сценарий — выбрать первый доступный контакт клиента: рабочий телефон, если его нет — мобильный, если нет и его — текст-заглушку:

MySQL 8.1
SELECT name, COALESCE(work_phone, mobile_phone, 'контактов нет') AS contact
FROM clients;

Каждая строка проходит проверку слева направо независимо от остальных: у Анны взялся рабочий телефон, у Бориса — мобильный, у Веры — заглушка.

COALESCE с агрегатными функциями

Агрегатные функции SUM, AVG, MIN, MAX возвращают NULL, если в группе не нашлось ни одного значения. Посчитаем среднее время доставки по статусам заказов:

MySQL 8.1
SELECT status, ROUND(AVG(delivery_time_min)) AS avg_delivery
FROM orders
GROUP BY status
ORDER BY status;

У отменённых заказов времени доставки нет — они не были доставлены, поэтому AVG вернул NULL. Если этот результат пойдёт в дальнейшие вычисления или на график, NULL сломает их. Обернём агрегат в COALESCE:

MySQL 8.1
SELECT status, COALESCE(ROUND(AVG(delivery_time_min)), 0) AS avg_delivery
FROM orders
GROUP BY status
ORDER BY status;

Важно понимать смысл подстановки: ноль здесь означает «данных нет», а не «доставили мгновенно». Для отчётов, где эта разница критична, лучше оставить NULL или вынести такие группы отдельно.

NULLIF: функция-противоположность

NULLIF делает обратное: превращает значение в NULL. Она принимает ровно два аргумента и возвращает NULL, если они равны, иначе — первый аргумент:

MySQL 8.1
NULLIF(значение_1, значение_2)

MySQL 8.1
SELECT NULLIF('доставлен', 'отменён') AS a, NULLIF('отменён', 'отменён') AS b;

Защита от деления на ноль

Самое частое применение NULLIF — знаменатель дроби. В PostgreSQL деление на ноль роняет запрос:

MySQL 8.1
SELECT 100 / 0 AS result;

MySQL в той же ситуации молча вернёт NULL и выдаст предупреждение. NULLIF делает поведение одинаковым и предсказуемым в обеих СУБД:

MySQL 8.1
SELECT 100 / NULLIF(0, 0) AS result;

Знаменатель превратился в NULL, деление на NULL дало NULL — запрос не упал. Снаружи можно добавить COALESCE и вернуть, например, ноль.

NULLIF и COALESCE вместе: чистим пустые строки

В реальных данных кроме NULL часто встречаются пустые строки и строки из пробелов. COALESCE их не заменит — пустая строка не равна NULL. В нашей таблице orders как раз такой случай:

MySQL 8.1
SELECT order_id, promo_code, COALESCE(promo_code, 'без промокода') AS promo
FROM orders
WHERE order_id IN (1, 2, 3, 8, 24)
ORDER BY order_id;

Заказ 1 обработан, а заказы 2 и 3 с пробелами и пустой строкой остались «дырками» в отчёте. Решение — связка из трёх функций: TRIM убирает пробелы, NULLIF превращает пустую строку в NULL, COALESCE подставляет заглушку:

MySQL 8.1
SELECT order_id, COALESCE(NULLIF(TRIM(promo_code), ''), 'без промокода') AS promo
FROM orders
WHERE order_id IN (1, 2, 3, 8, 24)
ORDER BY order_id;

Эта связка — рабочая лошадка очистки данных, запомните её.

Что вернёт выражение COALESCE(NULL, '', 'SQL Academy')?

COALESCE vs IFNULL vs ISNULL vs NVL

У COALESCE есть нестандартные родственники, привязанные к конкретным СУБД:

Главная ловушка здесь — ISNULL. В SQL Server это замена NULL с двумя аргументами, а в MySQL функция с тем же именем принимает один аргумент и просто проверяет его на NULL, возвращая 1 или 0:

MySQL 8.1
SELECT ISNULL(NULL) AS is_null, ISNULL('текст') AS is_not_null, IFNULL(NULL, 'замена') AS replaced;

Код с ISNULL(a, b), перенесённый из SQL Server в MySQL, упадёт с ошибкой о неверном числе аргументов.

В PostgreSQL нет ни IFNULL, ни ISNULL — запрос с ними завершится ошибкой:

MySQL 8.1
SELECT IFNULL(promo_code, 'без промокода') FROM orders;

Единственный вариант замены NULL здесь — COALESCE, и это ещё один довод писать на нём сразу.

Вывод простой: если код может оказаться в другой СУБД — пишите COALESCE, и он будет работать везде.

Какая функция замены NULL сработает и в MySQL, и в PostgreSQL, и в SQL Server?

Где COALESCE ведёт себя неожиданно

Несовместимые типы аргументов. PostgreSQL требует, чтобы все аргументы приводились к одному типу. Попытка подставить строку вместо числового NULL закончится ошибкой:

MySQL 8.1
SELECT COALESCE(delivery_time_min, 'нет данных') FROM orders;

MySQL в этой же ситуации молча приведёт число к строке — запрос отработает, но столбец сменит тип, что может удивить приложение, которое его читает.

COALESCE не заменит проверку IS NULL в условиях. Сравнение WHERE promo_code = NULL не вернёт ни одной строки, и COALESCE тут ни при чём: для фильтрации по NULL существует оператор IS NULL. Подробнее — в уроке Операторы IS NULL, BETWEEN, IN.

Пустая строка — не NULL. COALESCE('', 'замена') вернёт пустую строку, а не замену. Если «пусто» в ваших данных значит «нет значения», используйте связку NULLIF и COALESCE.

Что дальше

Лучший способ закрепить COALESCE — применить его руками:

• решайте задачи по SQL в тренажёре — функции работы с NULL встречаются там регулярно;
• разберитесь, как NULL ведёт себя в сравнениях и фильтрах, в уроке IS NULL, BETWEEN, IN;
• краткие справки по функциям — в справочнике: COALESCE, IFNULL, NULLIF.