Sql coalesce пример: COALESCE (Transact-SQL) — SQL Server

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

SQL возвращает первое не-NULL значение

Навигация по уроку

• COALESCE для простой замены NULL-значения
• COALESCE для выбора альтернативы
• COALESCE помогает избежать неопределённости в вычислениях

Связанные темы

• Оператор SELECT
• Подзапросы

Функция SQL COALESCE возвращает первое определённое, то есть не-NULL значение из списка её аргументов. Обычно
одним или несколькими аргументами функции COALESCE является столбец таблицы, к которой обращён запрос. Нередко
аргументом функции является и подзапрос. Это делается тогда, когда невозможно
с уверенностью утверждать, что подзапрос вернёт какое-либо определённое значение, например, 5, «строка», ‘2018-12-09’ и
т. д., а не значение NULL. Тогда это NULL-значение будет заменено на следующее сразу же за ним определённое значение.

Приведём простые примеры пока без имён столбцов и подзапросов.

COALESCE (NULL, 7, 9) // Вернёт 7
COALESCE (NULL, ‘Не найдено’) // Вернёт ‘Не найдено’
COALESCE (‘2017-10-20’, NULL, ‘2018-03-08’) // Вернёт ‘2018-03-08’

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

В первых примерах работаем с базой данных библиотеки и её таблицей «Книга в выдаче» (BOOKINUSE). Операции
будут относиться к столбцам Author (автор книги) и Title (название книги).

Если вы хотите выполнить запросы к базе данных из этого урока на MS SQL Server, но эта СУБД
не установлена на вашем компьютере, то ее можно установить, пользуясь инструкцией по этой ссылке.

Скрипт для создания базы данных библиотеки, её таблиц и заполения таблиц данными —
в файле по этой ссылке.

Пример 1. Есть база данных библиотеки и таблица «Книга в выдаче»
(BOOKINUSE). Таблица выглядит так:

Author	Title	Pubyear	Inv_No	Customer_ID
Толстой	Война и мир	2005	28	65
Чехов	Вишневый сад	2000	17	31
Чехов	Избранные рассказы	2011	19	120
Чехов	Вишневый сад	1991	5	65
Ильф и Петров	Двенадцать стульев	1985	3	31
Маяковский	Поэмы	1983	2	120
Пастернак	Доктор Живаго	2006	69	120
Толстой	Воскресенье	2006	77	47
Толстой	Анна Каренина	1989	7	205
Пушкин	Капитанская дочка	2004	25	47
Гоголь	Пьесы	2007	81	47
Чехов	Избранные рассказы	1987	4	205
Пушкин	Сочинения, т. 1	1984	6	47
Пастернак	Избранное	2000	137	18
Пушкин	Сочинения, т.2	1984	8	205
NULL	Наука и жизнь 9 2018	2019	127	18
Чехов	Ранние рассказы	2001	171	31

Как видим, в последней строке отсутствует определённное значение столбца Author,
так как выданное издание является журналом. Пусть требуется вывести авторов выданных изданий с определёнными
инвентарными номерами и при этом ни одно из полей не должно быть пустым. Для этого пишем запрос
с использованием фукнции COALESCE:

SELECT COALESCE (Author, ‘Журнал’)
AS InUse
FROM Bookinuse
WHERE inv_no IN (25, 81, 127)

Для издания с инвентарным номером 127 будет возвращено первое не-NULL значение —
‘Журнал’ и результирующая таблица будет выглядеть так:

• Страница 2 (функция COALESCE в более сложных запросах)

В информационных системах почти никогда не допускаются пустые строки как результат запроса. Если что-то,
что было указано в запросе, отстустствует, то в результирующей строке должно быть указано 0, если речь
идёт о количестве, либо «Отсутствует», если требуется текстовый ответ, либо другой подходящий по
типу данных результат.

Пример 2. Вновь работаем с таблицей BOOKINUSE базы данных
библиотеки. Требуется вывести количество изданий определённого автора, находящихся в выдаче. В таблице
видим, что в выдаче находится одна книга Пушкина. Проверяем. Пишем следующий запрос с использованием
функции COALESCE:

SELECT COALESCE ((SELECT COUNT(*)
FROM Bookinuse
WHERE Author=’Пушкин’), 0)
AS InUse

Результат выполнения этого запроса:

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

SELECT COALESCE ((SELECT COUNT(*)
FROM Bookinuse
WHERE Author=’Булгаков’), 0)
AS InUse

Результат выполнения этого запроса:

Таким образом, функция COALESCE вернула первое не-NULL значение: 0 и вместо пустой
строки мы получили строку со значением 0.

• Страница 2 (функция COALESCE в более сложных запросах)

Нередко некоторое результирующее значение основывается в зависимости от случая на значениях разных
столбцов таблицы. Тогда, как правило, не участвующие в формировании результирующего значения значения
столбца пусты. Для выбора необходимого столбца применяется функция COALESCE.

Пример 3. В базе данных фирмы есть таблица STAFF, по данным которой
можно вычислить годовой доход сотрудника.

Если сотрудник получает фиксированную заработную плату
(Salary), то значения столбцов Комиссионные (Comm) и Сделки (Sales) пусты (NULL). В таком случае для получения
годового дохода следует размер заработной платы умножить на 12. Если же сотрудик
получает комиссионные, то значение столбца Salary пусто (NULL). Возможны также случаи, когда сотруднику
назначены комиссионные, но он не провёл ни одной сделки. Тогда значение столбца Sales пусто (NULL).
В первом случае функция COALESCE возвращает значение Salary*12, во втором — Comm*Sales, в третьем — 0.
Итак, для вычисления годового дохода сотрудников пишем следующий запрос с использованием функции COALESCE:

SELECT LName,
COALESCE (Salary*12, Comm*Sales, 0)
AS Income
FROM STAFF

Результатом выполнения запроса будет следующая таблица:

В соединениях таблиц часто невозможно заранее предположить, всем ли значениям некоторого столбца из
одной таблице соответствует определённое значение из другой таблице. В случае несоответствия значение
является неопределённым (NULL). Но именно на основании этого значения должны производиться дополнительные
вычисления. Другая причина, по которой в сложных вычислениях часто применяется функция COALESCE,
состоит в том, что запрещается применять агрегатные функции от агрегатной функции, например, SUM(COUNT(*).

Работаем с базой данных «Театр». Таблица Play содержит данные о постановках. Таблица Team —
о ролях актёров. Таблица Actor — об актёрах. Таблица Director — о режиссёрах. Поля таблиц, первичные
и внешние ключи можно увидеть на рисунке ниже (для увеличения нажать левой кнопкой мыши).

Пример 4. В таблице Team есть столбец MainTeam, содержащий данные
о том, является ли роль главной. Если является, то значение столбца — Y, если нет — N. Требуется вывести
список актёров с фамилиями и количеством второстепенных ролей.

Потребуется соединение таблиц. Как мы уже замечали, в соединении таблиц Play (постановка)
и Team (роль) некоторые значения столбцов могут быть неопределёнными из-за того, что не у всех актёров в
каждой постановке обязательно есть и главые, и второстепенные роли. Кроме того, в качестве количества
второстепенных ролей требуется подсчитать сумму (SUM) числа строк (COUNT(*)), соответствующих определённому актёру, в которых
указано, что роль является второстепеннной. Но использование вложенных агрегатных функций запрещено.
В этом случае пишется запрос с применением функции COALESCE, возвращаемое которой значение уже
формально не является значением агрегатной функции:

SELECT a.LName AS Name,
SUM (COALESCE((SELECT COUNT(*)
FROM ACTOR a1
JOIN team t
ON a1.Actor_ID-t.ACTOR_ID
WHERE a1.Actor_ID=a.Actor_ID
AND t.MainTeam=’N’
GROUP BY a1.Actor_ID), 0)) AS NumSecRole
FROM ACTOR a
JOIN team t
ON a.Actor_ID=t.ACTOR_ID
JOIN Play p
ON t.PLAY_ID=p.Play_ID
ORDER BY a.Actor_ID

• Страница 2 (функция COALESCE в более сложных запросах)

Поделиться с друзьями

Использование функции COALESCE() в SQL Server

Введение:
Функция SQL Server COALESCE() полезна для обработки значений NULL. Значения NULL заменяются заданным пользователем значением в процессе оценки значения выражения. Функция SQL Server Coalesce оценивает выражение в определенном порядке и всегда возвращает первое ненулевое значение из определенного списка выражений.

Синтаксис:

 COALESCE ( exv1, exv2..., exvN )
 

Где –
exv1, exv2…, exvN — значения выражений.

Свойства синтаксиса функции объединения SQL Server:

• Все выражения должны иметь одинаковый тип данных.
• Может иметь несколько выражений.

Пример-1:

ВЫБЕРИТЕ ОБЪЕДИНЕНИЕ (НУЛЬ, 'X', 'Y')
В РЕЗУЛЬТАТЕ ; 

Вывод:

Пример 2:

 ВЫБЕРИТЕ ОБЪЕДИНЕНИЕ (NULL, 13, 24, 35, 46)
В РЕЗУЛЬТАТЕ ; 

Выход:

Пример-3:

 SELECT (NULL, NULL, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null. 
В РЕЗУЛЬТАТЕ ; 

Вывод:

Пример 4:

 SELECT COALESCE (NULL, NULL, NULL, NULL, NULL, 'GFG')
В РЕЗУЛЬТАТЕ ; 

Output :

Example-5 :

SELECT COALESCE (NULL, NULL, NULL, NULL, 5, ‘GFG’) AS РЕЗУЛЬТАТ ;

Вывод:
при выполнении запросов в SQL Server Management Studio.

Пример-6:

 ВЫБЕРИТЕ ОБЪЕДИНЕНИЕ
(NULL, NULL, NULL, NULL, NULL, 'GFG', 1) 

Вывод:

Использование функции SQL Server Coalesce в операции конкатенации строк:
Предположим, у нас есть имя таблицы «GeekName». ».

Пример-7:
Вывод:

 Выберите * из GeekName; 

Пример-8:

 SELECT F_Name + ' ' +M_Name+ ' '
+ L_Name Полное имя FROM GeekName ; 

Output :

Использование функции сервера SQL под названием COALESCE для обработки значений NULL:
Оператор SQL объединит все три имени, но значения NULL не появятся в выходных данных.

Пример-9:

 SELECT F_Name +' '+COALESCE(M_Name, '') +' '
+ L_Name  FullName  FROM GeekName ; 

Вывод:

SQL Coalesce function

In this topic, we described о функции объединения с подробным примером.

Функция COALESCE используется для получения первых NOT NULL VALUES в списке значений, которые содержат значения NULL. Используя функцию COALESCE, мы можем указать значения NULL в наборе результатов в существующей таблице. Функция COALESCE работает в SQL SERVER 2008 и более поздних версиях.

Синтаксис —

 SELECT COALESCE(выражение1, выражение2|значение) ИЗ таблицы;
 

• выражение1 — представляет имя выражения или имя столбца, для которого мы хотим проверить значения NULL.
• выражение2 — представляет имя выражения или имя столбца, значения которого мы хотим вставить в столбец, допускающий значение NULL.
• Значение — представляет значение, которое нам нужно указать в требуемом столбце, имеющем значения NULL.

Пример –

Давайте рассмотрим приведенную ниже таблицу (таблицы) в качестве примера таблицы (таблиц) для создания SQL-запроса для получения желаемых результатов.

employee_details —

emp_id	emp_name	обозначение	идентификатор_менеджера	date_of_hire	зарплата	dept_id
001	Сотрудник1	Директор		11.07.2019	45000,00	1000
002	Сотрудник2	Директор		11.07.2019	40000,00	2000
003	Сотрудник3	Менеджер	Сотрудник1	11. 07.2019	27000,00	1000
004	Сотрудник4	Менеджер	Сотрудник2	08.10.2019	25000,00	2000
005	Сотрудник5	Аналитик	Сотрудник3	11.07.2019	20000,00	1000
006	Сотрудник6	Аналитик	Сотрудник3	2019-10-08	18000.00	1000
007	Сотрудник7	Клерк	Сотрудник3	11.07.2019	15000,00	1000
008	Сотрудник8	Продавец	Сотрудник4	09.09.2019	14000.00	2000
009	Сотрудник9	Продавец	Сотрудник4	08.10.2019	13000.00	2000

Сценарий — Замена значений NULL в существующей таблице указанным значением в результирующем наборе с помощью функции COALESCE.

Требование — Замена значений NULL в employee_details указанным значением в результирующем наборе с помощью функции COALESCE. Запрос был следующим:

 SELECT emp_id, emp_name, COALESCE (manager_id, 'Employee0')manager_id
ОТ employee_details;
 

Выполнив вышеуказанный запрос, мы можем получить указанное значение в столбце manager_id в таблице employee_details, поскольку он содержит значения NULL. Результат был следующим:

Сценарий — если мы пытаемся поместить указанные значения в столбец NOT NULL в существующей таблице с помощью функции COALESCE.

Требование — Если мы пытаемся указать при обозначении столбец NOT NULL в employee_details с помощью Analyst с помощью функции COALESCE. Запрос был следующим:

 SELECT emp_id, emp_name, COALESCE (обозначение, «Аналитик»)обозначение
ОТ employee_details;
 

Выполнив приведенный выше запрос, мы можем получить существующие значения обозначений вместо получения указанного значения Analyst, поскольку столбец обозначений не имеет значений NULL. Результат был следующим:

Сценарий . Если мы пытаемся объединить несколько столбцов, имеющих значения NULL, получаем без значений NULL в наборе результатов в существующей таблице с помощью функции COALESCE.

Требование — Если мы пытаемся добавить столбцы emp_id, emp_name, manager_id, обозначение в таблицу employee_details без получения значений NULL с помощью функции COALESCE. Запрос был такой —

 SELECT emp_name+ COALESCE(manager_id, ' ')+ обозначение 'ИМЯ С
ОБОЗНАЧЕНИЕ ОТ employee_details;
 

Выполнив вышеуказанный запрос, мы можем получить вывод без значений NULL в результирующем наборе из таблицы employee_details. Вывод был следующим —

Сценарий — Замена значений NULL в существующей таблице другим указанным значением столбца в результирующем наборе с помощью COALESCE.