Как работает функция COALESCE в SQL? Приведите примеры использования.?

Функция COALESCE в SQL

COALESCE — одна из самых полезных функций в SQL, которую все должны знать. Раскажу про её работу и практические применения.

Что такое COALESCE

COALESCE возвращает первое ненулевое (NOT NULL) значение из списка аргументов.

Синтаксис:

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

Если value1 не NULL, вернёт value1. Если value1 = NULL, проверит value2. И так далее.

Если все значения NULL, вернёт NULL.

Простой пример

SELECT 
  user_id,
  COALESCE(phone, email, username) as contact_info
FROM users

Результат: для каждого пользователя вернёт телефон, если он есть. Если телефона нет, вернёт email. Если и email нет, вернёт username. Если всё NULL, вернёт NULL.

Сравнение с ISNULL / IFNULL

В разных БД есть свои функции для работы с NULL:

-- SQL Server
ISNULL(column, replacement_value)

-- MySQL
IFNULL(column, replacement_value)

-- PostgreSQL, SQLite, Oracle
COALESCE(column, replacement_value)

COALESCE универсален и работает во всех БД. Плюс он может принимать больше 2 аргументов, а ISNULL/IFNULL — только 2.

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

Пример 1: Замена NULL на дефолтное значение

SELECT 
  user_id,
  name,
  COALESCE(bio, 'No bio provided') as user_bio
FROM users

Если bio = NULL, покажет 'No bio provided'.

Пример 2: Приоритет контактов

SELECT 
  user_id,
  COALESCE(phone_primary, phone_secondary, phone_work, email) as preferred_contact
FROM users

Вернёт первый доступный контакт в приоритетном порядке.

Пример 3: Слияние данных из разных источников

Представьте, у вас есть таблицы users и users_backup.

SELECT 
  COALESCE(u.user_id, ub.user_id) as user_id,
  COALESCE(u.name, ub.name) as name,
  COALESCE(u.email, ub.email) as email
FROM users u
FULL OUTER JOIN users_backup ub 
  ON u.user_id = ub.user_id

Результат: если данные есть в users, используем их. Если нет, берём из backup. Гарантирует, что всегда будут данные (если они вообще существуют).

Пример 4: Расчёты с потенциальными NULL

SELECT 
  product_id,
  name,
  price,
  discount,
  COALESCE(discount, 0) as actual_discount,
  price * (1 - COALESCE(discount, 0) / 100) as final_price
FROM products

Если discount = NULL, вычислим с 0. Это избежит NULL результата в финальной цене.

Пример 5: Логирование и аудит

SELECT 
  user_id,
  action,
  COALESCE(updated_at, created_at) as last_activity
FROM user_events

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

Пример 6: Аналитика с пропусками

Часто в аналитике нужна таблица со всеми возможными комбинациями значений.

SELECT 
  COALESCE(daily_stats.date, weekly_stats.date) as stat_date,
  COALESCE(daily_stats.users, 0) as daily_users,
  COALESCE(weekly_stats.users, 0) as weekly_users
FROM daily_stats
FULL OUTER JOIN weekly_stats 
  ON daily_stats.date = weekly_stats.date

COALESCE гарантирует, что date всегда заполнена, даже если по дневной или недельной статистике нет данных.

Пример 7: Реальный кейс из продукта

Таблица orders может иметь NULL в некоторых полях:

SELECT 
  order_id,
  user_id,
  created_at,
  COALESCE(updated_at, created_at) as last_status_change,
  COALESCE(delivered_at, shipped_at, processed_at, created_at) as order_stage_date,
  COALESCE(refund_amount, 0) as actual_refund
FROM orders

Результат:

• last_status_change: самая поздняя дата изменения заказа
• order_stage_date: дата самого позднего события в жизненном цикле заказа
• actual_refund: сумма возврата, или 0 если возврата не было

Производительность

COALESCE быстр и эффективен. Обычно БД сразу прерывает вычисление, как только находит ненулевое значение (short-circuit evaluation).

-- Медленно: вычисляет все 3 функции для каждой строки
SELECT 
  COALESCE(
    EXPENSIVE_FUNCTION_1(col1),
    EXPENSIVE_FUNCTION_2(col2),
    EXPENSIVE_FUNCTION_3(col3)
  )

-- Быстро: вычисляет только нужное
SELECT COALESCE(col1, col2, col3)

Но сложные выражения внутри COALESCE могут быть медленными. Поэтому сортируй аргументы по частоте появления:

-- Оптимально: самый вероятно ненулевой агрумент первый
COALESCE(primary_phone, secondary_phone, email)

-- Менее оптимально
COALESCE(email, secondary_phone, primary_phone)

COALESCE vs CASE

Можешь сделать то же самое с CASE, но COALESCE короче:

-- С COALESCE
SELECT COALESCE(phone, email, username)

-- С CASE (длиннее)
SELECT CASE 
  WHEN phone IS NOT NULL THEN phone
  WHEN email IS NOT NULL THEN email
  ELSE username
END

Частые ошибки

Ошибка 1: Все аргументы NULL

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

Ошибка 2: Ожидание преобразования типов

SELECT COALESCE('text', 123) -- Может быть ошибка типа в строгих БД

Все аргументы должны быть совместимыми типами (или БД их автоматически преобразует).

Ошибка 3: Игнорирование pусстых строк

SELECT COALESCE(name, 'Unknown')
FROM users
WHERE name = ''  -- Пустая строка, а не NULL!

-- COALESCE вернёт пустую строку, не 'Unknown'
-- Используй:
SELECT COALESCE(NULLIF(name, ''), 'Unknown')

Практический совет: COALESCE + NULLIF

NULLIF(expr1, expr2) возвращает NULL если expr1 = expr2, иначе expr1.

Комбинация полезна:

SELECT 
  user_id,
  COALESCE(NULLIF(nickname, ''), name, 'Anonymous') as display_name
FROM users

Логика: если nickname пуст или NULL, используй name. Если и name пуст/NULL, используй 'Anonymous'.

Итог

COALESCE это:

• Универсальная функция для работы с NULL
• Работает во всех современных БД
• Краче и читабельнее чем CASE
• Важна для слияния данных и обработки пропусков
• Небольшой performance cost, но обычно незначительный

Используй COALESCE везде, где работаешь с потенциальными NULL значениями. Это одна из самых полезных функций в SQL.