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 isWHERE 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;
List all EAN codes where the producer is not known:SELECT ean_code FROM items WHERE producer_id IS NULL;
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');
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%');
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.
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