SQL in a Nutshell, 2nd Edition

Name

IS Operator

Synopsis

The IS operator determines whether a value is NULL or not.

Platform	Command
DB2	Supported
MySQL	Supported
Oracle	Supported
PostgreSQL	Supported
SQL Server	Supported

SQL2003 Syntax

{WHERE | {AND | OR} } expression IS [NOT] NULL

Keywords

WHERE | {AND | OR} expression IS NULL

Returns a Boolean value of TRUE if the expression is NULL, and FALSE if the expression is not NULL. The expression evaluated for NULL can be preceded by a the WHERE keyword or AND or OR keywords.

NOT

Inverses the predictate. The statement will instead return a Boolean TRUE if the value of expression is not NULL, and FALSE if the value of expression is NULL.

Rules at a Glance

Because the value of NULL is unknown, you cannot use comparison expressions to determine if a value is NULL. For example, the expressions X = NULL and X <> NULL cannot be resolved because no value can equal, or not equal, an unknown.

Instead, you must use the IS NULL operator. Be sure that you do not put the word NULL within quote marks, because if you do that, the DBMS will interpret the value as the word “NULL” and not the special value of NULL.

Programming Tips and Gotchas

Some platforms support the use of a comparison operator to determine if an expression is NULL. However, all platforms covered by this book now support the ANSI IS [NOT] NULL syntax.

Sometimes, checking for NULL will make your WHERE clause only slightly more complex. For example, rather than a simple predicate to test the value of stor_id:

SELECT ...