ISNULL (Transact-SQL)
Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) SQL analytics endpoint in Microsoft Fabric Warehouse in Microsoft Fabric
Replaces NULL with the specified replacement value.
Transact-SQL syntax conventions
Syntax
ISNULL ( check_expression , replacement_value )
Note
To view Transact-SQL syntax for SQL Server 2014 (12.x) and earlier versions, see Previous versions documentation.
Arguments
check_expression
Is the expression to be checked for NULL. check_expression can be of any type.
replacement_value
Is the expression to be returned if check_expression is NULL. replacement_value must be of a type that is implicitly convertible to the type of check_expression.
Return Types
Returns the same type as check_expression. If a literal NULL is provided as check_expression, returns the datatype of the replacement_value. If a literal NULL is provided as check_expression and no replacement_value is provided, returns an int.
Remarks
The value of check_expression is returned if it is not NULL; otherwise, replacement_value is returned after it is implicitly converted to the type of check_expression, if the types are different. replacement_value can be truncated if replacement_value is longer than check_expression.
Note
Use COALESCE (Transact-SQL) to return the first non-null value.
Examples
A. Using ISNULL with AVG
The following example finds the average of the weight of all products. It substitutes the value 50
for all NULL entries in the Weight
column of the Product
table.
USE AdventureWorks2022;
GO
SELECT AVG(ISNULL(Weight, 50))
FROM Production.Product;
GO
Here is the result set.
--------------------------
59.79
(1 row(s) affected)
B. Using ISNULL
The following example selects the description, discount percentage, minimum quantity, and maximum quantity for all special offers in AdventureWorks2022
. If the maximum quantity for a particular special offer is NULL, the MaxQty
shown in the result set is 0.00
.
USE AdventureWorks2022;
GO
SELECT Description, DiscountPct, MinQty, ISNULL(MaxQty, 0.00) AS 'Max Quantity'
FROM Sales.SpecialOffer;
GO
Here is the result set.
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)
C. Testing for NULL in a WHERE clause
Do not use ISNULL to find NULL values. Use IS NULL instead. The following example finds all products that have NULL
in the weight column. Note the space between IS
and NULL
.
USE AdventureWorks2022;
GO
SELECT Name, Weight
FROM Production.Product
WHERE Weight IS NULL;
GO
Examples: Azure Synapse Analytics and Analytics Platform System (PDW)
D. Using ISNULL with AVG
The following example finds the average of the weight of all products in a sample table. It substitutes the value 50
for all NULL entries in the Weight
column of the Product
table.
-- Uses AdventureWorks
SELECT AVG(ISNULL(Weight, 50))
FROM dbo.DimProduct;
Here is the result set.
--------------------------
52.88
E. Using ISNULL
The following example uses ISNULL to test for NULL values in the column MinPaymentAmount
and display the value 0.00
for those rows.
-- Uses AdventureWorks
SELECT ResellerName,
ISNULL(MinPaymentAmount,0) AS MinimumPayment
FROM dbo.DimReseller
ORDER BY ResellerName;
Here is a partial result set.
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 |
F. Using IS NULL to test for NULL in a WHERE clause
The following example finds all products that have NULL
in the Weight
column. Note the space between IS
and NULL
.
-- Uses AdventureWorks
SELECT EnglishProductName, Weight
FROM dbo.DimProduct
WHERE Weight IS NULL;
See Also
Expressions (Transact-SQL)
IS NULL (Transact-SQL)
System Functions (Transact-SQL)
WHERE (Transact-SQL)
COALESCE (Transact-SQL)
Povratne informacije
https://aka.ms/ContentUserFeedback.
Uskoro: tokom 2024. postepeno ćemo ukidati probleme s uslugom GitHub kao mehanizam povratnih informacija za sadržaj i zamijeniti ga novim sistemom povratnih informacija. Za više informacija, pogledajtePošalјite i prikažite povratne informacije za