coalesce Функции
Тип результата является наименее распространенным типом аргументов.
Должен быть хотя бы один аргумент. В отличие от обычных функций, в которых все аргументы вычисляются перед вызовом функции, coalesce вычисляет аргументы слева направо, пока не будет найдено значение, отличное от NULL. Если все аргументы имеют значение NULL , результатом будет NULL .
Примеры
> SELECT coalesce(NULL, 1, NULL); 1 -- The following example raises a runtime error because the second argument is evaluated. > SELECT coalesce(NULL, 5 / 0); Error: DIVISION_BY_ZERO -- The following example raises no runtime error because the second argument is not evaluated. > SELECT coalesce(2, 5 / 0); 2 > SELECT coalesce(NULL, 'hello'); hello Связанным с
- Функция nvl
- Функция nvl2
- Правила типов данных SQL
COALESCE (Transact-SQL)
Вычисляет аргументы по порядку и возвращает текущее значение первого выражения, изначально не вычисленного как NULL . Например, SELECT COALESCE(NULL, NULL, ‘third_value’, ‘fourth_value’); возвращает третье значение, так как это первое значение, не равное NULL.
Синтаксис
COALESCE ( expression [ . n ] ) Аргументы
выражение
Выражение любого типа данных.
Сведения о синтаксисе Transact-SQL для SQL Server 2014 (12.x) и более ранних версиях см . в документации по предыдущим версиям.
Типы возвращаемых данных
Возвращает тип данных аргумента expression с наиболее высоким приоритетом. Если ни одно из выражений не допускает значения NULL, то результат типизируется как не допускающий значения NULL.
Замечания
Если все аргументы имеют значение NULL , COALESCE возвращает NULL . По крайней мере одно из значений NULL должно быть типизированным NULL .
Сравнение COALESCE и CASE
Выражение COALESCE — синтаксический ярлык для выражения CASE . Это означает, что код COALESCE (expression1,. n) переписывается оптимизатором запросов как следующее выражение CASE :
CASE WHEN (expression1 IS NOT NULL) THEN expression1 WHEN (expression2 IS NOT NULL) THEN expression2 . ELSE expressionN END Поэтому входные значения (expression1, expression2, expressionN и т. д.) вычисляются многократно. Выражение значения, содержащее вложенный запрос, считается недетерминированным, и вложенный запрос вычисляется дважды. Этот результат соответствует стандарту SQL. В любом случае могут быть возвращены различные результаты для первого и последующих вычислений.
Например, если выполняется код COALESCE((subquery), 1) , вложенный запрос вычисляется дважды. В результате можно получить различные результаты в зависимости от уровня изоляции запроса. Например, код может вернуть NULL при уровне изоляции READ COMMITTED в многопользовательской среде. Чтобы обеспечить устойчивые результаты, используйте уровень изоляции SNAPSHOT ISOLATION или замените COALESCE функцией ISNULL . Кроме того, можно переписать запрос, чтобы поместить вложенный запрос в подзапрос выборки, как показано в следующем примере:
SELECT CASE WHEN x IS NOT NULL THEN x ELSE 1 END FROM ( SELECT (SELECT Nullable FROM Demo WHERE SomeCol = 1) AS x ) AS T; Сравнение COALESCE и ISNULL
Функция ISNULL и выражение COALESCE имеют аналогичные цели, но могут отличаться поведением.
- Так как ISNULL — это функция, она вычисляется только один раз. Как было сказано выше, входные значения для выражения COALESCE могут вычисляться несколько раз.
- Различается определение типа данных результирующего выражения. ISNULL использует тип данных первого параметра, COALESCE следует правилам выражения CASE и возвращает тип данных значения с самым высоким приоритетом.
- Для ISNULL и COALESCE различается допустимость значения NULL для результирующего выражения. Значение, возвращаемое ISNULL , никогда НЕ БЫВАЕТ нулевым (предполагается, что возвращаемое значение является ненулевым). В то время как функция COALESCE с параметрами, которые не допускают значение NULL, считается имеющей значение NULL . Таким образом, выражения ISNULL(NULL, 1) и COALESCE(NULL, 1) , несмотря на одинаковый синтаксис, имеют разные значения допустимости NULL. Эти выражения отличаются при использовании в вычисляемых столбцах, создании ограничений ключа или детерминировании возвращаемого значения определяемой пользователем скалярной функции для возможности индексации, как показано в приведенном ниже примере.
USE tempdb; GO -- This statement fails because the PRIMARY KEY cannot accept NULL values -- and the nullability of the COALESCE expression for col2 -- evaluates to NULL. CREATE TABLE #Demo ( col1 INTEGER NULL, col2 AS COALESCE(col1, 0) PRIMARY KEY, col3 AS ISNULL(col1, 0) ); -- This statement succeeds because the nullability of the -- ISNULL function evaluates AS NOT NULL. CREATE TABLE #Demo ( col1 INTEGER NULL, col2 AS COALESCE(col1, 0), col3 AS ISNULL(col1, 0) PRIMARY KEY ); Примеры
А. Выполнение простого примера
В следующем примере показано, как функция COALESCE выбирает из первого столбца данные, отличные от значения NULL. В этом примере используется база данных AdventureWorks2022.
SELECT Name, Class, Color, ProductNumber, COALESCE(Class, Color, ProductNumber) AS FirstNotNull FROM Production.Product; B. Выполнение сложного примера
В следующем примере таблица wages включает три столбца с данными о ежегодной заработной плате сотрудников: hourly_wage, salary и commission. Однако служащий получает только один тип выплат. Для определения общей оплаты для всех служащих используйте функцию COALESCE для получения не равных NULL значений столбцов hourly_wage , salary и commission .
SET NOCOUNT ON; GO USE tempdb; IF OBJECT_ID('dbo.wages') IS NOT NULL DROP TABLE wages; GO CREATE TABLE dbo.wages ( emp_id TINYINT IDENTITY, hourly_wage DECIMAL NULL, salary DECIMAL NULL, commission DECIMAL NULL, num_sales TINYINT NULL ); GO INSERT dbo.wages (hourly_wage, salary, commission, num_sales) VALUES (10.00, NULL, NULL, NULL), (20.00, NULL, NULL, NULL), (30.00, NULL, NULL, NULL), (40.00, NULL, NULL, NULL), (NULL, 10000.00, NULL, NULL), (NULL, 20000.00, NULL, NULL), (NULL, 30000.00, NULL, NULL), (NULL, 40000.00, NULL, NULL), (NULL, NULL, 15000, 3), (NULL, NULL, 25000, 2), (NULL, NULL, 20000, 6), (NULL, NULL, 14000, 4); GO SET NOCOUNT OFF; GO SELECT CAST(COALESCE(hourly_wage * 40 * 52, salary, commission * num_sales) AS money) AS 'Total Salary' FROM dbo.wages ORDER BY 'Total Salary'; GO Total Salary ------------ 10000.00 20000.00 20800.00 30000.00 40000.00 41600.00 45000.00 50000.00 56000.00 62400.00 83200.00 120000.00 (12 row(s) affected) В. Простой пример
В приведенном ниже примере показано, как COALESCE выбирает данные из первого столбца, содержащего значение, отличное от NULL. В этом примере предполагается, что таблица Products содержит следующие данные:
Name Color ProductNumber ------------ ---------- ------------- Socks, Mens NULL PN1278 Socks, Mens Blue PN1965 NULL White PN9876 Затем выполняется следующий запрос COALESCE:
SELECT Name, Color, ProductNumber, COALESCE(Color, ProductNumber) AS FirstNotNull FROM Products ; Name Color ProductNumber FirstNotNull ------------ ---------- ------------- ------------ Socks, Mens NULL PN1278 PN1278 Socks, Mens Blue PN1965 Blue NULL White PN9876 White Обратите внимание на то, что в первой строке значение FirstNotNull равно PN1278 , а не Socks, Mens . Этот параметр принимает такое значение, так как столбец Name в примере не был указан в качестве параметра для COALESCE .
Г. Сложный пример
В приведенном ниже примере COALESCE используется для сравнения значений в трех столбцах и возврата только значения, отличного от NULL, найденного в столбцах.
CREATE TABLE dbo.wages ( emp_id TINYINT NULL, hourly_wage DECIMAL NULL, salary DECIMAL NULL, commission DECIMAL NULL, num_sales TINYINT NULL ); INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales) VALUES (1, 10.00, NULL, NULL, NULL); INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales) VALUES (2, 20.00, NULL, NULL, NULL); INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales) VALUES (3, 30.00, NULL, NULL, NULL); INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales) VALUES (4, 40.00, NULL, NULL, NULL); INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales) VALUES (5, NULL, 10000.00, NULL, NULL); INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales) VALUES (6, NULL, 20000.00, NULL, NULL); INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales) VALUES (7, NULL, 30000.00, NULL, NULL); INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales) VALUES (8, NULL, 40000.00, NULL, NULL); INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales) VALUES (9, NULL, NULL, 15000, 3); INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales) VALUES (10,NULL, NULL, 25000, 2); INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales) VALUES (11, NULL, NULL, 20000, 6); INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales) VALUES (12, NULL, NULL, 14000, 4); SELECT CAST(COALESCE(hourly_wage * 40 * 52, salary, commission * num_sales) AS DECIMAL(10,2)) AS TotalSalary FROM dbo.wages ORDER BY TotalSalary; Total Salary ------------ 10000.00 20000.00 20800.00 30000.00 40000.00 41600.00 45000.00 50000.00 56000.00 62400.00 83200.00 120000.00 Какой функции нет в 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 coalesce
Функция CASE проверяет истинность набора условий и в зависимости от результата проверки может возвращать тот или иной результат. Эта функция принимает следующую форму:
CASE WHEN условие_1 THEN результат_1 WHEN условие_2 THEN результат_2 . WHEN условие_N THEN условие_N [ELSE альтернативный_результат] END
Возьмем для примера следующую таблицу Products:
CREATE TABLE Products ( Id INT AUTO_INCREMENT PRIMARY KEY, ProductName VARCHAR(30) NOT NULL, Manufacturer VARCHAR(20) NOT NULL, ProductCount INT DEFAULT 0, Price DECIMAL NOT NULL );
Выполним запрос к этой таблице и используем функцию CASE:
SELECT ProductName, ProductCount, CASE WHEN ProductCount = 1 THEN 'Товар заканчивается' WHEN ProductCount = 2 THEN 'Мало товара' WHEN ProductCount = 3 THEN 'Есть в наличии' ELSE 'Много товара' END AS Category FROM Products;
Функция IF
Функция IF в зависимости от результата условного выражения возвращает одно из двух значений. Общая форма функции выглядит следующим образом:
IF(условие, значение_1, значение_2)
Если условие, передаваемое в качестве первого параметра, верно, то возвращается первое значение, иначе возвращается второе значение. Например:
SELECT ProductName, Manufacturer, IF(ProductCount > 3, 'Много товара', 'Мало товара') FROM Products;
IFNULL
Функция IFNULL проверяет значение некоторого выражения. Если оно равно NULL, то функция возвращает значение, которое передается в качестве второго параметра:
IFNULL(выражение, значение)
Например, возьмем следующую таблицу
CREATE TABLE Clients ( Id INT AUTO_INCREMENT PRIMARY KEY, FirstName VARCHAR(20) NOT NULL, LastName VARCHAR(20) NOT NULL, Phone VARCHAR(20) NULL, Email VARCHAR(20) NULL ); INSERT INTO Clients (FirstName, LastName, Phone, Email) VALUES ('Tom', 'Smith', '+36436734', NULL), ('Bob', 'Simpson', NULL, NULL);
И применим при получении данных функцию IFNULL:
SELECT FirstName, LastName, IFNULL(Phone, 'не определено') AS Phone, IFNULL(Email, 'неизвестно') AS Email FROM Clients;
COALESCE
Функция COALESCE принимает список значений и возвращает первое из них, которое не равно NULL:
COALESCE(выражение_1, выражение_2, выражение_N)
Например, выберем из таблицы Clients пользователей и в контактах у них определим либо телефон, либо электронный адрес, если они не равны NULL:
SELECT FirstName, LastName, COALESCE(Phone, Email, 'не определено') AS Contacts FROM Clients;
То есть в данном случае возвращается телефон, если он определен. Если он не определен, то возвращается электронный адрес. Если и электронный адрес не определен, то возвращается строка «не определено».