Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All

ISNULL (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

Replaces NULL with the specified replacement value.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database, Azure SQL Data Warehouse Public Preview.

Topic link icon Transact-SQL Syntax Conventions


ISNULL ( check_expression , replacement_value )

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_expresssion.

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.

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.

System_CAPS_noteNote

Use COALESCE (Transact-SQL) to return the first non-null value.

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 AdventureWorks2012;
GO
SELECT AVG(ISNULL(Weight, 50))
FROM Production.Product;
GO

Here is the result set.

--------------------------

59.79

(1 row(s) affected)

The following example selects the description, discount percentage, minimum quantity, and maximum quantity for all special offers in AdventureWorks2012. If the maximum quantity for a particular special offer is NULL, the MaxQty shown in the result set is 0.00.

USE AdventureWorks2012;
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)

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 AdventureWorks2012;
GO
SELECT Name, Weight
FROM Production.Product
WHERE Weight IS NULL;
GO

Community Additions

ADD
Show:
© 2015 Microsoft