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
The expression to be checked for NULL
. check_expression can be of any type.
replacement_value
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, ISNULL
returns the data type of the replacement_value. If a literal NULL
is provided as check_expression and no replacement_value is provided, ISNULL
returns an int.
Remarks
The value of check_expression is returned if it's not NULL
. Otherwise, replacement_value is returned after it's 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 to return the first non-null value.
Examples
The Transact-SQL code samples in this article use the AdventureWorks2022
or AdventureWorksDW2022
sample database, which you can download from the Microsoft SQL Server Samples and Community Projects home page.
A. Use 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
B. Use 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 11 to 14 |
0.02 | 11 | 14 |
Volume Discount 15 to 24 |
0.05 | 15 | 24 |
Volume Discount 25 to 40 |
0.10 | 25 | 40 |
Volume Discount 41 to 60 |
0.15 | 41 | 60 |
Volume Discount over 60 |
0.20 | 61 | 0 |
Mountain-100 Clearance Sale |
0.35 | 0 | 0 |
Sport Helmet Discount-2002 |
0.10 | 0 | 0 |
Road-650 Overstock |
0.30 | 0 | 0 |
Mountain Tire Sale |
0.50 | 0 | 0 |
Sport Helmet Discount-2003 |
0.15 | 0 | 0 |
LL Road Frame Sale |
0.35 | 0 | 0 |
Touring-3000 Promotion |
0.15 | 0 | 0 |
Touring-1000 Promotion |
0.20 | 0 | 0 |
Half-Price Pedal Sale |
0.50 | 0 | 0 |
Mountain-500 Silver Clearance Sale |
0.40 | 0 | 0 |
The following example uses ISNULL
to replace a NULL
value for Color
, with the string None
.
USE AdventureWorks2022;
GO
SELECT ProductID,
Name,
ProductNumber,
ISNULL(Color, 'None') AS Color
FROM Production.Product;
Here's a partial result set.
ProductID | Name | ProductNumber | Color |
---|---|---|---|
1 | Adjustable Race |
AR-5381 | None |
2 | Bearing Ball |
BA-8327 | None |
3 | BB Ball Bearing |
BE-2349 | None |
4 | Headset Ball Bearings |
BE-2908 | None |
316 | Blade |
BL-2036 | None |
317 | LL Crankarm |
CA-5965 | Black |
318 | ML Crankarm |
CA-6738 | Black |
319 | HL Crankarm |
CA-7457 | Black |
C. Test for NULL
in a WHERE clause
Don't 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. Use 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 AdventureWorksDW
SELECT AVG(ISNULL(Weight, 50))
FROM dbo.DimProduct;
Here is the result set.
52.88
E. Use 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's 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. Use 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 AdventureWorksDW
SELECT EnglishProductName, Weight
FROM dbo.DimProduct
WHERE Weight IS NULL;