Skip to main content

This is documentation for Caché & Ensemble. See the InterSystems IRIS version of this content.

For information on migrating to InterSystems IRIS, see Why Migrate to InterSystems IRIS?

IS NULL (SQL)

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 
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

Feedback