Синтаксический сахар 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

Еще пример использования функции 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 и показали её важность. Посмотрите на те запросы, которые вы пишите каждый день - нет ли там места ошибке? Может и у Вас могут возникнуть какие-то ситуации, когда расчет будет неправильным, а вы даже не сможете этого увидеть? Чтобы избежать таких ситуаций, используйте COALESCE!