COALESCE: примеры (SQL)

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

Работа с числовыми и строковыми данными:

SELECT COALESCE(NULL, 10, 20) AS Result;

Result
-------
10

SELECT COALESCE(NULL, NULL, 'Третий текст') AS Result;

Result
--------------
Третий текст

Использование с NULL аргументами:

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

Result
-------
NULL

Использование с колонками таблицы:

CREATE TABLE #Temp (Phone1 VARCHAR(10), Phone2 VARCHAR(10));
INSERT INTO #Temp VALUES (NULL, '9031112233'), ('4951234567', NULL);
SELECT Phone1, Phone2, COALESCE(Phone1, Phone2, 'Не указан') AS ContactPhone FROM #Temp;

Phone1      Phone2      ContactPhone
----------  ----------  --------------
NULL        9031112233  9031112233
4951234567  NULL        4951234567

Похожие функции в MS SQL Server

ISNULL(expression, replacement_value): функция, специфичная для SQL Server. Принимает только два аргумента. Возвращает replacement_value, если expression равен NULL. Тип возвращаемого значения соответствует типу первого аргумента, что может приводить к усечению данных. Работает быстрее COALESCE в простых случаях, но менее гибкая.

NULLIF(expression1, expression2): возвращает NULL, если expression1 равно expression2, иначе возвращает expression1. Полезен для преобразования конкретных значений в NULL, которые затем можно обработать COALESCE.

CASE: конструкция CASE предлагает максимальную гибкость для условной логики и может эмулировать поведение COALESCE: CASE WHEN expr1 IS NOT NULL THEN expr1 WHEN expr2 IS NOT NULL THEN expr2 ... END. Используется, когда нужны более сложные условия, чем просто проверка на NULL.

Предпочтительнее использовать COALESCE при работе с несколькими аргументами и для написания кода, совместимого со стандартом ANSI. ISNULL подходит для быстрой замены NULL на конкретное значение в среде SQL Server.

Аналоги функции в других СУБД

MySQL: поддерживает как COALESCE, так и функцию IFNULL(expr1, expr2), аналогичную ISNULL. Пример:

SELECT COALESCE(NULL, 'MySQL', 'Backup') AS Result;

Result
------
MySQL

Oracle: использует NVL(expr1, expr2) для двух аргументов и COALESCE для нескольких. Также есть NVL2(expr1, expr2, expr3), которая возвращает expr2, если expr1 не NULL, иначе expr3.

SELECT NVL(NULL, 'Oracle NVL') AS Result FROM DUAL;

Result
----------
Oracle NVL

PostgreSQL: полностью поддерживает стандартную COALESCE. Также есть функция NULLIF.

SELECT COALESCE(NULL, NULL, 'PostgreSQL') AS Result;

Result
----------
PostgreSQL

SQLite: поддерживает COALESCE и IFNULL(expr1, expr2).

SELECT COALESCE(NULL, 'SQLite') AS Result;

Result
------
SQLite

Sybase ASE: поддерживает ISNULL (с двумя аргументами) и COALESCE. Поведение аналогично MS SQL Server.

Типичные ошибки при использовании

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

DECLARE @DateVal DATE = '2023-01-01';
DECLARE @IntVal INT = 5;
SELECT COALESCE(@DateVal, @IntVal) AS Result;

Ошибка: Ошибка преобразования типа данных.

Использование с недопустимыми аргументами: передача только одного аргумента вызывает ошибку.

SELECT COALESCE(NULL) AS Result;

Ошибка: Функция coalesce требует не менее двух аргументов.

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

SELECT COALESCE(1, (SELECT 1/0)) AS Result; -- Деление на ноль все равно произойдет

Ошибка: Найдено деление на ноль.

Изменения в последних версиях

В SQL Server 2012 и более поздних версиях, включая SQL Server 2022, не было внесено фундаментальных изменений в работу функции COALESCE. Однако улучшения в оптимизаторе запросов могут влиять на производительность выполнения планов запросов, содержащих эту функцию. Для Azure SQL Database также характерна полная поддержка стандартного поведения функции.

Важным аспектом остается совместимость со стандартом ANSI, которая обеспечивается на протяжении многих версий.

Расширенные примеры применения

Использование в вычислениях для предотвращения ошибок с NULL:

DECLARE @Price DECIMAL(10,2) = NULL, @Discount DECIMAL(10,2) = 0.1;
SELECT COALESCE(@Price, 100) * (1 - COALESCE(@Discount, 0)) AS FinalPrice;

FinalPrice
----------
90.00

Динамическое формирование строки с разделителями, игнорируя NULL значения:

SELECT 
    CONCAT(
        COALESCE(FirstName + ' ', ''),
        COALESCE(MiddleName + ' ', ''),
        COALESCE(LastName, '')
    ) AS FullName
FROM (VALUES ('Иван', NULL, 'Иванов'), 
             ('Петр', 'Петрович', NULL)) AS Users(FirstName, MiddleName, LastName);

FullName
----------------
Иван Иванов
Петр Петрович

Использование с агрегатными функциями для подстановки значения по умолчанию:

CREATE TABLE #Sales (Region VARCHAR(20), Amount INT);
INSERT INTO #Sales VALUES ('North', 100), ('South', NULL), ('East', 150);
SELECT Region, COALESCE(SUM(Amount), 0) AS Total FROM #Sales GROUP BY Region;

Region   Total
-------  -----
North    100
South    0
East     150

Совместное использование с NULLIF для обработки специальных значений:

SELECT 
    ProductName,
    COALESCE(NULLIF(DiscountPercent, 0), 10) AS EffectiveDiscount -- Если скидка 0, подставить 10%
FROM (VALUES ('Product A', 5), ('Product B', 0)) AS Products(ProductName, DiscountPercent);

ProductName  EffectiveDiscount
-----------  -----------------
Product A    5
Product B    10

Использование в выражении WHERE для поиска по нескольким полям с приоритетом:

SELECT * FROM Employees 
WHERE COALESCE(WorkPhone, MobilePhone, '') LIKE '%123%';

Пояснение: Поиск подстроки '123' сначала в рабочем телефоне, затем в мобильном. Если оба NULL, строка не попадет в результат.