During all the years I have been working with DB2 I have constantly tried to avoid using NULL values. On the other hand I am quite fond of outer joins which introduces NULL values at a greater scale. Fortunately these NULL values may be converted into something more useful by the VALUE function which I have already described. The use of NULL values in the WHERE clause seems to me to be harder to understand than in the SELECT list. I will try to ease the pain in this tip.

Assume column COLN in table TABLEX is able to contain NULL values and in fact has some rows where COLN is set to NULL. Here is an example of a SQL statement retrieving data from TABLEX:

select COL1, COL2, COL3
from TABLEX
where COLN <> ''

Now it is a real good question whether rows where COLN is NULL are returned. The correct answer is: they are not returned. NULL is not equal to, greater than, less than or different from any normal value. In other words: if you are referring a column in your where clause that contains NULL values, the correspondent rows will not be returned.

The only way to return rows containing NULL values in COLN is to change the where clause in the above example to COLN <> '' OR COLN IS NULL. Because of the unique status of the NULL value SQL has built in the two predicates COLN IS NULL or COLN IS NOT NULL. You are not able to write COLN = NULL or COLN <> NULL. This is a syntax error.

When it comes to outer joins I have many times seen that the special status of the NULL value is not understood. Let me give you another example:

select A.COL1, B.COL2
from TABLEA A left outer join TABLEB B
  on A.JOINCOL = B.JOINCOL
where A.COL1 > 100
  and B.COLX = 'X'

This SQL statement is a good example of how many of us gets our first impression of the outer join capability. Before executing the above example we have tried without the AND part. We see that outer joins works perfectly well and we are quite happy and put our hands in the air. Then we add a predicate that references a column in the table that we outer join with, in the example AND B.COLX = 'X', we execute the statement and the result is disappointing. Suddenly the SQL statement works like an ordinary inner join and our hands come down quickly. Many programmers have turned their back on outer joins after this experience, because there must be something wrong with DB2. But DB2 works as designed. The problem is caused by the fact that if a row in TABLEA does not match a row in TABLEB all columns in TABLEB are assigned the NULL value for the "corresponding" row in TABLEB. B.COLX becomes NULL for those rows and B.COLX = 'X' does not match NULL.

The solution in the above example is either to move AND B.COLX = 'X' up as a part of the ON predicate (which is both possible and legal) or to change the AND B.COLX = 'X' to AND (B.COLX = 'X' OR B.COLX IS NULL). You must be aware that these two solutions may produce different results depending on the data contents. Please remember to try both possibilities to find out which of the options that produces the result most suitable for you.