Handling NULL Values

---

NULL values require special handling in SQL queries. NULL represents an unknown value, and strictly speaking NULL is never equal to NULL. (NULL values are however treated as equal for the purposes of GROUP BY, DISTINCT and UNION).

Searching for NULL

The condition for selecting NULL values is

 WHERE column IS NULL
 

The negated form (WHERE column IS NOT NULL) selects values which are not NULL (i.e. values which are known).

List all currencies, and their codes, where the exchange rate is not known:

 SELECT currency, code
    FROM currencies
    WHERE exchange_rate IS NULL;

Result:

CURRENCY	CODE
Armenian Drams	AMD
Kwanza	AOA
Brunei Dollars	BND
Francs Congolais	CDF
Saint Helena Pounds	SHP
Somali Shillings	SOS
Somoni	TJS

List all EAN codes where the producer is not known:

 SELECT ean_code
    FROM items
    WHERE producer_id IS NULL;

Result:

EAN_CODE
4988002364947
4988011353147

List all EAN codes issued to Llewellyn Publications, where the release date is not known:

 SELECT ean_code
    FROM items
    WHERE release_date IS NULL
    AND PRODUCER_ID = (SELECT producer_id
                          FROM producers
                          WHERE producer = 'Llewellyn Publications');

Result:

EAN_CODE
9780875428697
9780875428949
9780875428260
9780875428680
9780875427386

Null values in ALL, ANY, IN and EXISTS Queries

Null values should be treated cautiously, particularly in ALL, ANY, IN and EXISTS queries.

The result of a comparison involving NULL is unknown, which is generally treated as false. This can lead to unexpected results.

For example, neither of the following conditions are true:

 <null>     IN (...,null,...)
 <null> NOT IN (...,null,...)
 

The first result is almost intuitive: since NULL is not equal to NULL, NULL is not a member of a set containing NULL.

But if NULL is not a member of a set containing NULL, the second result is intuitively true.

In fact, neither result is true or false: both are unknown. If NULL values are involved on either side of the comparison, IN and NOT IN are not complementary. Similar arguments apply to queries containing ALL or ANY, for example:

 SELECT currency, code
    FROM currencies
    WHERE exchange_rate > ALL (SELECT exchange_rate
                                  FROM currencies
                                  WHERE currency LIKE 'D%');

Result:

CURRENCY	CODE
Belarussian Rubles	BYR
Maticais	MZM
Lei	ROL
Turkish Liras	TRL

This query works as long as there are no NULL values returned by the subselect. But perform the subselect against a range of currencies that contain a NULL value in the exchange rate, and the query results in an empty set:

 SELECT currency, code
    FROM currencies
    WHERE exchange_rate > ALL (SELECT exchange_rate
                                  FROM currencies
                                  WHERE currency LIKE 'A%');
 

Moreover, the reverse query, currencies that have a lower exchange rate, also results in an empty set. A justification for this is that as long as an exchange rate is unknown, it is impossible to say whether other currency rates are greater or less.

Using Exists

It is always possible to rephrase a query using ALL, ANY or IN in terms of one using EXISTS (with an outer reference between the selection and the EXISTS condition). This is to be recommended if the NULL indicator is to be permitted in the comparison sets, since NULL handling is then written out explicitly in the query.

Distinctions between queries involving NULL comparisons are subtle and are easily overlooked.

It is essential that the aim of a query is stringently defined before the query is formulated in SQL, and that the possible effects of NULL values in the search condition are considered.

There are many real-life examples where the presence of NULL has resulted in unforeseen and sometimes misleading data retrievals. It is advisable to define all columns in the database tables as NOT NULL except those where unknown values have a specific meaning. In this way the risks of confusion with NULL handling are minimized.

Mimer Information Technology AB Voice: +46 18 780 92 00 Fax: +46 18 780 92 40 info@mimer.se