Skip to main content
Previous sectionNext section

IS NULL

Determines if a data value is NULL.

Synopsis

scalar-expression IS [NOT] NULL

Description

The IS NULL predicate detects undefined values. You can detect all null values, or all non-null values:

SELECT Name, FavoriteColors FROM Sample.Person
WHERE FavoriteColors IS NULL 
Copy code to clipboard
SELECT Name, FavoriteColors FROM Sample.Person
WHERE FavoriteColors IS NOT NULL
Copy code to clipboard

The IS NULL / IS NOT NULL predicate is one of the few predicates that can be used on a stream field in a WHERE clause. This is shown in the following example:

SELECT Title,%OBJECT(Picture) AS PhotoOref FROM Sample.Employee
WHERE Picture IS NOT NULL
Copy code to clipboard

IS NULL can be used wherever a predicate condition can be specified, as described in the Overview of Predicates page of this manual.

The IS NULL predicate should not be confused with the SQL ISNULL function.

See Also