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)

See Also

CASE

CREATE PROCEDURE

CREATE TABLE

Data Types

Expressions

INSERT

LIKE

Null Values

Operators (Logical Operators)

SELECT

sp_help

UPDATE

WHERE