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

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 в более сложных запросах)

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

краткое описание, особенности использования, примеры

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

Вывод непустых значений с помощью Coalesce. Особенности

Рассмотрим Sql Coalesce особенности использования:

1. Допускает указание любого количества аргументов (в отличие от Isnull/Nvl/Nvl2, число аргументов в которых ограничено).
2. Может принимать в качестве аргумента подзапросы.
3. Возвращает результат, равный первому отличному от Null значению, либо Null, если отличного от Null значения не будет найдено.
4. Sql Coalesce может использоваться в предложении Select для выбора непустого значения, а также в Where для уточнения, что набор столбцов с пустыми значениями не допускается (/допускается).
5. Данное выражение равнозначно применению выражения Case, проверяющему последовательно каждый аргумент на условие When argument1 is not null then argument1. По сути, Coalesce это «ярлык», созданный для удобства использования, и во многих СУБД оптимизаторы запросов переписывают выражение Coalesce на Case.
6. Sql Coalesce функции имеются во всех ведущих реляционных системах управления базами данных.

Основные операторы SQL

Обзор технологии СУБД SQL и описание основных операторов для создания, управления, обработки данных…

Синтаксис Coalesce

Каждый, кто хоть раз применял Coalesce при составлении sql-запросов, знает, что синтаксис данного выражения предельно прост. Достаточно в круглых скобках указать аргументы, проверяемые на Null, через запятую. Если допустим, что аргументы имеют имена arg1, arg2, … argN, то синтаксис Coalesce будет иметь следующий вид:

Coalesce(arg1, arg2, … argN).

Подготовим несколько таблиц для изучения механизма работы данного выражения.

Подготовка таблиц

Чтобы лучше понять Coalesce sql описание, создадим в базе данных две таблицы, содержащие информацию по объектам недвижимости.

Первая таблица Area пусть содержит наименования объектов недвижимости и их площадь. Площадь может быть уточненной (area_yt) либо декларированной (area_decl).

Вторая таблица Basic_characteristic пусть содержит информацию по основной характеристике объекта недвижимости – протяженность (Extension), глубина (Depth), площадь (Area), объем (Scope), высота (Height).

Рассмотрели синтаксис Coalesce sql, описание, особенности использования и перейдем непосредственно к примерам.

Примеры использования

Синтаксис у выражения Coalesce предельно прост, однако важно не забывать, что результатом выполнения команды станет ПЕРВОЕ непустое найденное значение из списка аргументов. Это замечание имеет очень большое значение, поэтому аргументы в выражении необходимо расставлять в порядке их важности. Проще всего понять принцип по таблице площадей. Составьте запрос, выбирающий наименование объекта недвижимости, а также значение площади:

Declare SQL: краткое описание. Transact-SQL

Transact-SQL — это расширение, которое используется в SQL Server. Данная разработка тесно…

Использование функции 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)
В РЕЗУЛЬТАТЕ ; 

Вывод:

9005 1

Пример 3:

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

Вывод:

Пример 4:

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

Вывод:

9005 1

Пример-5:

ВЫБЕРИТЕ ОБЪЕДИНЕНИЕ (NULL, NULL, NULL, NULL, 5, ‘GFG’) КАК РЕЗУЛЬТАТ ;

Вывод:
при выполнении запросов в 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 ; 

Вывод:

Полное имя
Маной М. Кумар
NULL
Payal K. Chauan К. Маан

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

Пример-9:

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

Вывод:

Функция SQL Coalesce

В этом разделе мы описали о функции объединения с подробным примером.

Функция 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.