COALESCE

Функция для подстановки значений вместо NULL

Теория

Что делать, если в некоторых строках нужного столбца присутствуют значения NULL, а хочется обработать столбец так, чтобы вместо NULL в результате выдавалось что-то определенное, например какой-то текст или число или еще что-нибудь отличное от NULL?
В принципе, в прошлой теме мы изучили конструкцию CASE, которая хорошо подойдет для такого рода задач, но значения NULL так часто встречаются в таблицах и появляются в результате других операций, таких как соединение таблиц, что использовать везде для их замены конструкцию CASE может оказаться сложноватым.

Представьте ситуацию. Мы хотим вывести список пользователей. И если у человека в столбце phone указан номер телефона, выводим его телефон в результате. Если телефона нет, то показываем его почту из столбца email. А если и этого нет, выводим фразу 'контакт не указан'. С этой задачей идеально справится функция COALESCE.

По сути COALESCE — это как CASE на минималках. Данная функция просто проверяет значения по порядку и берёт первое, которое не равно NULL.

Принцип работы функции заключается в следующем: сначала система пытается найти первое значение из тех, что указаны в скобках внутри функции. Если оно отсутствует (то есть оно NULL), функция переходит к следующему значению. Если и оно не найдено, проверяется следующее. Если же и после этого значение не обнаружено, возвращается NULL или заранее установленное запасное значение.

Важно: COALESCE ничего не меняет в таблице. Она только помогает показать результат запроса в удобном виде или подставить запасной вариант вместо неопределённого значения.

Синтаксис

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

SQL

Внутри COALESCE(...) значения пишутся по порядку.

Логика работы следующая:

• если первое значение есть, функция вернёт его (речь про значения внутри скобок после COALESCE(...));
• если первое значение равно NULL, проверяется второе;
• если второе тоже NULL, проверяется третье;
• и так далее.

То есть COALESCE всегда пытается взять первое доступное значение, которое не является NULL.

Например, запись COALESCE(city, 'город не указан') отработает так: если город указан, выведет его; если нет — выведет текст 'город не указан'.

А если написать COALESCE(phone, email, 'контакт не указан'): сначала функция попробует вывести номер телефона, если его нет — почту, а если нет ни того ни другого — покажет текст 'контакт не указан').

Есть и ограничение: значения внутри COALESCE должны быть совместимы по типу данных. Например, нельзя смешать число, дату и текст в одной функции. В таких случаях SQL выдаст ошибку. Если очень нужно совместить разные типы, сначала их приводят к одному виду через преобразование типа.

Примеры

1. Показываем понятный текст вместо неопределенного города

SELECT user_id,
       first_name,
       city,
       COALESCE(city, 'город не указан') AS city_label
FROM users
ORDER BY registration_date DESC;

Если город в строке заполнен, COALESCE вернёт его. Если в city есть NULL, в результате появится текст 'город не указан'.

🔄 Попробуйте изменить запрос:

• Замените текст 'город не указан' на 'нет данных'
• Отсортируйте результат по вычисляемому столбцу: ORDER BY city_label ASC

2. Подставляем запасной статус для заказов без статуса

SELECT order_id,
       order_date,
       status,
       COALESCE(status, 'статус не указан') AS status_label
FROM orders
WHERE order_date >= '2024-01-01'
ORDER BY order_date DESC;

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

🔄 Попробуйте изменить запрос:

• Поменяйте текст 'статус не указан' на 'ожидает обработки'
• Оставьте только заказы без статуса, добавив условие AND status IS NULL

3. Ищем товары, где категория не указана

SELECT product_id,
       product_name,
       category,
       price
FROM products
WHERE COALESCE(category, 'без категории') = 'без категории'
ORDER BY price DESC;

В этом примере COALESCE используется не в SELECT, а в WHERE. Если категория у товара не указана, функция временно подставит текст 'без категории', и такую строку можно будет отфильтровать обычным сравнением.

🔄 Попробуйте изменить запрос:

• Замените текст 'без категории' на 'категория не указана' и поправьте сравнение тоже
• Добавьте в SELECT столбец COALESCE(category, 'без категории') AS category_label

4. Сортируем заказы так, чтобы пустой статус не мешал

SELECT order_id,
       order_date,
       status
FROM orders
ORDER BY COALESCE(status, 'яяя'),
         order_date DESC;

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

🔄 Попробуйте изменить запрос:

• Подставьте вместо 'яяя' текст 'ааа' и посмотрите, как изменится порядок строк
• Добавьте в SELECT столбец COALESCE(status, 'статус не указан') AS status_label

5. Подставляем текст вместо цены и заранее приводим тип

SELECT product_id,
       product_name,
       COALESCE(price::text, 'цена не указана') AS price_label
FROM products
ORDER BY product_id;

Для корректной работы COALESCE необходимо привести типы данных. Поле price является числовым, а текст цена не указана — строковым. Если использовать конструкцию COALESCE(price, 'цена не указана'), PostgreSQL выдаст ошибку. Чтобы избежать этого, сначала преобразуем числовое значение price в текст с помощью price::text, а затем применим COALESCE.

🔄 Попробуйте изменить запрос:

• Уберите ::text у поля price и посмотрите, что произойдет

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

Думают, что COALESCE заменяет и NULL, и пустую строку '' — это не NULL. Если в колонке лежит пустая строка, COALESCE её не тронет. Используют COALESCE там, где достаточно простого IS NULL Если задача просто найти пустые значения, обычно понятнее написать WHERE column IS NULL. COALESCE полезнее там, где нужно именно подставить запасной вариант. Забывают, что аргументы проверяются слева направо COALESCE вернёт первое подходящее значение и дальше уже не пойдёт. Поэтому порядок аргументов здесь важен. Смешивают несовместимые типы данных Например, запись вроде COALESCE(price, 'нет цены') может закончиться ошибкой, потому что число и текст — это разные типы. Если нужно такое поведение, сначала стоит явно привести значения к одному типу.

COALESCE

Теория

Синтаксис

Примеры

1. Показываем понятный текст вместо неопределенного города

🔄 Попробуйте изменить запрос:

2. Подставляем запасной статус для заказов без статуса

🔄 Попробуйте изменить запрос:

3. Ищем товары, где категория не указана

🔄 Попробуйте изменить запрос:

4. Сортируем заказы так, чтобы пустой статус не мешал

🔄 Попробуйте изменить запрос:

5. Подставляем текст вместо цены и заранее приводим тип

🔄 Попробуйте изменить запрос:

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

Практика

Проверь себя