SQL in a Nutshell, 3rd Edition

Name

IS Operator

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

Platform	Command
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 the WHERE keyword or AND or OR keywords.

NOT

Inverses the predicate: 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 whether 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 quotation marks, because if you do that, the DBMS will interpret the value as the word “NULL” and not the special value NULL.

Programming Tips and Gotchas

Some platforms support the use of a comparison operator to determine whether 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, as shown here:

SELECT stor_id, ord_date FROM ...