COALESCE в SQL: как заменить NULL на понятное значение

COALESCE — это функция SQL, которая возвращает первое значение, которое не равно NULL.

Проще говоря, это запасной план на случай пустоты:

«Возьми никнейм. Если никнейма нет — возьми имя. Если и имени нет — покажи значение по умолчанию».

Например:

SELECT COALESCE(nickname, full_name, 'Guest') AS display_name
FROM users;

Такой запрос говорит базе:

1. Если nickname заполнен — верни его.
2. Если nickname равен NULL, попробуй full_name.
3. Если и full_name равен NULL, верни 'Guest'.

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

Зачем нужен COALESCE

NULL в SQL означает: значения нет, значение неизвестно или оно не было заполнено.

Сам по себе NULL не плохой. Это честный способ сказать: «данных нет».

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

Например, в интерфейсе хочется показать:

Hello, Anna

А получается:

Hello,

Или в отчёте хочется увидеть сумму бонусов, а вместо числа приходит пустое значение.

Ещё неприятнее NULL ведёт себя в арифметике:

SELECT 100 + NULL;

Результат будет:

?column?
NULL

SQL рассуждает так: если одно из значений неизвестно, итог тоже неизвестен.

100 + NULL — это не 100. Это неизвестный результат, потому что мы не знаем, что было на месте NULL.

Именно здесь помогает COALESCE: он позволяет заранее заменить NULL на безопасное значение.

Базовый синтаксис COALESCE

Синтаксис простой:

COALESCE(value_1, value_2, value_3)

Аргументов может быть два, три, пять — сколько нужно.

SQL проверяет их слева направо и возвращает первое значение, которое не равно NULL.

Например:

SELECT COALESCE(NULL, NULL, 'hello', 'world') AS result;

Результат:

result
hello

Почему вернулся hello?

Потому что первые два значения — NULL, а hello — первое нормальное значение.

До world дело уже не дошло.

Если все значения равны NULL, результат тоже будет NULL:

SELECT COALESCE(NULL, NULL, NULL) AS result;

Результат:

result
NULL

Пример с пользователями

Допустим, есть таблица users:

id	full_name	nickname
1	Anna	anya_88
2	Bob	NULL
3	NULL	gigachad
4	NULL	NULL

Нужно вывести имя, которое можно показать в интерфейсе.

Логика такая:

• если есть nickname, показываем его;
• если nickname нет, показываем full_name;
• если нет ни того ни другого, показываем гостевое имя.

Запрос:

SELECT
  id,
  COALESCE(nickname, full_name, 'Guest') AS display_name
FROM users;

Результат:

id	display_name
1	anya_88
2	Bob
3	gigachad
4	Guest

Разберём по строкам.

У первого пользователя есть nickname, поэтому вернулся anya_88.

У второго nickname равен NULL, зато есть full_name, поэтому вернулся Bob.

У третьего нет full_name, но есть nickname, поэтому вернулся gigachad.

У четвёртого оба поля равны NULL, поэтому сработал последний запасной вариант — Guest.

В этом и красота COALESCE: он позволяет написать цепочку fallback-значений прямо в запросе.

COALESCE не меняет данные в таблице

Важно понимать: COALESCE в SELECT не записывает новое значение в таблицу.

Запрос:

SELECT
  id,
  COALESCE(nickname, full_name, 'Guest') AS display_name
FROM users;

не заполняет пустые nickname и full_name.

Он просто показывает результат так, будто у нас есть удобная колонка display_name.

Исходные данные остаются прежними.

Это похоже на витрину магазина: внутри склада может быть беспорядок, но на витрине мы показываем аккуратную карточку товара.

COALESCE для безопасной арифметики

Один из самых частых сценариев — заменить NULL на 0 перед расчётом.

Допустим, есть таблица users:

id	name	points	bonus
1	Anna	100	20
2	Bob	50	NULL
3	Vera	NULL	10
4	Denis	NULL	NULL

Если написать обычное сложение:

SELECT
  name,
  points + bonus AS total_score
FROM users;

результат будет таким:

name	total_score
Anna	120
Bob	NULL
Vera	NULL
Denis	NULL

Почему?

Потому что любое сложение с NULL даёт NULL.

Для Bob база видит:

50 + unknown = unknown

Для Vera:

unknown + 10 = unknown

Для Denis:

unknown + unknown = unknown

Если по смыслу отсутствующие баллы нужно считать нулём, используем COALESCE:

SELECT
  name,
  COALESCE(points, 0) + COALESCE(bonus, 0) AS total_score
FROM users;

Результат:

name	total_score
Anna	120
Bob	50
Vera	10
Denis	0

Теперь NULL в points превращается в 0, и NULL в bonus тоже превращается в 0.

Это удобно для очков, бонусов, количества товаров, просмотров и других чисел, где пустое значение по бизнес-логике можно считать нулём.

Когда нельзя бездумно заменять NULL на 0

COALESCE — полезный инструмент, но применять его нужно по смыслу.

Например, если у товара нет оценки, это не всегда означает, что оценка равна 0.

product_id	rating
1	5
2	NULL

Если написать:

SELECT
  product_id,
  COALESCE(rating, 0) AS rating
FROM product_reviews;

пользователь может подумать, что товар получил оценку 0. Но на самом деле оценку ещё никто не поставил.

Для интерфейса иногда лучше показать текст вроде «нет оценок», а не подставлять ноль.

Главное правило: заменяй NULL на 0 только тогда, когда по смыслу отсутствие значения действительно равно нулю.

Для суммы заказов — часто да.

Для оценки, даты рождения, email или номера телефона — обычно нет.

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

Агрегатные функции тоже часто возвращают NULL.

Например, есть таблица orders:

id	customer_id	amount
1	10	1200
2	10	800
3	15	500

Посчитаем сумму заказов клиента 10:

SELECT SUM(amount) AS total_amount
FROM orders
WHERE customer_id = 10;

Результат:

total_amount
2000

А теперь посчитаем сумму заказов клиента, у которого заказов нет:

SELECT SUM(amount) AS total_amount
FROM orders
WHERE customer_id = 999;

Результат:

total_amount
NULL

Это может удивлять новичков.

Кажется, что сумма пустого набора должна быть 0. Но SQL возвращает NULL: считать было нечего, результата нет.

В отчётах и интерфейсах чаще нужен 0. Тогда пишут так:

SELECT COALESCE(SUM(amount), 0) AS total_amount
FROM orders
WHERE customer_id = 999;

Результат:

total_amount
0

Такой приём стоит запомнить. Он часто встречается в финансовых отчётах, личных кабинетах, админках и аналитических дашбордах.

COALESCE с AVG, MIN и MAX

С другими агрегатами логика похожая.

Если подходящих строк нет, AVG, MIN и MAX тоже могут вернуть NULL.

SELECT AVG(rating) AS avg_rating
FROM reviews
WHERE product_id = 999;

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

Можно написать:

SELECT COALESCE(AVG(rating), 0) AS avg_rating
FROM reviews
WHERE product_id = 999;

Но здесь нужно быть осторожным.

Средняя оценка 0 и отсутствие оценок — это разные вещи.

Для внутреннего числового отчёта 0 может быть удобен. А для пользовательского интерфейса иногда лучше оставить NULL и на уровне приложения показать «пока нет отзывов».

COALESCE даёт техническую возможность заменить NULL, но решение всегда должно соответствовать смыслу данных.

COALESCE и строки

COALESCE часто используют для текстовых полей.

Например, нужно собрать контактное имя клиента:

SELECT
  id,
  COALESCE(company_name, contact_name, email, 'Unknown') AS contact_label
FROM customers;

Логика:

1. Если есть название компании — показываем его.
2. Если компании нет — показываем имя контакта.
3. Если имени нет — показываем email.
4. Если нет вообще ничего — показываем Unknown.

Такой запрос удобен для списков, CRM, админок и таблиц, где нужно показать человеку хоть какую-то понятную подпись.

Пустая строка — это не NULL

Очень частая ловушка: COALESCE работает только с NULL.

Пустая строка — это не NULL.

Например:

SELECT COALESCE('', 'default') AS result;

Результат будет пустой строкой, а не default.

Почему?

Потому что пустая строка — это значение. Просто оно состоит из нуля символов.

Для SQL это не то же самое, что отсутствие значения.

Если нужно считать и NULL, и пустую строку отсутствием значения, используют связку NULLIF и COALESCE.

SELECT COALESCE(NULLIF(value, ''), 'default') AS clean_value
FROM settings;

Разберём:

NULLIF(value, '')

возвращает NULL, если value равен пустой строке.

А потом COALESCE заменяет этот NULL на default.

Так мы обрабатываем сразу два случая:

• значение действительно NULL;
• значение есть, но оно пустая строка.

NULLIF: обратная идея

NULLIF часто изучают рядом с COALESCE.

Если COALESCE заменяет NULL нормальным значением, то NULLIF делает обратное: превращает выбранное значение в NULL.

Синтаксис:

NULLIF(value_1, value_2)

Если value_1 равно value_2, функция вернёт NULL.

Если не равно — вернёт value_1.

Пример:

SELECT NULLIF(score, 0) AS score_or_null
FROM results;

Если score = 0, результат будет NULL.

Если score = 7, результат будет 7.

NULLIF для безопасного деления

Самый практичный пример NULLIF — защита от деления на ноль.

Обычный запрос:

SELECT total_amount / orders_count AS avg_order_amount
FROM customer_stats;

Если orders_count = 0, база получит деление на ноль. Это ошибка.

Можно защититься так:

SELECT total_amount / NULLIF(orders_count, 0) AS avg_order_amount
FROM customer_stats;

Что происходит?

Если orders_count равен 0, выражение:

NULLIF(orders_count, 0)

вернёт NULL.

А деление на NULL даст NULL, а не ошибку деления на ноль.

Если хочется вместо NULL получить 0, можно добавить COALESCE:

SELECT
  COALESCE(total_amount / NULLIF(orders_count, 0), 0) AS avg_order_amount
FROM customer_stats;

Здесь вся цепочка читается так:

1. Если orders_count равен 0, преврати его в NULL.
2. Деление на NULL даст NULL.
3. Если итог получился NULL, покажи 0.

Для отчётов это очень полезный приём.

COALESCE вместо CASE

COALESCE можно представить как короткую форму CASE.

Запрос:

SELECT COALESCE(nickname, full_name, 'Guest') AS display_name
FROM users;

по смыслу похож на:

SELECT
  CASE
    WHEN nickname IS NOT NULL THEN nickname
    WHEN full_name IS NOT NULL THEN full_name
    ELSE 'Guest'
  END AS display_name
FROM users;

Результат будет тот же.

Но COALESCE короче и читается лучше, когда логика простая: «возьми первое не NULL».

CASE нужен, когда условие сложнее.

Например:

SELECT
  CASE
    WHEN total_spent >= 10000 THEN 'platinum'
    WHEN total_spent >= 1000 THEN 'gold'
    ELSE 'regular'
  END AS user_tier
FROM users;

Здесь мы проверяем не NULL, а диапазоны значений. Для такой логики нужен CASE.

Итого:

• если нужно выбрать первое заполненное значение — COALESCE;
• если нужна условная логика — CASE.

Типы данных в COALESCE

Все аргументы COALESCE должны быть совместимы по типам.

Хороший пример:

SELECT COALESCE(nickname, full_name, 'Guest') AS display_name
FROM users;

Здесь все значения текстовые.

Тоже хороший пример:

SELECT COALESCE(points, 0) AS points
FROM users;

Здесь points — число, и 0 — число.

А вот так может быть ошибка:

SELECT COALESCE(points, 'unknown') AS points_label
FROM users;

Если points — числовая колонка, а unknown — текст, базе нужно решить, какой тип должен быть у результата. В PostgreSQL такой запрос может закончиться ошибкой преобразования типов.

Правильнее явно выбрать одну идею.

Если нужен числовой результат:

SELECT COALESCE(points, 0) AS points
FROM users;

Если нужен текстовый результат:

SELECT COALESCE(points::TEXT, 'unknown') AS points_label
FROM users;

Главное: не смешивать в одной функции числа, даты и текст без понимания, к чему база будет приводить типы.

COALESCE в WHERE: когда лучше не надо

Технически COALESCE можно использовать в WHERE.

Например:

SELECT
  id,
  email
FROM users
WHERE COALESCE(deleted_at, DATE '1970-01-01') = DATE '1970-01-01';

Такой запрос пытается найти строки, где deleted_at равен NULL.

Но лучше написать проще:

SELECT
  id,
  email
FROM users
WHERE deleted_at IS NULL;

Почему второй вариант лучше?

Потому что он понятнее и обычно лучше дружит с обычным индексом по колонке deleted_at.

Когда мы оборачиваем колонку в функцию:

COALESCE(deleted_at, DATE '1970-01-01')

базе сложнее использовать простой индекс по исходной колонке. В некоторых случаях можно создать специальный индекс по выражению, но для обычной фильтрации по NULL это лишняя сложность.

Хорошее правило для новичка:

• в SELECT использовать COALESCE для красивого вывода — отлично;
• в WHERE для проверки пустоты чаще писать IS NULL или IS NOT NULL.

Когда COALESCE не подходит

COALESCE проверяет только NULL.

Он не считает плохими значениями:

• 0;
• FALSE;
• пустую строку;
• отрицательное число;
• дату из прошлого;
• неизвестный статус вроде unknown.

Например:

SELECT COALESCE(0, 100) AS result;

Результат:

result
0

Потому что 0 — это не NULL.

Ещё пример:

SELECT COALESCE(FALSE, TRUE) AS result;

Результат:

result
false

Потому что FALSE — это нормальное boolean-значение, а не отсутствие значения.

Если тебе нужно заменить не только NULL, но и 0, пустую строку или какой-то специальный статус, обычно нужен CASE или связка с NULLIF.

Например, заменить 0 на 100:

SELECT COALESCE(NULLIF(score, 0), 100) AS score_value
FROM results;

Здесь NULLIF(score, 0) сначала превращает 0 в NULL, а потом COALESCE заменяет его на 100.

Длинные цепочки COALESCE

COALESCE может принимать много аргументов:

SELECT COALESCE(phone, mobile_phone, work_phone, email, 'no_contact') AS contact
FROM users;

Иногда это нормально. Например, мы действительно выбираем первый доступный способ связи.

Но если цепочка становится слишком длинной, это повод задуматься.

COALESCE(a, b, c, d, e, f, g)

Формально работает, но читать такое тяжело. Часто длинная цепочка говорит о том, что данные хранятся не очень удобно или в схеме накопилось много исторических полей.

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

Практический пример: карточка пользователя

Допустим, нужно собрать данные для карточки пользователя.

Таблица users:

id	nickname	full_name	email	points
1	anya_88	Anna Ivanova	anna@example.com	120
2	NULL	Bob Petrov	bob@example.com	NULL
3	NULL	NULL	vera@example.com	50
4	NULL	NULL	NULL	NULL

Хотим вывести:

• имя для отображения;
• контакт;
• баллы, где отсутствие баллов считается нулём.

SELECT
  id,
  COALESCE(nickname, full_name, 'Guest') AS display_name,
  COALESCE(email, 'no_email') AS contact_email,
  COALESCE(points, 0) AS points
FROM users;

Результат:

id	display_name	contact_email	points
1	anya_88	anna@example.com	120
2	Bob Petrov	bob@example.com	0
3	Guest	vera@example.com	50
4	Guest	no_email	0

Такой результат уже можно удобно показывать в интерфейсе. Вместо дыр и пустых значений — понятные fallback-значения.

Практический пример: отчёт по заказам

Есть таблица orders:

id	customer_id	amount
1	10	1200
2	10	800
3	15	500

Нужно вывести сумму заказов по конкретному клиенту. Если заказов нет — показать 0.

SELECT
  COALESCE(SUM(amount), 0) AS total_amount
FROM orders
WHERE customer_id = 999;

Результат:

total_amount
0

Без COALESCE результатом был бы NULL.

Для отчёта, где ожидается число, это почти всегда неудобно.

Частые ошибки новичков

Думать, что COALESCE заменяет пустые строки

Пустая строка не равна NULL.

SELECT COALESCE('', 'default') AS result;

Результат — пустая строка.

Если нужно обработать и NULL, и пустую строку, используй NULLIF:

SELECT COALESCE(NULLIF(value, ''), 'default') AS result
FROM settings;

Смешивать несовместимые типы

Плохо:

SELECT COALESCE(points, 'unknown') AS points
FROM users;

Если points — число, а unknown — текст, база может не понять, каким должен быть итоговый тип.

Лучше либо число:

SELECT COALESCE(points, 0) AS points
FROM users;

либо текст:

SELECT COALESCE(points::TEXT, 'unknown') AS points_label
FROM users;

Использовать COALESCE в фильтре там, где достаточно IS NULL

Сложнее и хуже для чтения:

SELECT *
FROM users
WHERE COALESCE(deleted_at, DATE '1970-01-01') = DATE '1970-01-01';

Проще:

SELECT *
FROM users
WHERE deleted_at IS NULL;

Для проверки NULL в WHERE обычно лучше использовать IS NULL и IS NOT NULL.

Ждать, что COALESCE заменит 0 или FALSE

SELECT COALESCE(0, 100) AS result;

Вернёт 0.

SELECT COALESCE(FALSE, TRUE) AS result;

Вернёт false.

COALESCE реагирует только на NULL.

0 и FALSE — это нормальные значения.

Прятать проблему данных за длинной цепочкой

SELECT COALESCE(a, b, c, d, e, f, 'unknown') AS value
FROM some_table;

Иногда это оправдано. Но если таких цепочек много, стоит подумать: почему одно и то же значение может лежать в шести разных колонках?

COALESCE помогает сделать вывод аккуратнее, но не заменяет нормальную модель данных.

Короткая шпаргалка по COALESCE

Первое не NULL значение:

SELECT COALESCE(NULL, 'hello', 'world') AS result;

Fallback для имени пользователя:

SELECT COALESCE(nickname, full_name, 'Guest') AS display_name
FROM users;

Замена NULL на 0 в арифметике:

SELECT
  COALESCE(points, 0) + COALESCE(bonus, 0) AS total_score
FROM users;

Ноль вместо NULL для пустой суммы:

SELECT COALESCE(SUM(amount), 0) AS total_amount
FROM orders;

Обработка пустой строки и NULL:

SELECT COALESCE(NULLIF(value, ''), 'default') AS clean_value
FROM settings;

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

SELECT
  COALESCE(total_amount / NULLIF(orders_count, 0), 0) AS avg_order_amount
FROM customer_stats;

Проверка NULL в фильтре:

SELECT *
FROM users
WHERE deleted_at IS NULL;

Главное из статьи

COALESCE возвращает первое значение, которое не равно NULL.

Он работает слева направо:

COALESCE(value_1, value_2, value_3)

Если value_1 не NULL, вернётся он. Если value_1 равен NULL, SQL проверит value_2. Если и он равен NULL, проверит следующее значение.

Главное применение COALESCE — подставлять значение по умолчанию вместо NULL.

Для текста это может быть fallback вроде Guest.

Для чисел — часто 0.

Для агрегатов — например, COALESCE(SUM(amount), 0), чтобы пустая сумма отображалась как 0, а не как NULL.

COALESCE не заменяет пустые строки, 0 или FALSE, потому что всё это не NULL.

Если нужно считать пустую строку отсутствием значения, помогает связка NULLIF и COALESCE.

В WHERE для проверки пустоты чаще лучше использовать IS NULL или IS NOT NULL, а не оборачивать колонку в COALESCE.

Главная мысль простая: COALESCE делает работу с отсутствующими значениями спокойной и предсказуемой. Вместо пустот в отчётах, сломанной арифметики и неожиданных NULL ты явно говоришь базе, какое значение использовать как запасной вариант.