Coalesce sql: описание, особенности использования, примеры. Sql isnull примеры
SQL isnull()
SQL Функции ISNULL(), NVL(), IFNULL() и COALESCE()
Есть таблица "Products":
| 1 | Jarlsberg | 10.45 | 16 | 15 |
| 2 | Mascarpone | 32.56 | 23 | |
| 3 | Gorgonzola | 15.67 | 9 | 20 |
Предположим, что столбец "UnitsOnOrder" может содержать NULL значения.
Для этого используем такой запрос:
SELECT ProductName,UnitPrice*(UnitsInStock+UnitsOnOrder) FROM ProductsВ приведенном выше примере, если какой-либо из "UnitsOnOrder" имеет значения NULL, то результат будет недействительным.
ISNULL() - функция используется для указания способа получения NULL значений.
В данном случае мы хотим, чтобы NULL значения были нулем.
Если "UnitsOnOrder" в таком случае будет NULL для нас не представит сложности использовать ISNULL() которая вернет нули вместо NULL:
SQL Server / MS Access
| 1 2 | SELECT ProductName,UnitPrice*(UnitsInStock+ISNULL(UnitsOnOrder,0)) FROM Products |
Oracle
В Oracle нет функцию ISNULL (). Тем не менее NVL () аналогична функции ISNULL ():
| 1 2 | SELECT ProductName,UnitPrice*(UnitsInStock+NVL(UnitsOnOrder,0)) FROM Products |
MySQL
В MySQL используется функция ISNULL().
| 1 2 | SELECT ProductName,UnitPrice*(UnitsInStock+IFNULL(UnitsOnOrder,0)) FROM Products |
или же мы можем использовать функцию COALESCE().
dimonchik.com
Функции COALESCE и ISNULL в T-SQL – особенности и основные отличия | Info-Comp.ru
В языке 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. Отличие в возвращаемом типе данных (текстовые данные).
Пример 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.
Рекомендации по использованию функций COALESCE и ISNULL
Функцию COALESCE рекомендовано использовать тогда, когда просто необходимо вернуть первое значение отличное от NULL.
Функцию ISNULL используйте для замены значения NULL при возможном его появлении в выражении, на другое значение при этом в качестве второго параметра лучше указывать выражение 100% отличное от NULL, например константу. Другими словами чтобы предотвратить ситуацию, когда мы хотим использовать значение, а оно у нас NULL.
Если говорить о производительности, то в случаях, когда в качестве выражения выступает подзапрос, а по документации SQL сервера в функции COALESCE значение подзапроса будет вычисляться дважды, можно сделать вывод что в этих случаях быстрей будет ISNULL, так как в ней значение вычисляется один раз. В остальных случаях разница будет не значительна.
Заметка! Начинающим программистам рекомендую почитать мою книгу «Путь программиста T-SQL. Самоучитель по языку Transact-SQL», в ней я подробно, с большим количеством примеров, рассказываю про другие полезные функции и возможности языка Transact-SQL.
На этом у меня все, удачи!
Похожие статьи:
MS SQL Server и T-SQL
Функции NEWID, ISNULL и COALESCE
Последнее обновление: 29.07.2017
NEWID
Для генерации объекта 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То есть в данном случае возвращается телефон, если он определен. Если он не определен, то возвращается электронный адрес. Если и электронный адрес не определен, то возвращается строка "не определено".
metanit.com
Функция ISNULL (Transact-SQL)
ПРИМЕНЯЕТСЯ К: SQL Server (начиная с 2008) База данных SQL Azure Хранилище данных SQL Azure Parallel Data Warehouse
Заменяет значение NULL указанным замещающим значением.
Синтаксические обозначения в Transact-SQL
-- Syntax for SQL Server, Azure SQL Database, Azure SQL Data Warehouse, Parallel Data Warehouse ISNULL ( check_expression , replacement_value )replacement_valueВыражение, возвращаемое, если check_expression имеет значение NULL. replacement_value должен иметь тип, который может быть неявно преобразован в тип check_expresssion.
Возвращает тот же тип, как check_expression. Если литерал NULL предоставляется как check_expression, возвращает тип данных replacement_value. Если литерал NULL предоставляется как check_expression и не replacement_value предоставляется, возвращает int.
Значение check_expression возвращается, если она не NULL, в противном случае — replacement_value возвращается после неявно преобразуется к типу check_expression, если типы различаются. replacement_value может быть усечена, если replacement_value длиннее, чем check_expression.
Используйте COALESCE (Transact-SQL) чтобы вернуть первое значение, отличное от null. |
A. Использование функции ISNULL с функцией AVG
Следующий пример демонстрирует расчет среднего значения веса всех продуктов. Все записи со значением NULL в столбце 50 таблицы Weight заменяются значением Product.
USE AdventureWorks2012; GO SELECT AVG(ISNULL(Weight, 50)) FROM Production.Product; GOНиже приводится результирующий набор.
--------------------------
59.79
(1 row(s) affected)
Б. Использование функции ISNULL
Следующий пример производит выборку описания, процента скидки, минимального и максимального количества для всех специальных предложений из базы AdventureWorks2012. Если максимальное количество для отдельного специального предложения равно NULL, отображаемое значение MaxQty в результирующем наборе заменяется на 0.00.
USE AdventureWorks2012; GO SELECT Description, DiscountPct, MinQty, ISNULL(MaxQty, 0.00) AS 'Max Quantity' FROM Sales.SpecialOffer; GOНиже приводится результирующий набор.
Description DiscountPct MinQty Max Quantity
--------------- ------------- -------- ---------------
No Discount 0.00 0 0
Volume Discount 0.02 11 14
Volume Discount 0.05 15 4
Volume Discount 0.10 25 0
Volume Discount 0.15 41 0
Volume Discount 0.20 61 0
Mountain-100 Cl 0.35 0 0
Sport Helmet Di 0.10 0 0
Road-650 Overst 0.30 0 0
Mountain Tire S 0.50 0 0
Sport Helmet Di 0.15 0 0
LL Road Frame S 0.35 0 0
Touring-3000 Pr 0.15 0 0
Touring-1000 Pr 0.20 0 0
Half-Price Peda 0.50 0 0
Mountain-500 Si 0.40 0 0
(16 row(s) affected)
В. Проверка значений NULL в предложении WHERE
Не используйте для поиска значений NULL выражение ISNULL, вместо него следует использовать выражение IS NULL. В следующем примере выполняется поиск всех продуктов, имеющих значение NULL в столбце веса. Заметьте, что между словами IS и NULL стоит пробел.
USE AdventureWorks2012; GO SELECT Name, Weight FROM Production.Product WHERE Weight IS NULL; GOГ. Использование функции ISNULL с функцией AVG
В следующем примере вычисляется среднее значение веса всех продуктов в образец таблицы. Все записи со значением NULL в столбце 50 таблицы Weight заменяются значением Product.
SELECT AVG(ISNULL(Weight, 50)) FROM dbo.DimProduct;Ниже приводится результирующий набор.
-------------------------- 52.88Д. Использование функции ISNULL
В следующем примере используется функция ISNULL для проверки наличия значений NULL в столбце MinPaymentAmount и отобразить значение 0.00 для этих строк.
SELECT ResellerName, ISNULL(MinPaymentAmount,0) AS MinimumPayment FROM dbo.DimReseller ORDER BY ResellerName;Здесь приводится частичный результирующий набор.
ResellerName MinimumPayment
------------------------- --------------
A Bicycle Association 0.0000
A Bike Store 0.0000
A Cycle Shop 0.0000
A Great Bicycle Company 0.0000
A Typical Bike Shop 200.0000
Acceptable Sales & Service 0.0000
Е. Использование IS NULL для проверки значений NULL в предложении WHERE
Следующий пример находит все продукты, которые имеют NULL в Weight столбец. Заметьте, что между словами IS и NULL стоит пробел.
SELECT EnglishProductName, Weight FROM dbo.DimProduct WHERE Weight IS NULL;Выражения (Transact-SQL) ИМЕЕТ значение NULL (Transact-SQL) Системные функции (Transact-SQL) ГДЕ (Transact-SQL) Функция ОБЪЕДИНЕНИЯ (Transact-SQL)
technet.microsoft.com
Функция IsNull - Access
Возвращает значение типа Boolean, указывающее на то, содержит ли выражение допустимые данные или же равно NULL.
Синтаксис
IsNull ( выражение )
Обязательный аргумент выражениеаргумент является значением типа Variant, содержащим числовое выражение или строковое выражение.
Замечания
Функция IsNull возвращает значение True, если аргумент выражение имеет значение NULL. В противном случае функция IsNull возвращает значение False. Если выражение содержит несколько переменных и хотя бы одна из них имеет значение NULL, возвращается значение True для всего выражения.
Значение NULL указывает, что переменная типа Variant не содержит допустимых данных. Значение NULL и пусто — не одно и то же; последнее указывает, что переменная еще не инициализирована. Оно не равно также и строке нулевой длины (""), иногда называемой пустой строкой.
Важно: Используйте функцию IsNull, чтобы определить, содержит ли выражение значение NULL. Некоторые выражения, которые, казалось бы, должны в определенных случаях возвращать True, например If Var = Null и If Var <> Null, всегда возвращают False. Это происходит потому. что выражение, содержащее NULL, само по себе равно NULL и поэтому дает False.
Пример
Примечание: В примерах ниже показано, как использовать эту функцию в модуле Visual Basic для приложений (VBA). Чтобы получить дополнительные сведения о работе с VBA, выберите Справочник разработчика в раскрывающемся списке рядом с полем Поиск и введите одно или несколько слов в поле поиска.
В следующем примере с помощью функции IsNull определяется, содержит ли переменная значение NULL.
Dim MyVar, MyCheckMyCheck = IsNull(MyVar) ' Returns False.MyVar = ""MyCheck = IsNull(MyVar) ' Returns False.MyVar = NullMyCheck = IsNull(MyVar) ' Returns True.support.office.com
Функция SQL ISNULL (), NVL (), IFNULL () и COALESCE ()
Функция SQL ISNULL (), NVL (), IFNULL () и COALESCE ()
Посмотрите на следующую таблицу "Продукты":
| 1 | Jarlsberg | 10.45 | 16 | 15 |
| 2 | Mascarpone | 32.56 | 23 | |
| 3 | Gorgonzola | 15.67 | 9 | 20 |
Если "UnitsOnOrder" не является обязательным, и может содержать NULL значения.
Мы используем следующую ЗЕЬЕСТ:
SELECT ProductName,UnitPrice*(UnitsInStock+UnitsOnOrder) FROM Products
В приведенном выше примере, если есть "UnitsOnOrder" значение NULL, то результат равен NULL.
функция от Microsoft ISNULL () используется для указания того, как обрабатывать NULL значения.
Функция NVL (), IFNULL () и COALESCE () также может достичь тех же результатов.
Здесь, мы надеемся, что NULL значение равно 0.
Теперь, если "UnitsOnOrder" является NULL, это не повлияет на расчет, потому что если значение равно NULL, ISNULL () возвращает 0:
SQL Server / MS Access
SELECT ProductName,UnitPrice*(UnitsInStock+ISNULL(UnitsOnOrder,0)) FROM Products
оракул
Oracle не IsNull функцию (). Тем не менее, мы можем использовать функцию NVL (), чтобы достичь того же результата:
SELECT ProductName,UnitPrice*(UnitsInStock+NVL(UnitsOnOrder,0)) FROM Products
MySQL
MySQL также имеет аналогичную функцию ISNULL (). Но он работает с ISNULL от Microsoft () функция немного отличается.
В MySQL, мы можем использовать функцию IFNULL () следующим образом:
SELECT ProductName,UnitPrice*(UnitsInStock+IFNULL(UnitsOnOrder,0)) FROM Products
Или же мы можем использовать функцию COALESCE () следующим образом:
SELECT ProductName,UnitPrice*(UnitsInStock+COALESCE(UnitsOnOrder,0)) FROM Products
www.w3big.com
Coalesce sql: описание, особенности использования, примеры
При составлении sql-запросов нередко возникает ситуация, когда при выборке данных надо сравнить значения нескольких столбцов и вывести то из них, которое содержит данные (не пустое). Эту задачу отлично решает выражение Coalesce. В данной статье вы найдете полную характеристику выражения Coalesce sql, описание особенностей использования, а также примеры.
Вывод непустых значений с помощью Coalesce. Особенности
Рассмотрим Sql Coalesce особенности использования:
- Допускает указание любого количества аргументов (в отличие от Isnull/Nvl/Nvl2, число аргументов в которых ограничено).
- Может принимать в качестве аргумента подзапросы.
- Возвращает результат, равный первому отличному от Null значению, либо Null, если отличного от Null значения не будет найдено.
- Sql Coalesce может использоваться в предложении Select для выбора непустого значения, а также в Where для уточнения, что набор столбцов с пустыми значениями не допускается (/допускается).
- Данное выражение равнозначно применению выражения Case, проверяющему последовательно каждый аргумент на условие When argument1 is not null then argument1. По сути, Coalesce это «ярлык», созданный для удобства использования, и во многих СУБД оптимизаторы запросов переписывают выражение Coalesce на Case.
- Sql Coalesce функции имеются во всех ведущих реляционных системах управления базами данных.
Синтаксис Coalesce
Каждый, кто хоть раз применял Coalesce при составлении sql-запросов, знает, что синтаксис данного выражения предельно прост. Достаточно в круглых скобках указать аргументы, проверяемые на Null, через запятую. Если допустим, что аргументы имеют имена arg1, arg2, … argN, то синтаксис Coalesce будет иметь следующий вид:
Coalesce(arg1, arg2, … argN).
Подготовим несколько таблиц для изучения механизма работы данного выражения.
Подготовка таблиц
Чтобы лучше понять Coalesce sql описание, создадим в базе данных две таблицы, содержащие информацию по объектам недвижимости.
Первая таблица Area пусть содержит наименования объектов недвижимости и их площадь. Площадь может быть уточненной (area_yt) либо декларированной (area_decl).
id | object_name | area_yt | area_decl |
1 | Здание 1 | 116,2 | 114 |
2 | Объект незавершенного строительства 1 | 568 | |
3 | Помещение 1 | 64,7 | |
4 | Помещение 2 | 34,2 | |
5 | Земельный участок 1 | 112 | 111,6 |
6 | Сооружение 1 | ||
7 | Помещение 3 | 27,9 | |
8 | Сооружение 2 | 37,2 | 36,4 |
9 | Здание 2 |
Вторая таблица Basic_characteristic пусть содержит информацию по основной характеристике объекта недвижимости – протяженность (Extension), глубина (Depth), площадь (Area), объем (Scope), высота (Height).
id | object_name | Extension | Depth | Area | Scope | Height |
1 | Здание 1 | 892,4 | 30 | |||
2 | Здание 2 | 48 | ||||
3 | Сооружение 1 | 164,7 | ||||
4 | Земельный участок 1 | |||||
5 | Помещение 1 | 23,6 | ||||
6 | Помещение 2 | 34,7 | ||||
7 | Помещение 3 | 19,8 |
Рассмотрели синтаксис Coalesce sql, описание, особенности использования и перейдем непосредственно к примерам.
Примеры использования
Синтаксис у выражения Coalesce предельно прост, однако важно не забывать, что результатом выполнения команды станет ПЕРВОЕ непустое найденное значение из списка аргументов. Это замечание имеет очень большое значение, поэтому аргументы в выражении необходимо расставлять в порядке их важности. Проще всего понять принцип по таблице площадей. Составьте запрос, выбирающий наименование объекта недвижимости, а также значение площади:
SELECT Area.id, Area.object_name, coalesce(Area.area_yt, Area.area_decl) FROM Area |
И получите результат:
id | object_name | Coalesce |
1 | Здание 1 | 116,2 |
2 | Объект незавершенного строительства 1 | 568 |
3 | Помещение 1 | 64,7 |
4 | Помещение 2 | 34,2 |
5 | Земельный участок 1 | 112 |
6 | Сооружение 1 | |
7 | Помещение 3 | 27,9 |
8 | Сооружение 2 | 37,2 |
9 | Здание 2 |
По объектам «Здание 1», «Земельный участок 1» и «Сооружение 2» были заполнены оба значения площади, но в приоритете оказалась площадь уточненная, поскольку ее мы указали в списке аргументов первой. Выражение Coalesce нашло первое непустое значение и вывело его, прекратив дальнейший просмотр аргументов. Данное построение запроса – верное, ведь уточненная площадь более определенная, нежели декларированная. Если бы мы указали в качестве первого аргумента площадь декларированную, то при заполненности этого поля таблицы она оказалась бы в приоритете.
Помимо использования в Select, очень часто выражение Coalesce применяется с условием Where. Оно позволяет отсечь из результата те строки, в которых значения перечня полей пустые (или наоборот, включить в результат только те значения, где перечень полей не заполнен). Данная ситуация встречается повсеместно: например, на предприятии при оформлении нового сотрудника в базу данных внесли только базовые сведения о нем, а заполнение подробной информации оставили «на потом». Постепенно «пробелы» всплывают – или перед проверкой, или при направлении сотрудника в отпуск/командировку/больничный.
Выберем из таблицы с основными характеристиками объекты недвижимости, у которых не заполнено ни одно из значений характеристик:
SELECT id, object_name FROM Basic_characteristic Where coalesce(Extension, Depth, Area, Scope, Height) is null |
По данному запросу в таблице найдется одно совпадение – объект «Земельный участок 1», у которого все поля с характеристиками пустые:
id | object_name |
4 | Земельный участок 1 |
Надеемся, что наше подробное Coalesce sql описание помогло вам понять все особенности использования данного выражения, а также разобраться с важными нюансами.
fb.ru
