Skip navigation.

What a strange way to write NULL

gojko's picture

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.

Don't need a hint to use a function based index

(preface that this is for the CBO)
You don't need a hint to employ a function based index. The function you index must be the same as the function you use in the predicate, but the hint is not needed. In fact, it may bias the optimizer to use the hint when it would be more effective not to.

fbi and hints

fair enough - every single time I used a FBI to optimise queries, I had to force Oracle to see it, but then again, maybe I just was not lucky enough. If your experience is different, then "hint must be used" should be "hint may be required for". Thanks for the comment, I'll keep that in mind and check if Oracle would use the index before adding the hint next time.

Gojko Adzic
http://www.gojko.com

Use of NVL and NVL2 can be useful as well

NVL can be a useful tool when you want to replace a NULL value with another value (such as making a NULL numeric value be 0 instead). For instance, your tip #7:

update my_tab set my_number=my_number+3

Could be re-written to also update NULL my_number values with this small modificaton:

update my_tab set my_number = nvl(my_number,0) + 3

NVL2 can also be useful to use one value when a column is NULL, and another when it is not. For example:

SELECT NVL2(NULL, 'MickeyMouse@Disneyland.com', 'DonaldDuck@Disneyland.com'),
       NVL2('a',  'MickeyMouse@Disneyland.com', 'DonaldDuck@Disneyland.com')
  FROM DUAL

thanks

These notes regarding NULLs, and searching for NULL data from table really gives me a lot knowledge.
like - Functioal based index

Thanks a lot

Image Store

How can i store *.doc(like resume.doc) file in oracle 8i.

Null is not Zero

In th post, you indicate that "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", however, that is misleading. Null is not the same as zero, which is why the two values are differentiated. Null is used to indicate a value that is not known. When you conflate zero and null, the math does not make sense, because, as you state, 1 apple does not equal no apples. However, if you restate the question as "does 1 apple equal an unknown quantity of apples?" the answer is clearly "I don't know." Taken this way, it makes sense that the test "1 = null" would render a result of null.