Функция COALESCE: SQL возвращает первое не-NULL значение
Функция 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’
COALESCE для простой замены NULL-значения
При создании таблицы базы данных можно для ряда столбцов предусмотреть значения по умолчанию 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 значение — ‘Журнал’ и результирующая таблица будет выглядеть так:
| InUse |
| Пушкин |
| Гоголь |
| Журнал |
В информационных системах почти никогда не допускаются пустые строки как результат запроса. Если что-то, что было указано в запросе, отстустствует, то в результирующей строке должно быть указано 0, если речь идёт о количестве, либо «Отсутствует», если требуется текстовый ответ, либо другой подходящий по типу данных результат.
Пример 2. Вновь работаем с таблицей BOOKINUSE базы данных библиотеки. Требуется вывести количество изданий определённого автора, находящихся в выдаче. В таблице видим, что в выдаче находится одна книга Пушкина. Проверяем. Пишем следующий запрос с использованием функции COALESCE:
SELECT COALESCE (( SELECT COUNT (*) FROM Bookinuse WHERE Author=’Пушкин’), 0) AS InUse
Результат выполнения этого запроса:
| InUse |
| 3 |
Но среди выданных изданий отстутствуют книги Булгакова. Проверяем. Пишем аналогичный запрос, меняем лишь автора:
SELECT COALESCE (( SELECT COUNT (*) FROM Bookinuse WHERE Author=’Булгаков’), 0) AS InUse
Результат выполнения этого запроса:
| InUse |
| 0 |
Таким образом, функция COALESCE вернула первое не-NULL значение: 0 и вместо пустой строки мы получили строку со значением 0.
COALESCE для выбора альтернативы
Нередко некоторое результирующее значение основывается в зависимости от случая на значениях разных столбцов таблицы. Тогда, как правило, не участвующие в формировании результирующего значения значения столбца пусты. Для выбора необходимого столбца применяется функция COALESCE.
Пример 3. В базе данных фирмы есть таблица STAFF, по данным которой можно вычислить годовой доход сотрудника.
| ID | LName | Salary | Comm | Sales |
| 1 | Johnson | 12300 | NULL | NULL |
| 2 | Brown | NULL | 600 | 24 |
| 3 | MacGregor | 1420 | NULL | NULL |
| 4 | Calvin | NULL | 780 | 18 |
| 5 | Levy | 11400 | NULL | NULL |
| 6 | Right | NULL | 800 | NULL |
Если сотрудник получает фиксированную заработную плату (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
Результатом выполнения запроса будет следующая таблица:
| LName | Income |
| Johnson | 147600 |
| Brown | 14400 |
| MacGregor | 170400 |
| Calvin | 14040 |
| Levy | 136800 |
| Right | 0 |
COALESCE помогает избежать неопределённости в вычислениях
В соединениях таблиц часто невозможно заранее предположить, всем ли значениям некоторого столбца из одной таблице соответствует определённое значение из другой таблице. В случае несоответствия значение является неопределённым (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
Какой функции нет в sql coalesce
Для генерации объекта UNIQUEIDENTIFIER, то есть некоторого уникального значения, используется функция NEWID() . Например, мы можем определить для столбца первичного ключа тип UNIQUEIDENTIFIER и по умолчанию присваивать ему значение функции NEWID:
CREATE TABLE Clients ( Id UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(), FirstName NVARCHAR(20) NOT NULL, LastName NVARCHAR(20) NOT NULL, Phone NVARCHAR(20) NULL, Email NVARCHAR(20) NULL ) INSERT INTO Clients (FirstName, LastName, Phone, Email) VALUES ('Tom', 'Smith', '+36436734', NULL), ('Bob', 'Simpson', NULL, NULL)
ISNULL
Функция ISNULL проверяет значение некоторого выражения. Если оно равно NULL, то функция возвращает значение, которое передается в качестве второго параметра:
ISNULL(выражение, значение)
Например, возьмем выше созданную таблицу и применим при получении данных функцию ISNULL:
SELECT FirstName, LastName, ISNULL(Phone, 'не определено') AS Phone, ISNULL(Email, 'неизвестно') AS Email FROM Clients
COALESCE
Функция COALESCE принимает список значений и возвращает первое из них, которое не равно NULL:
COALESCE(выражение_1, выражение_2, выражение_N)
Например, выберем из таблицы Clients пользователей и в контактах у них определим либо телефон, либо электронный адрес, если они не равны NULL:
SELECT FirstName, LastName, COALESCE(Phone, Email, 'не определено') AS Contacts FROM Clients
То есть в данном случае возвращается телефон, если он определен. Если он не определен, то возвращается электронный адрес. Если и электронный адрес не определен, то возвращается строка «не определено».
SQL-Ex blog
Иногда имеются веские основания использовать либо COALESCE, либо ISNULL в силу того, что они имеют разные возможности, поведение и поддержку в разных базах данных.
Однако isnull имеет несколько особых возможностей, представляющихся интересными, несмотря на её ограничения: только два аргумента, специфичен для SQL Server и, эх. мы не всегда можем указать три причины, как однажды сказал один мудрый человек.
Одна вещь делает isnull интересной в определенных сценариях. Давайте рассмотрим пару.
Первый сценарий
Во-первых, нам потребуется индекс.
CREATE INDEX party
ON dbo.Votes
(CreationDate, VoteTypeId)
INCLUDE
(UserId);
Вот несколько запросов, для которых он понадобится.
SELECT TOP (10)
u.DisplayName
FROM dbo.Users AS u
WHERE NOT EXISTS
(
SELECT
1/0
FROM dbo.Votes AS v
WHERE v.UserId = u.Id
AND v.VoteTypeId IN (1, 2, 3)
AND ISNULL(v.CreationDate, '19000101') > '20131201'
)
ORDER BY u.CreationDate DESC;
SELECT TOP (10)
u.DisplayName
FROM dbo.Users AS u
WHERE NOT EXISTS
(
SELECT
1/0
FROM dbo.Votes AS v
WHERE v.UserId = u.Id
AND v.VoteTypeId IN (1, 2, 3)
AND COALESCE(v.CreationDate, '19000101') > '20131201'
)
ORDER BY u.CreationDate DESC;
Первый запрос использует ISNULL, а второй — COALESCE.
Знаю я, знаю — я много раз здесь говорил, чтобы вы не использовали ISNULL в предложении WHERE.
Обычно, когда вы оборачиваете столбец функцией, как в данном случае, случаются плохие вещи. Поиск превращается в сканирование, вино — в воду, проблемы — в табуляторы, а лицо, которое вы запомнили с прошлого раза, превращается в ноябрьского Джека О’Лантерна.
Но в данном случае, завернутый в нашем предложении WHERE столбец, который является первый столбцом в индексе, не допускает NULL-значений.
Оптимизатор SQL Server, имея это в виду, производит план с поиском в индексе.
Проверка на NULL отбрасывается, и все заканчивается поиском значений CreationDate, о чем мы беспокоились, и остаточным предикатом на VoteTypeId.
Красота
Второй запрос, который использует coalesce, имеет несколько отличий. Давайте посмотрим план.
Вместо 157 мс этот запрос выполняется дольше одной минуты на пять секунд. Все время тратится на Top > Index Scan. У нас больше нет поиска по индексу.
Отметим, что предикат на CreationDate является полным выражением CASE, проверяющим на NULL. Это мог быть хороший сценарий, если бы мы имели поиск чего-то, однако без надлежащего индексирования и написания запросов это беда.
Причина изменения запроса заключается в том, что оптимизатор решил, что применение Row Goal будет лучше. Вот почему мы имеем Nested Loops Join и Top > Index Scan. Это не очень хорошо работает.
Это не единственный случай, когда это происходит, но, вероятно, худший.
Второй сценарий
Вы можете наблюдать это в шаблоне, против которого я часто выступаю, использование Left Join для нахождения несуществующих строк:
SELECT TOP (10)
u.DisplayName
FROM dbo.Users AS u
LEFT JOIN dbo.Votes AS v
ON v.UserId = u.Id
AND v.VoteTypeId IN (1, 2, 3)
AND ISNULL(v.CreationDate, '19000101') > '20131201'
WHERE v.Id IS NULL
ORDER BY u.CreationDate DESC;
SELECT TOP (10)
u.DisplayName
FROM dbo.Users AS u
LEFT JOIN dbo.Votes AS v
ON v.UserId = u.Id
AND v.VoteTypeId IN (1, 2, 3)
AND COALESCE(v.CreationDate, '19000101') > '20131201'
WHERE v.Id IS NULL
ORDER BY u.CreationDate DESC;
Здесь не так плохо, но все равно заметно.
План с ISNULL выглядит примерно так:
Быстро
При 163 миллисекундах тут не на что жаловаться.
Версия с COALESCE значительно хуже, всего за 1,5 секунды.
Задыхается!
Что мы узнали
В SQL Server использование функций в предложении WHERE обычно находится в черном списке. В небольшом числе случаев использование встроенной функции isnull приводит к лучшей производительности по сравнению с coalesce для столбцов, не допускающих NULL-значений.
Конечно, этого шаблона обычно следует избегать. На столбцах с NULL это всегда будет плохо. Конечно, это имеет наибольшее значение, когда функция приводит к невозможному поиску в индексе, возможному в других случаях, и мы можем использовать только сканирование индекса для поиска строк.
Еще одно соображение — это когда мы можем сначала обратиться к очень ограниченному набору строк. Скажем, мы можем сократить количество (только для объяснения) примерно до 1000 строк, например, таким предикатом Score> 10000.
Для оставшихся 1000 строк маловероятно, что дополнительный предикат, подобные которому мы здесь видели, драматически сказался бы на времени выполнения относительно простого запроса.
Однако они могут привести к плохим оценкам числа строк для более сложных запросов.
Обратные ссылки
Нет обратных ссылок
Комментарии
Показывать комментарии Как список | Древовидной структурой
Автор не разрешил комментировать эту запись
Функции COALESCE и ISNULL в T-SQL – особенности и основные отличия
В языке T-SQL есть такие функции как COALESCE и ISNULL, которые умеют проверять входящие параметры на значение NULL, сегодня мы рассмотрим особенности этих функций, а также сравним их и определим, какие между ними есть отличия.
Напомню, ранее мы с Вами уже рассмотрели основы программирования на языке T-SQL, а также составили краткий справочник по данному языку, но подробно о функциях COALESCE и ISNULL мы не разговаривали и уж тем более не сравнивали их. И так как у начинающих программистов T-SQL может сложиться впечатление, что функции COALESCE и ISNULL работают абсолютно одинаково, что на самом деле не так, я предлагаю поговорить об этих функциях более подробно, а именно узнать, какие между ними есть отличия и в каких случаях лучше использовать ту или иную функцию.
COALESCE
COALESCE – функция T-SQL, которая возвращает первое выражение из списка параметров, неравное NULL.
Синтаксис
COALESCE (входящие параметры [,…n])
Особенности
В качестве параметров могут выступать выражения любого типа данных, в том числе и подзапросы. В случае если все параметры равны NULL, то функция вернет значение NULL. Тип возвращаемого значения равняется типу данных значения с наибольшим приоритетом. Например, указано 5 параметров и все они имеют разные типы данных, то вернется первое значение отличное от NULL с типом данных, который имеет наибольший приоритет среди всех типов значений указанных в параметрах.
Как заявляют разработчики, т.е. это указанно в официальной документации, функция COALESCE является эквивалентов выражения CASE, т.е. всего лишь его синтаксическое сокращение. Для примера давайте напишем запрос, в котором будет два выражения, первое с использованием функции COALESCE, а второе с использованием CASE, которые будут работать одинаково.
DECLARE @Var1 VARCHAR(5) DECLARE @Var2 VARCHAR(5) DECLARE @Var3 VARCHAR(5) SET @Var1 = NULL SET @Var2 = NULL SET @Var3 = 'Var3' SELECT COALESCE(@Var1, @Var2, @Var3, 'Все параметры пустые') AS [COALESCE], CASE WHEN @Var1 IS NOT NULL THEN @Var1 WHEN @Var2 IS NOT NULL THEN @Var2 WHEN @Var3 IS NOT NULL THEN @Var3 ELSE 'Все параметры пустые' END AS [CASE]
ISNULL
ISNULL – функция T-SQL, которая заменяет значение NULL первого параметра, на указанное значение во втором параметре. Другими словами, в случае если первый параметр равен NULL, то возвращается второй параметр.
Синтаксис
ISNULL (check_expression, replacement_value)
- check_expression — выражение, проверяемое на NULL;
- replacement_value — выражение, возвращаемое в случае, если значение check_expression равно NULL.
Особенности
Тип данных выражения replacement_value должен явно преобразовываться к типу данных значения check_expresssion, в итоге у функции ISNULL тип возвращаемого значения равен типу данных check_expression.
Пример
DECLARE @Var1 VARCHAR(35) SET @Var1 = NULL SELECT ISNULL (@Var1, 'Значение первого параметра NULL') AS [ISNULL]
Сравнение COALESCE и ISNULL — их основные отличия
На первый взгляд может показаться, что эти функции работают одинаково за исключением того, что у функции ISNULL два параметра, а у COALESCE их может быть несколько, но помимо этого, есть и другие более важные отличия.
- Первое — это, как было уже отмечено, функция ISNULL принимает только 2 параметра, а COALESCE принимает изменяемое число параметров;
- Функция ISNULL возвращает тип данных первого параметра, поэтому замещающее значение, указанное во втором параметре, должно явно преобразовываться. В то время, как функция COALESCE возвращает тип данных значения с наибольшим приоритетом (также как и в CASE);
- Следует помнить, что выражение, возвращаемое функцией ISNULL, рассматривается SQL сервером как не NULL, а COALESCE наоборот допускающее NULL. Это можно будет заметить, например, при создании ограничения первичного ключа на вычисляемом столбце, т.е. с использованием ISNULL(NULL, 1) — это можно будет сделать, а с COALESCE(NULL, 1) нет (в примерах ниже мы это рассмотрим).
Примеры, показывающие отличия в работе функций COALESCE и ISNULL
Пример 1. Отличие в количестве параметров.
DECLARE @Var1 INT DECLARE @Var2 INT DECLARE @Var3 INT SET @Var1 = NULL SET @Var2 = NULL SET @Var3 = 1 SELECT COALESCE(@Var1, @Var2, @Var3) AS [COALESCE], ISNULL(@Var1, @Var2) AS [ISNULL]--Будет ошибка, если указать третий параметр
Пример 2. Отличие в возвращаемом типе данных (текстовые данные).
DECLARE @Var1 VARCHAR(5) DECLARE @Var2 VARCHAR(20) SET @Var1 = NULL SET @Var2 = 'Первый параметр NULL' /* Функция COALESCE вернет значение с типом varchar(20), т.е. как у переменной @Var2, а ISNULL с типом varchar(5) как у первого параметра @Var1, т.е. произойдет усечение данных */ SELECT COALESCE(@Var1, @Var2) AS [COALESCE], ISNULL(@Var1, @Var2) AS [ISNULL]
Пример 3. Отличие в возвращаемом типе данных (целочисленный тип данных).
DECLARE @Var1 TINYINT DECLARE @Var2 INT SET @Var1 = NULL SET @Var2 = -1 /* Функция COALESCE вернет более приоритетный тип данных, т.е. INT, а функция ISNULL выдаст ошибку, так как нельзя явно преобразовать значение -1 в тип TINYINT, который является типом первого параметра */ PRINT COALESCE(@Var1, @Var2) PRINT ISNULL(@Var1, @Var2)
Пример 4. Отличие в возвращаемом значении, т.е. результат ISNULL — это не NULL, а результат COALESCE — это NULL.
В примере ниже мы создаем временные таблицы с ограничением первичного ключа на вычисляемом столбце, в первой таблице вычисляемый столбец использует в своем выражении ISNULL(column1, 1), где column1 допускает значения NULL, а во второй таблице COALESCE(column1, 1), т.е. по сути, эквивалентные выражения. В итоге первая таблица будет создана успешно, так как SQL сервер подразумевает, что возвращаемое значение функции ISNULL не допускает значений NULL. А при создании второй таблицы выйдет ошибка, так как в этом случае SQL сервер предполагает, что возвращаемое значение функции COALESCE будет NULL.
--Таблица будет создана успешно CREATE TABLE #TempTable1 ( column1 integer NULL, column2 AS ISNULL(column1, 1) PRIMARY KEY ) IF OBJECT_ID('tempdb..#TempTable1') IS NOT NULL BEGIN PRINT 'Таблица #TempTable1 успешно создана' DROP TABLE #TempTable1 --Сразу удаляем ее END GO --Ошибка CREATE TABLE #TempTable2 ( column1 integer NULL, column2 AS COALESCE(column1, 1) PRIMARY KEY )
Рекомендации по использованию функций COALESCE и ISNULL
Функцию COALESCE рекомендовано использовать тогда, когда просто необходимо вернуть первое значение отличное от NULL.
Функцию ISNULL используйте для замены значения NULL, при возможном его появлении в выражении, на другое значение, при этом в качестве второго параметра лучше указывать выражение 100% отличное от NULL, например константу. Другими словами, чтобы предотвратить ситуацию, когда мы хотим использовать значение, а оно у нас NULL.
Если говорить о производительности, то в случаях, когда в качестве выражения выступает подзапрос, а по документации SQL сервера в функции COALESCE значение подзапроса будет вычисляться дважды, можно сделать вывод, что в этих случаях быстрей будет ISNULL, так как в ней значение вычисляется один раз. В остальных случаях разница будет незначительна.
Заметка! Для комплексного изучения языка SQL и T-SQL рекомендую посмотреть мои видеокурсы по T-SQL, в которых используется последовательная методика обучения специально для начинающих.
На этом у меня все, удачи!