IS [NOT] NULL (T-SQL)

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 expression specified by the predicate is negated.

Result Types

Boolean

Return Code Values

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

Remarks

To determine if an expression is NULL, use IS 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)

See Also