Nulls

Comments on Nulls : Nothing to worry about by Phil Zoio as published in Oracle mag (link to global post)
First you should ask what it means from a business point of view to have a null ! Information missing ? not applicable ?
Then COALESCE, IS NULL, IS NOT NULL are the most obvious functions to avoid problems.
Would you feel safe by using only “NOT NULL” fields in your tables ? No : an outer join will bring them back easily.
In scalar expression : SAL+NULL results in NULL, but in boolean expression : SAL (SELECT MAX(SAL) FROM A) and WHERE SAL > ALL (SELECT SAL FROM A) if A is empty, the first query returns nothing (UNKNOWN) but the second will always be TRUE, because SAL > ALL (NULL) is always true.
Is SUM(SAL+COMM) the same as SUM(SAL)+SUM(COMM) ?? No : Nulls are ignored by aggregates functions !
More tricky : WHERE EMPNO IN (SELECT MGR FROM A) if a MGR is NULL : what do you expect ? Nothing : since EMPNO IN (…, NULL, …) is UNKNOWN or FALSE, the query returns nothing.
Conclusion : there are more than one way to write a query : choose the right one …

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: