Home Library Learn Downloads Support Community
Sign in | United States - English | |
32 out of 44 rated this helpful - Rate this topic

ISNULL (Transact-SQL)

SQL Server 2008

Replaces NULL with the specified replacement value.

Topic link iconTransact-SQL Syntax Conventions

Copy 

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.

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.

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.

Copy 
USE AdventureWorks;
GO
SELECT AVG(ISNULL(Weight, 50))
FROM Production.Product;
GO

Here is the result set.

Copy 
-------------------------- 
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 AdventureWorks. If the maximum quantity for a particular special offer is NULL, the MaxQty shown in the result set is 0.00.

Copy 
USE AdventureWorks;
GO
SELECT Description, DiscountPct, MinQty, ISNULL(MaxQty, 0.00) AS 'Max Quantity'
FROM Sales.SpecialOffer;
GO

Here is the result set.

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

Copy 
USE AdventureWorks;
GO
SELECT Name, Weight
FROM Production.Product
WHERE Weight IS NULL;
GO
Did you find this helpful?
(1500 characters remaining)
Community Content Add
Annotations FAQ
Comparison between ISNULL() and COALESCE()
The page http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=ISNULL_COALESCE&referringTitle=Home contains a useful comparison between ISNULL() and COALESCE(). Based on this, COALESCE() is recommended over ISNULL() in typical cases, it will be the exceptions that justify usage of ISNULL().
History