Что такое COALESCE в SQL? Замена NULL для начинающих

COALESCE — это функция, которая берёт несколько значений и возвращает первое не-NULL. По сути, это «fallback-цепочка»: если первое пусто, возьми второе; если и оно пусто — третье; и так до конца.

Самое частое применение — поставить дефолт вместо NULL: «если у юзера нет никнейма, покажи его имя; если и имени нет — 'Гость'».

Зачем нужен COALESCE

NULL — это «нет значения». Когда NULL попадает в строку отчёта или интерфейса, обычно это выглядит как дыра: «привет, [пусто]» вместо «привет, Аня». Или того хуже — NULL в арифметике превращает весь результат в NULL (100 + NULL = NULL).

COALESCE — простой способ сказать «если NULL, поставь вот это вместо».

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

COALESCE(значение1, значение2, значение3, ..., значениеN)

Postgres проверяет аргументы слева направо и возвращает первое, которое не NULL. Если все NULL — возвращает NULL.

SELECT COALESCE(NULL, NULL, 'hello', 'world');
-- → 'hello'

SELECT COALESCE(nickname, full_name, 'Гость') AS display_name
FROM users;
-- → если nickname есть, берём его
-- → иначе full_name
-- → иначе 'Гость'

Пример с таблицей

Таблица users:

id	full_name	nickname
1	Аня	anya_88
2	Боб	NULL
3	NULL	gigachad
4	NULL	NULL

Запрос с COALESCE:

SELECT
  id,
  COALESCE(nickname, full_name, 'Гость') AS display_name
FROM users;

Результат:

id	display_name
1	anya_88
2	Боб
3	gigachad
4	Гость

Каждой строке достался свой fallback:

• У Ани был nickname — взяли его.
• У Боба нет nickname, но есть full_name — взяли имя.
• У третьего нет full_name, но есть nickname — взяли никнейм.
• Четвёртый — оба NULL, поставился литерал 'Гость'.

Безопасная арифметика

NULL в формуле «отравляет» результат:

SELECT 100 + NULL;  -- → NULL
SELECT NULL * 2;    -- → NULL

Это часто ломает отчёты:

-- Если у юзера нет points, total_score = NULL вместо нормального числа
SELECT name, points + bonus AS total_score FROM users;

COALESCE спасает:

SELECT name, COALESCE(points, 0) + COALESCE(bonus, 0) AS total_score FROM users;
-- → теперь NULL обрабатывается как 0, total_score всегда число

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

Агрегатные функции (SUM, AVG, ...) возвращают NULL, если выборка пустая:

SELECT SUM(amount) FROM orders WHERE customer_id = 999;
-- → если у клиента 999 нет заказов, SUM = NULL, не 0

COALESCE спасает и тут:

SELECT COALESCE(SUM(amount), 0) FROM orders WHERE customer_id = 999;
-- → теперь 0 при пустой выборке

Это обязательная привычка при выводе цифр в UI: фронтенд ожидает число, NULL ломает форматирование.

COALESCE и индексы

WHERE COALESCE(deleted_at, '1970-01-01') = '1970-01-01' — формально работает (находит строки, где deleted_at IS NULL), но мешает индексу: Postgres не может использовать обычный индекс на deleted_at. Лучше:

-- Хорошо — простое условие, индекс работает
WHERE deleted_at IS NULL;

-- Плохо — функция оборачивает колонку, индекс не используется
WHERE COALESCE(deleted_at, '1970-01-01') = '1970-01-01';

COALESCE для отображения (SELECT) — отлично. Для фильтрации — обычно лучше переписать на IS NULL.

NULLIF — обратная функция

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

SELECT NULLIF(score, 0);
-- → если score = 0, вернёт NULL
-- → если score = 7, вернёт 7

Зачем это нужно? Чаще всего — для безопасного деления:

-- Деление на ноль вызовет ошибку
SELECT total / count FROM stats;

-- Защита: NULLIF превращает 0 в NULL, и Postgres вернёт NULL вместо ошибки
SELECT total / NULLIF(count, 0) FROM stats;

В паре COALESCE и NULLIF дают полный контроль над NULL'ами.

COALESCE vs CASE

COALESCE(a, b, c) это короткая форма для:

CASE
  WHEN a IS NOT NULL THEN a
  WHEN b IS NOT NULL THEN b
  ELSE c
END

То же самое, но COALESCE короче и читается яснее. CASE нужен, когда условие сложнее, чем «не NULL».

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

1. COALESCE не помогает с пустыми строками. '' (empty string) — это не NULL, это пустая строка. COALESCE('', 'default') вернёт '', не 'default'. Если нужно «и для NULL, и для пустых»:

-- Один из подходов
COALESCE(NULLIF(value, ''), 'default')

-- Или CASE
CASE WHEN value IS NULL OR value = '' THEN 'default' ELSE value END

2. Несовместимые типы аргументов. COALESCE(int_col, 'unknown') — Postgres попробует кастить 'unknown' в число, упадёт. Все аргументы должны быть совместимых типов.

3. Функция в WHERE мешает индексу. Уже разобрали — для фильтра пиши IS NULL/IS NOT NULL явно.

4. Думают, что COALESCE работает с FALSE/0. Нет — только с NULL. COALESCE(0, 'fallback') вернёт 0, потому что 0 IS NOT NULL. Если хочешь «или 0, или fallback» — это другая логика, и COALESCE не подходит.

5. Длинные цепочки COALESCE. COALESCE(a, b, c, d, e, f) — формально работает, но если их шесть, обычно дизайн неправильный. Пять-шесть полей-кандидатов на одну ячейку — повод подумать о схеме.

Мини-резюме

• COALESCE(a, b, c, ...) — возвращает первое не-NULL значение.
• Дефолты вместо NULL в выводе: COALESCE(nickname, name, 'Гость').
• Безопасная арифметика: COALESCE(points, 0) + COALESCE(bonus, 0).
• Дефолт для пустых агрегатов: COALESCE(SUM(amount), 0).
• Не работает с пустыми строками — для них в паре с NULLIF.
• В WHERE для фильтра NULL — лучше IS NULL/IS NOT NULL, не COALESCE.
• NULLIF(a, b) — антипод: возвращает NULL, если a = b. Главное применение — деление через NULLIF(divisor, 0).