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 

SELECT Name, FavoriteColors FROM Sample.Person
WHERE FavoriteColors IS NOT NULL

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

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

• SELECT statement, HAVING clause, WHERE clause

• Overview of Predicates