IS NULL (SQL)

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 Overview of Predicates.

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

See Also

• SELECT statement, HAVING clause, WHERE clause

• Overview of Predicates