Как вывести ненулевые значения 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 имеют аналогичные цели, но могут отличаться поведением.

1. Так как ISNULL — это функция, она вычисляется только один раз. Как было сказано выше, входные значения для выражения COALESCE могут вычисляться несколько раз.
2. Различается определение типа данных результирующего выражения. ISNULL использует тип данных первого параметра, COALESCE следует правилам выражения CASE и возвращает тип данных значения с самым высоким приоритетом.
3. Для 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 

LAST_VALUE (Transact-SQL)

Возвращает последнее значение из упорядоченного набора значений.

Синтаксис

LAST_VALUE ( [ scalar_expression ] ) [ IGNORE NULLS | RESPECT NULLS ] OVER ( [ partition_by_clause ] order_by_clause [ rows_range_clause ] ) 

Сведения о синтаксисе Transact-SQL для SQL Server 2014 (12.x) и более ранних версиях см . в документации по предыдущим версиям.

Аргументы

scalar_expression

Возвращаемое значение. scalar_expression может быть столбцом, вложенным запросом или другим выражением, результатом вычисления которого является единичное значение. Другие аналитические функции использовать запрещено.

[ ИГНОРИРОВАТЬ ЗНАЧЕНИЯ NULL | УВАЖЕНИЕ ЗНАЧЕНИЙ NULL ] **

Область применения: SQL Server (начиная с SQL Server 2022 (16.x)), База данных SQL Azure, Управляемый экземпляр SQL Azure, Azure SQL Edge

IGNORE NULLS — игнорировать значения NULL в наборе данных при вычислении последнего значения по секции.

RESPECT NULLS — учитывает значения NULL в наборе данных при вычислении последнего значения по секции. RESPECT NULLS — это поведение по умолчанию, если параметр NULLS не указан.

Дополнительные сведения об этом аргументе в Azure SQL Edge см. в разделе «Очиска отсутствующих значений».

OVER ( [ partition_by_clause ] order_by_clause [ rows_range_clause ] )

partition_by_clause делит результирующий набор, полученный с помощью предложения FROM, на секции, к которым применяется эта функция. Если этот параметр не указан, функция обрабатывает все строки результирующего набора запроса как отдельные группы.

order_by_clause определяет порядок данных перед применением функции. Аргумент order_by_clause является обязательным.

rows_range_clause еще больше ограничивает строки в пределах секции, задавая начальную и конечную точки.

Дополнительные сведения см. в статье Предложение OVER (Transact-SQL).

Типы возвращаемых данных

Тот же тип, что и scalar_expression.

Замечания

Функция LAST_VALUE не детерминирована. Дополнительные сведения см. в разделе детерминированные и недетерминированные функции.

Примеры

А. Использование LAST_VALUE для секций

В следующем примере показано возвращение даты найма последнего сотрудника каждого отдела для указанной заработной платы ( Rate ). Предложение PARTITION BY разделяет сотрудников по подразделениям, а функция LAST_VALUE применяется к каждой из секций по отдельности. Предложение ORDER BY , указанное в предложении OVER , определяет логический порядок, в котором функция LAST_VALUE применяется к строкам каждой секции.

USE AdventureWorks2022; GO SELECT Department, LastName, Rate, HireDate, LAST_VALUE(HireDate) OVER ( PARTITION BY Department ORDER BY Rate ) AS LastValue FROM HumanResources.vEmployeeDepartmentHistory AS edh INNER JOIN HumanResources.EmployeePayHistory AS eph ON eph.BusinessEntityID = edh.BusinessEntityID INNER JOIN HumanResources.Employee AS e ON e.BusinessEntityID = edh.BusinessEntityID WHERE Department IN (N'Information Services', N'Document Control'); 

Department LastName Rate HireDate LastValue --------------------------- ----------------------- ------------ ---------- ---------- Document Control Chai 10.25 2003-02-23 2003-03-13 Document Control Berge 10.25 2003-03-13 2003-03-13 Document Control Norred 16.8269 2003-04-07 2003-01-17 Document Control Kharatishvili 16.8269 2003-01-17 2003-01-17 Document Control Arifin 17.7885 2003-02-05 2003-02-05 Information Services Berg 27.4038 2003-03-20 2003-01-24 Information Services Meyyappan 27.4038 2003-03-07 2003-01-24 Information Services Bacon 27.4038 2003-02-12 2003-01-24 Information Services Bueno 27.4038 2003-01-24 2003-01-24 Information Services Sharma 32.4519 2003-01-05 2003-03-27 Information Services Connelly 32.4519 2003-03-27 2003-03-27 Information Services Ajenstat 38.4615 2003-02-18 2003-02-23 Information Services Wilson 38.4615 2003-02-23 2003-02-23 Information Services Conroy 39.6635 2003-03-08 2003-03-08 Information Services Trenary 50.4808 2003-01-12 2003-01-12 

B. Использование функций FIRST_VALUE и LAST_VALUE в вычисляемом выражении

В следующем примере с помощью функций FIRST_VALUE и LAST_VALUE , указанных в вычисляемых выражениях, показывается разница между значением квоты продаж для текущего квартала и для первого и последнего квартала года соответственно для данного числа сотрудников. Функция FIRST_VALUE возвращает значение квоты продаж за первый квартал года, затем вычитает его из значения квоты продаж за текущий квартал. Он возвращает производный столбец под названием DifferenceFromFirstQuarter . За первый квартал года значение столбца DifferenceFromFirstQuarter составляет 0. Функция LAST_VALUE возвращает значение квоты продаж за последний квартал года, затем вычитает его из значения квоты продаж за текущий квартал. Он возвращается в производном столбце, который имеет право DifferenceFromLastQuarter . За последний квартал года значение столбца DifferenceFromLastQuarter составляет 0.

RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING Предложение требуется в этом примере для возврата ненулевых значений в столбце DifferenceFromLastQuarter . Диапазон по умолчанию — RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW . В этом примере результатом использования указанного диапазона по умолчанию (если не включать диапазон, будет использоваться диапазон по умолчанию) будет возврат нулей в столбце DifferenceFromLastQuarter . Дополнительные сведения см. в статье Предложение OVER (Transact-SQL).

USE AdventureWorks2022; GO SELECT BusinessEntityID, DATEPART(QUARTER, QuotaDate) AS Quarter, YEAR(QuotaDate) AS SalesYear, SalesQuota AS QuotaThisQuarter, SalesQuota - FIRST_VALUE(SalesQuota) OVER ( PARTITION BY BusinessEntityID, YEAR(QuotaDate) ORDER BY DATEPART(QUARTER, QuotaDate) ) AS DifferenceFromFirstQuarter, SalesQuota - LAST_VALUE(SalesQuota) OVER ( PARTITION BY BusinessEntityID, YEAR(QuotaDate) ORDER BY DATEPART(QUARTER, QuotaDate) RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) AS DifferenceFromLastQuarter FROM Sales.SalesPersonQuotaHistory WHERE YEAR(QuotaDate) > 2005 AND BusinessEntityID BETWEEN 274 AND 275 ORDER BY BusinessEntityID, SalesYear, Quarter; 

BusinessEntityID Quarter SalesYear QuotaThisQuarter DifferenceFromFirstQuarter DifferenceFromLastQuarter ---------------- ----------- ----------- --------------------- --------------------------- ----------------------- 274 1 2006 91000.00 0.00 -63000.00 274 2 2006 140000.00 49000.00 -14000.00 274 3 2006 70000.00 -21000.00 -84000.00 274 4 2006 154000.00 63000.00 0.00 274 1 2007 107000.00 0.00 -9000.00 274 2 2007 58000.00 -49000.00 -58000.00 274 3 2007 263000.00 156000.00 147000.00 274 4 2007 116000.00 9000.00 0.00 274 1 2008 84000.00 0.00 -103000.00 274 2 2008 187000.00 103000.00 0.00 275 1 2006 502000.00 0.00 -822000.00 275 2 2006 550000.00 48000.00 -774000.00 275 3 2006 1429000.00 927000.00 105000.00 275 4 2006 1324000.00 822000.00 0.00 275 1 2007 729000.00 0.00 -489000.00 275 2 2007 1194000.00 465000.00 -24000.00 275 3 2007 1575000.00 846000.00 357000.00 275 4 2007 1218000.00 489000.00 0.00 275 1 2008 849000.00 0.00 -20000.00 275 2 2008 869000.00 20000.00 0.00 

Связанный контент

• FIRST_VALUE (Transact-SQL)
• SELECT — предложение OVER (Transact-SQL)

SQL условие IS NOT NULL

В этом учебном материале вы узнаете, как использовать SQL условие IS NOT NULL с синтаксисом и примерами.

Описание

Условие IS NOT NULL используется в SQL для проверки значения, отличного от NULL. Оно возвращает TRUE, если найдено ненулевое значение, в противном случае оно возвращает FALSE. Его можно использовать в операторе SELECT, INSERT, UPDATE или DELETE.

Синтаксис

Синтаксис для условия IS NOT NULL в SQL:

expression IS NOT NULL

Параметры или аргументы

expression Выражение для проверки значения NOT NULL.

Пример — использование IS NOT NULL с оператором SELECT

При проверке значения, отличного от NULL, IS NOT NULL является рекомендуемым оператором сравнения для использования в SQL. Давайте начнем с примера, который показывает, как использовать условие IS NOT NULL в SELECT предложении.

В этом примере у нас есть таблица products со следующими данными:

product_id	product_name	category_id
1	Pear	50
2	Banana	50
3	Orange	50
4	Apple	50
5	Bread	75
6	Sliced Ham	25
7	Kleenex	NULL

Введите следующий SQL оператор:

FROM products
WHERE category_id IS NOT NULL ;

Будет выбрано 6 записей. Вот результаты, которые вы должны получить:

product_id	product_name	category_id
1	Pear	50
2	Banana	50
3	Orange	50
4	Apple	50
5	Bread	75
6	Sliced Ham	25

В этом примере будут возвращены все записи из таблицы products , где customer_id не содержит значения NULL.

Пример — использование IS NOT NULL с оператором UPDATE

Далее давайте рассмотрим пример использования условия IS NOT NULL в запросе UPDATE.

В этом примере у нас есть таблица customer со следующими данными:

customer_id	first_name	last_name	favorite_website
4000	Justin	Bieber	google.com
5000	Selena	Gomez	bing.com
6000	Mila	Kunis	yahoo.com
7000	Tom	Cruise	oracle.com
8000	Johnny	Depp	NULL
9000	Russell	Crowe	google.com

Введите следующий запрос UPDATE:

UPDATE customers
SET favorite_website = ‘google.com’
WHERE favorite_website IS NOT NULL ;

Будет обновлено 5 записей. Выберите данные из таблицы customer еще раз:

FROM customers;

Вот результаты, которые вы должны получить:

customer_id	first_name	last_name	favorite_website
4000	Justin	Bieber	google.com
5000	Selena	Gomez	google.com
6000	Mila	Kunis	google.com
7000	Tom	Cruise	google.com
8000	Johnny	Depp	NULL
9000	Russell	Crowe	google.com

В этом примере будут обновлены все значения fav_website в таблице customer до google.com, где favourite_website содержит значение NULL. Как вы видите, значения поля favorite_website обновлены все строки кроме одной.

Пример — использование IS NOT NULL с оператором DELETE

Далее давайте рассмотрим пример использования условия IS NULL в запросе DELETE.

В этом примере у нас есть таблица orders и следующими данными:

order_id	customer_id	order_date
1	7000	2019/06/18
2	5000	2019/06/18
3	8000	2019/06/19
4	4000	2019/06/20
5	NULL	2019/07/01

Введите следующий запрос DELETE:

PostgreSQL условие IS NOT NULL

В этом учебном пособии вы узнаете, как использовать PostgreSQL условие IS NOT NULL с синтаксисом и примерами.

Описание

PostgreSQL условие IS NOT NULL используется для проверки значения NOT NULL в операторе SELECT, INSERT, UPDATE или DELETE.

Синтаксис

Синтаксис для условия IS NOT NULL в PostgreSQL:

expression IS NOT NULL

Параметры или аргументы

expression Значение для проверки, является ли оно ненулевым значением.

Примечание

• Если expression НЕ является значением NULL, условие оценивается как TRUE.
• Если expression является значением NULL, условие оценивается как FALSE.

Пример — с оператором SELECT

Вот пример того, как использовать PostgreSQL условие IS NOT NULL в операторе SELECT: