What a strange way to write NULL

A few days ago, while hunting for a bug in PL/SQL code, I stumbled upon the strangest way to write NULL. If the e-mail address parameter was empty, the genius who wrote this PL/SQL procedure set it to , then compared it with ten lines below, in order to log a problem. I really don't know what is it about NULL that scares people so much, but over the years I got used to occasional -1 and 0, or even 'EMPTY'. However, this is the first time I ran across Donald.

Trying to use a NULL value as a normal object in languages like Java or C# typically causes an error. I guess that people coming from Java to PL/SQL may expect that using NULL values in database operations will also break the code. Going a bit more into the history, NULL effectively was equal to zero in C, so that's where the idea of using 0 may come from. And -1? Well, -1 is the typical answer to the question 'What if it CAN be 0?'. I once spent an hour in an argument with a guy who asked 'What should I store if they don't pass the price?'. He was so much against using NULL that I had to write code and show him that (almost) everything works as expected if the price is NULL.

Though using a special constant instead of NULL can simplify comparisons, in my experience it's not worth the trouble. Whatever effort is saved in writing simpler comparisons, it's typically lost with additional effort to make aggregate functions and range conditions work properly. And, on the end, you don't have to worry that some day, someone will create a category named 'EMPTY'.

Using NULL also helps to make the system more consistent. If you want to use a special constant for missing prices, 0 is probably OK. For number of items on stock, 0 is a valid value so -1 will have to be used. For temperatures, both -1 and 0 are valid values, so some other negative value will be used. When date columns and varchars come into the play, situation only gets worse. Making up special constants for 'not existing' makes code very error-prone, as developers will have to remember which value is used in which context.

Logical fallacy

Though the database NULL is much friendlier then it's Java-in-Law, it is not without a few peculiarities. The concept of NULL in the database is undoubtedly very useful, but I think that it could have been implemented better. Nothing turned out to be more complex to use than something. To be fair to SQL language designers, nothingness raised strange philosophical questions throughout history and tortured the smartest minds. Or, as Wikipedia puts it: The concept of "nothing" has been studied throughout history by philosophers and theologians; many have found that careful consideration of the notion can easily lead to the logical fallacy. I doubt that they were thinking about SQL when they wrote that, but they were completely right. NULL is not equal to NULL, nor is it different from NULL. Though common sense leads us to conclusion that a basket with one apple is clearly different from a basket without apples, in Oracle they are not different. Or, to put it in in PL/SQL:

create or replace procedure IsNotApple(b varchar2) as
begin
	if b !='Apple'  then
		dbms_output.put_line('Not Apple');
	else
		dbms_output.put_line('Apple');
	end if;
end;
/

begin	
	isNotApple(Null);
end;
/

So the empty basket is not different from a non-empty one, but then again, they are also not the same. Putting all that in one sentence, the empty is not equal to anything, nor different from anything, including nothing. It is a bit painful, isn't it?

In theory, any binary operation involving NULL will also give NULL, but even that is not consistent. Add NULL to 1 and you will get NULL, but concatenate NULL to 'Donald' and you will get 'Donald'. NULL and empty string, as far as Oracle is concerned, are the same (but then again, not equal). And, to make things worse, there are EMPTY_CLOB and EMPTY_BLOB. Clobs and Blobs are strange enough for themselves, but EMPTY versions exist only to make our life harder.

My top 10 tips for dealing with nothingness

1. Use NULL for non-existing or missing values. Don't make up a special constant for that.

2. Don't forget that IS must be used to check for possible NULL values. If the parameter can be NULL (as in the example with apples), be sure to check (b!='Apple' or b is NULL). If both parameter and value can be NULL, to check for equality try both cases: name = :name or (:name is null and name is null).

3. In SQL, use DECODE to compare values that can be NULL. Decode follows common sense logic - to see that, just try:

Select decode(null,null,1,0), decode(null,'a',1,0),decode('a','a',1,0),decode('b','a',1,0) from dual

So, instead of:

where name = :name or (:name is null and name is null)

you can write

decode(name,:name,1)=1

4. To avoid logical fallacies, it's often better to check for equality then for difference. For example, this would print the correct result even if b is Null:

if b = 'Apple'  then
	dbms_output.put_line('Apple');
else
	dbms_output.put_line('Not Apple');
end if;

5. Use NULL for non-existing CLOBs and LOBs. EMPTY_CLOB and EMPTY_LOB are initialiser functions required to put the content into the LOB. But if you don't want to store anything in the LOB yet, use NULL instead. EMPTY_CLOB will allocate storage, NULL will not.

6. Remember that empty string is also NULL, so comparing if something is equal to an empty string does not work. (try IsNotApple('') to see the effect).

7. Remember that concatenation (||) with NULL does not produce NULL - most other binary operators do. The following update will skip all NULLs:

update my_tab set my_number=my_number+3

But this one will change NULL fields into 'Donald':

update my_tab set my_varchar2=my_varchar2 ||'Donald';

8. NULL values are excluded from B-tree indexes - so searching for NULL values will cause a full table scan. Bitmap indexes can be used to search for NULLs, but they require significant overhead for maintenance, and are not appropriate for tables that are frequently updated (see http://www.dba-oracle.com/oracle_tips_bitmapped_indexes.htm and http://www.oracle.com/technology/pub/articles/sharma_indexes.html).

9. Searching for NOT NULL values in B-Tree indexed column will can use a full index scan, but sometimes Oracle has to be forced to use it with an optimiser hint.

10. Function based indexes can be used to search for NULL values - just use a function to turn NULL into a specific, non-NULL value:

create  index temp3_i on temp1(decode(null,b,1));

select /*+INDEX (temp1 temp3_i)*/ * from temp1 where decode(null,b,1)=1

Remember that FIRST_ROWS or INDEX hint must be used to utilise a function-based index.

About the author

Gojko Adzic is an IT consultant specialised in designing and building high-throughput transaction processing systems and enterprise system integrations. His story so far includes equity and energy trading, mobile content delivery, e-commerce, online betting and complex configuration management. In his free time, Gojko maintains a blog about programming on gojko.net.