SQL Pocket Guide, 3rd Edition

Nulls

When writing SQL, it’s critical to understand nulls and three-valued logic. With few exceptions, the result of any expression involving a null will be either null or unknown, and this has ramifications for any expression (comparison or otherwise) that you write.

Predicates for Nulls

You should not compare a null to any other value using the standard comparison operators. For example, the following query will not return all rows from the upfall table:

SELECT u.id, u.name, u.datum
FROM upfall u
WHERE u.datum = 'NAD1927'
   OR u.datum <> 'NAD1927';

You’d think that any given datum would either be NAD1927 or not be NAD1927, but this is not the case. A null datum is not NAD1927, nor is it not not NAD1927.

SQL provides the IS NULL and IS NOT NULL predicates to detect the presence or absence of null. To find all datum values other than NAD1927, including those that are null, specify:

SELECT u.id, u.name, u.datum
FROM upfall u
WHERE u.datum IS NULL
   OR u.datum <> 'NAD1927';

Similarly, you can use IS NOT NULL to match non-null values explicitly.

Using CASE with Nulls

CASE expressions can sometimes be helpful when working with potentially null data. For example, you can use CASE to ensure that you always get a non-null datum in your result set:

SELECT u.id, u.name,
  CASE WHEN u.datum IS NULL THEN
     '*None!*'
  ELSE u.datum END
FROM upfall u;

Most databases also provide functions to do this type of thing more succinctly.

Using the COALESCE Function

All platforms support the standard SQL COALESCE function. ...