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

Если Вы работаете с SQL, то скорее всего Вы сталкивались с выражением 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 и так далее.

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

Аналогия с CASE

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

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

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

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