Transact-SQL Reference

IS [NOT] NULL

Determines whether or not a given expression is NULL.

Syntax

expression IS [ NOT ] NULL

Arguments

expression

Is any valid Microsoft® SQL Server™ expression.

NOT

Specifies that the Boolean result be negated. The predicate reverses its return values, returning TRUE if the value is not NULL, and FALSE if the value is NULL.

Result Types

Boolean

Return Code Values

If the value of expression is NULL, IS NULL returns TRUE; otherwise, it returns FALSE.

If the value of expression is NULL, IS NOT NULL returns FALSE; otherwise, it returns TRUE.

Remarks

To determine if an expression is NULL, use IS NULL or IS NOT NULL rather than comparison operators (such as = or !=). Comparison operators return UNKNOWN if either or both arguments are NULL.

Examples

This example returns the title number and the advance amount for all books in which either the advance amount is less than $5,000 or the advance is unknown (or NULL). Note that the results shown are those returned after Example C has been executed.

USE pubs
SELECT title_id, advance
FROM titles
WHERE advance < $5000 OR advance IS NULL
ORDER BY title_id

Here is the result set:

title_id advance                    
-------- -------------------------- 
MC2222   0.0000                     
MC3026   NULL                       
PC9999   NULL                       
PS2091   2275.0000                  
PS3333   2000.0000                  
PS7777   4000.0000                  
TC4203   4000.0000                  

(7 row(s) affected)