SQL Server ОБЪЕДИНЕНИЕ

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

Введение в выражение COALESCE SQL Server

Выражение COALESCE SQL Server принимает несколько аргументов, оценивает их последовательно и возвращает первый ненулевой аргумент.

Ниже показан синтаксис выражения COALESCE:

COALESCE(e1,[e2,...,en])

В этом синтаксисе e1, e2, … en — скалярные выражения, которые вычисляются как скалярные значения. Выражение COALESCE возвращает первое ненулевое выражение. Если все выражения вычисляются как NULL, то выражение COALESCE возвращает NULL;

Поскольку COALESCE является выражением, его можно использовать в любом предложении, которое принимает выражение, например SELECT, WHERE, GROUP BY и HAVING.

Примеры выражений COALESCE для SQL Server

Давайте рассмотрим практические примеры использования выражения COALESCE.

A) Пример использования выражения COALESCE SQL Server с данными символьной строки

В следующем примере выражение COALESCE используется для возврата строки «Hi», поскольку это первый ненулевой аргумент:

SELECT 
    COALESCE(NULL, 'Hi', 'Hello', NULL) result;

Вот что получилось:

result
------
Hi
(1 row affected)

B) Использование выражения COALESCE SQL Server с примером числовых данных

В этом примере выражение COALESCE используется для оценки списка аргументов и возврата первого числа:

SELECT 
    COALESCE(NULL, NULL, 100, 200) result;

Вывод следующий:

result
-----------
100
(1 row affected)

C) Использование выражения COALESCE SQL Server для замены NULL новыми значениями

См. следующую таблицу sales.customers из примера базы данных.

Следующий запрос возвращает имя, фамилию, телефон и адрес электронной почты всех клиентов:

SELECT 
    first_name, 
    last_name, 
    phone, 
    email
FROM 
    sales.customers
ORDER BY 
    first_name, 
    last_name;

Вот частичный вывод:

Столбец «Телефон» будет содержать значение NULL, если у клиента нет номера телефона, записанного в таблице sales.customers.

Чтобы сделать вывод более удобным для бизнеса, можно использовать выражение COALESCE для замены NULL на строку N/A(недоступно), как показано в следующем запросе:

SELECT 
    first_name, 
    last_name, 
    COALESCE(phone,'N/A') phone, 
    email
FROM 
    sales.customers
ORDER BY 
    first_name, 
    last_name;

На следующем рисунке показан частичный вывод:

D) Использование выражения COALESCE SQL Server для использования доступных данных

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

CREATE TABLE salaries(
    staff_id INT PRIMARY KEY,
    hourly_rate decimal,
    weekly_rate decimal,
    monthly_rate decimal,
    CHECK(
        hourly_rate IS NOT NULL OR 
        weekly_rate IS NOT NULL OR 
        monthly_rate IS NOT NULL)
);

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

Во-вторых, вставьте несколько строк в таблицу зарплат:

INSERT INTO 
    salaries(
        staff_id, 
        hourly_rate, 
        weekly_rate, 
        monthly_rate
    )
VALUES
   (1,20, NULL,NULL),
   (2,30, NULL,NULL),
   (3,NULL, 1000,NULL),
   (4,NULL, NULL,6000);
   (5,NULL, NULL,6500);

В-третьих, запросите данные из таблицы зарплат:

SELECT
    staff_id, 
    hourly_rate, 
    weekly_rate, 
    monthly_rate
FROM
    salaries
ORDER BY
    staff_id;

Вот что получилось:

В-четвертых, рассчитайте ежемесячно для каждого сотрудника, используя выражение COALESCE, как показано в следующем запросе:

SELECT
    staff_id,
    COALESCE(
        hourly_rate*22*8, 
        weekly_rate*4, 
        monthly_rate
    ) monthly_salary
FROM
    salaries;

На следующем рисунке показан результат:

В этом примере мы использовали выражение COALESCE, чтобы использовать только ненулевые значения, найденные в столбцах hourly_rate, weekly_rate и month_rate.

COALESCE против выражения CASE

Выражение COALESCE представляет собой синтаксический сахар выражения CASE.

Следующие выражения возвращают тот же результат:

COALESCE(e1,e2,e3)
CASE
    WHEN e1 IS NOT NULL THEN e1
    WHEN e2 IS NOT NULL THEN e2
    ELSE e3
END

Обратите внимание, что оптимизатор запросов может использовать выражение CASE для переписывания выражения COALESCE.

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