If a table has NULL values in it, a special pair of comparison operators
that can be used to include or omit NULL valued fields. You can check for fields set to
NULL using the IS NULL keyword phrase. In order to check
for a non-NULL value, use the IS NOT NULL keyword
phrase.
Example 5-15 uses the IS NULL keyword to check for
authors whose first_name column value are set to
NULL.
Example 5-15. Comparisons using IS NULL
booktown=#
SELECT last_name, first_name
booktown-#
FROM authors
booktown-#
WHERE first_name IS NULL;
last_name | first_name
-----------+------------
Geisel |
(1 row)
Examining Example 5-15 and Example 5-16, you might think that
the syntax in the two statements provided are identical. There is, however, a key difference.
Example 5-16. Comparisons equal to NULL
booktown=#
SELECT last_name, first_name
booktown-#
FROM authors
booktown-#
WHERE first_name = NULL;
last_name | first_name
-----------+------------
Geisel |
(1 row)
PostgreSQL provides a translation from = NULL to IS NULL,
and likewise for the != NULL operation with IS NOT NULL. This
is provided only for compatibility with existing client applications (such as Microsoft Access).
When comparing a value to NULL in an expression, be in the habit of using the
IS NULL and IS NOT NULL keyword operators rather
than the = or != math-style operators. While this
translation is provided for the sake of compatibility with other systems, it may be discontinued in the
future, as it is not a standard SQL procedure (and it is certainly not guaranteed to be a portable procedure to other
SQL-based database systems for the same reason).
Any as-yet undiscussed comparison operator used on a NULL value will return a
NULL value, as NULL will never be larger, smaller, or
otherwise related to any non-NULL value. (See Example 5-17.) A direct query on the result of a comparison
against a NULL value will therefore return NULL. You can
think of a NULL value as being a sort of SQL black hole, from which no comparison (outside
of IS NULL, and its special = translation) may return
true, and to which no values may be added, or concatenated.
Example 5-17. Using operators with NULL values
booktown=#
\pset null *null*
Null display is '*null*'.
booktown=#
SELECT 5 > NULL;
?column?
----------
*null*
(1 row)
booktown=#
SELECT NULL IS NULL;
?column?
----------
t
(1 row)
booktown=#
SELECT NULL || 'Test';
?column?
----------
*null*
(1 row)
