Mimer SQL Documentation TOC PREV NEXT INDEX

Mimer SQL Developer Site


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 search condition:

 WHERE column = NULL
 

will not retrieve any rows since NULL is not equal to anything. 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
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
info@mimer.se
Mimer SQL Documentation TOC PREV NEXT INDEX