Синтаксический сахар SQL: функция COALESCE

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

Итак, начнем с самого простого - что такое COALESCE?

Определение и описание функции COALESCE

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

Пример:

SELECT COALESCE(NULL, NULL, 1, 2, NULL, 3)
# 1

Этот запрос вернет 1, потому что первые два аргумента NULL, а третий аргумент принимает значение отличное от NULL. Соответственно, выражение COALESCE даже не будет смотреть дальше - главное, что 3 аргумент не NULL.

Другие примеры:

SELECT COALESCE(1, NULL, 2, NULL)
# 1

или

SELECT COALESCE(NULL, NULL)
# NULL

Наверно, вы поняли основной смысл. Если можно вернуть не NULL - возвращается первое непустое значение. Если нельзя - возвращается NULL.

Тут и ниже мы рассматриваем примеры использования COALESCE в PostgreSQL, но стоит отметить, что эта функция используется абсолютно идентично во всех остальных популярных СУБД: MS SQL Server, Oracle и MySQL.

Давайте теперь копнем чуть глубже и посмотрим, как же эта функция устроена.

Аналогия COALESCE с CASE

Мы разобрались, как выражение COALESCE работает, но сможем ли мы написать его с нуля? Допустим, нам дали задание:

Не используя COALESCE, напишите SELECT-запрос, который будет аналогичен запросу SELECT COALESCE(expression1, expression2, expression3, expression4)

Сможете решить такую задачу сходу? На самом деле, её решение довольно простое: COALESCE - это просто удобная обертка для конструкции CASE. Ее удобно использовать для обработки значений NULL. Именно поэтому функцию COALESCE в SQL можно считать хорошим примером синтаксического сахара.

Синтаксический сахар (syntactic sugar) в программировании - это термин, описывающий удобные языковые конструкции, которые делают код более читаемым и коротким, но не добавляют новых возможностей. Это упрощенные способы выражения того, что могло бы быть написано более громоздко, при этом они не меняют семантику кода.

Учитывая, что обработка пропусков - типовая задача при написании SQL-запросов, решили выделить эту конструкцию в отдельную функцию COALESCE, чтобы бедные разработчики не мучались каждый раз и не громоздили огромные CASE-выражения.

Давайте же и мы с вами решим эту задачу. Ответ будет такой:

SELECT 
    CASE
        WHEN (expression1 IS NOT NULL) THEN expression1
        WHEN (expression2 IS NOT NULL) THEN expression2
        WHEN (expression3 IS NOT NULL) THEN expression3
        ELSE expression4
    END

Вот, собственно, и все премудрости. Согласитесь - не сложно. Но значительно удобней писать COALESCE, чем каждый раз записывать такую объемную конструкцию с CASE.

Боевая задача

Давайте на реальном примере рассмотрим, когда использование COALESCE - не просто желание побаловаться, а реальная необходимость.

Вы можете сказать:

Я столько времени пишу SQL-запросы и ни разу не пользовался COALESCE!

Ну, что же, возможно Вам просто везло… Но с высокой долей вероятности некоторые Ваши запросы начнут выдавать неправильные результаты, если таблицы начнут заполняться «некрасивыми» данными.

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

+--------+----------+-----------+--------------+
|   ID   |   name   |   bonus   |     date     |
+--------+----------+-----------+--------------+
|   1    |   Ivan   |   NULL    |  2020-01-01  |
|   1    |   Maya   |   3500    |  2020-01-03  |
|   1    |   Dora   |   4500    |  2020-01-02  |
|   1    |   Petr   |   5750    |  2020-02-01  |
|   1    |   Ivan   |   3220    |  2020-03-05  |
|                   ...                        |
+--------+----------+-----------+--------------+

Так получилось, что у сотрудника Ivan не прогрузился бонус за январь и в таблице стоит значение NULL. Соответственно, приведенный ниже запрос вернет 3220 для этого сотрудника.

SELECT name, SUM(bonus)
FROM table_name
GROUP BY name

Мало того, что результат получился неверным - мы об этом даже не узнаем. А если записей много, то различные расчеты и агрегации постоянно будут приводить к скрытым ошибкам (проверено на практике).

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

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

В нашем же случае решение можно записать так:

SELECT name, SUM(COALESCE(bonus, 1000000))
FROM table_name
GROUP BY name

Еще примеры SQL-запросов с использованием функции COALESCE

Замена NULL на метку

Рассмотрим такую простую ситуацию: в таблице users есть поле phone. Если номер телефона отсутствует, нужно заменить значение на "Unknown".

SELECT user_id,
    COALESCE(phone, 'Unknown') AS phone_status
FROM users;

На выходе получаем такую таблицу:

+----------+--------------+
| user_id  | phone_status |
+----------+--------------+
| 1        | +79112223344 |
| 2        | Unknown      |
| 3        | +79211234567 |
| 4        | Unknown      |
+----------+--------------+

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

Рассмотрим таблицу orders с данными о заказах, которая содержит id заказа, дату его создания, дату оплаты и дату отправки.

+--------+----------------+----------------+----------------+
|   id   |   order_date   |  payment_date  |  shipped_date  |
+--------+----------------+----------------+----------------+
|   1    |   2024-01-01   |      NULL      |      NULL      |
|   2    |   2024-01-01   |   2024-01-02   |      NULL      |
|   3    |   2024-01-02   |   2024-01-02   |   2024-01-04   |
|   4    |   2024-01-02   |   2024-01-05   |      NULL      |
|   5    |   2024-01-03   |   2024-01-03   |   2024-03-05   |
|                            ...                            |
+--------+----------------+----------------+----------------+

Мы видим, что не все заказы оплачены, а из тех, которые оплачены - не все отправлены. Допустим, что мы хотим вывести дату последней активности по каждому заказу. Чтобы сделать это быстро и красиво, мы можем использовать функцию COALESCE:

SELECT id,
       COALESCE(shipped_date, payment_date, order_date) AS last_date
FROM orders;

В результате получаем такую таблицу:

+--------+--------------+
|   ID   |  last_date   | 
+--------+--------------+
|   1    |  2024-01-01  |
|   2    |  2024-01-02  |
|   3    |  2024-01-04  |
|   4    |  2024-01-05  |
|   5    |  2024-01-05  | 
|           ...         |
+--------+--------------+

Использование COALESCE при конкатенации NULL и строк

Рассмотрим сценарий: таблица products содержит названия товаров (product_name) и категории (category). Некоторые товары не имеют названия или категории. Нужно вывести название и категорию в одном поле, используя разделитель " - ":

SELECT 
    product_id,
    COALESCE(product_name, 'Unnamed Product') || ' - ' || COALESCE(category, 'No Category') AS product_info
FROM products;

В результате получим такую таблицу:

+------------+-------------------------------+
| product_id |          product_info         |
+------------+-------------------------------+
| 1          | Laptop - Electronics          |
| 2          | Table - Furniture             |
| 3          | Sofa - No Category            |
| 4          | Unnamed Product - Electronics |
+------------+-------------------------------+

Использование COALESCE для замены NULL вычисленным значением

Представим, что таблица orders содержит информацию о стоимости и скидке заказов (столбцы discount и price).

+----------+-------+----------+
| order_id | price | discount |
+----------+-------+----------+
| 1        | 1 000 | NULL     |
| 2        | 2 000 | 100      |
| 3        | 1 500 | NULL     |
| 4        | 4 000 | 500      |
+----------+-------+----------+

Если скидка заказа не указана, она должна быть рассчитана как 10% от цены. Используем такой запрос:

SELECT 
    order_id,
    price,
    COALESCE(discount, price * 0.1) AS final_discount
FROM orders;

В результате получим такую таблицу:

+----------+-------+----------------+
| order_id | price | final_discount |
+----------+-------+----------------+
| 1        | 1 000 | 100            |
| 2        | 2 000 | 100            |
| 3        | 1 500 | 150            |
| 4        | 4 000 | 500            |
+----------+-------+----------------+

Эпилог

Мы рассмотрели функцию COALESCE и показали её важность. Посмотрите на те запросы, которые вы пишите каждый день - нет ли там места ошибке? Может и у Вас могут возникнуть какие-то ситуации, когда расчет будет неправильным, а вы даже не сможете этого увидеть? Чтобы избежать таких ситуаций, используйте COALESCE!