OracleBlog

If you read only one sentence per posting, read this:

NULLs may not behave as you'd expect in Oracle, and as a result, NULLs are the cause of many application errors.

I got a fair bit of private feedback on my recent article on the differences between "NULL" and nothing. Here is the link to the original article, and check out Howard Rogers' comments.

http://thinkoracle.blogspot.com/2005/05/null-vs-nothing.html

Let's start with an example to demonstrate a simple property of NULL: that NULL is not equal to anything, including NULL itself.

SELECT * FROM dual WHERE NULL = NULL;

No rows selected.

SELECT * FROM dual WHERE NULL <> NULL;

No rows selected.

What happened here? How can something be both not equal and not not equal to something else at the same time? That probably doesn't make much sense, does it?

Essentially NULL means you don't know the value. Basically, you can't say whether its equal or not equal to anything else. You need to abandon the binary logic you learned in first-year math and embrace tri-value logic.

That was also a clear demonstration of how you should be careful using equals or not equals when dealing with NULLs. Instead, use "IS". Observe:

SELECT * FROM dual WHERE NULL IS NULL;

D
-
X

As an aside, you may be wondering what the heck "dual" is. And what do we do when we have a question? We ask Tom!

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:1562813956388

In my mind, dual is simply a table with a single row that is guaranteed to be there. And when you use it, it makes you look clever because that's what the experts use. :)

While I'm blowing your mind with the behaviour of NULL, check out this other case, kindly provided by Tom Kyte:

IF (x = 'A') THEN something
ELSE something else
END IF;

IF NOT(x = 'A') THEN something else
ELSE something
END IF;

Do these two pieces of pseudo-code look the same to you? In many languages, yes. But not in PL/SQL. Why?

Consider the case that x is NULL. In the first case it will do "something else" because it is not equal to 'A'. In the second case it will do "something" because it is also not not equal to 'A'.

Tom knows I never believe anything without proof, so he provided me with one:

ops$tkyte@ORA9IR2> declare
2 x varchar2(1);
3 begin
4 if ( x = 'A' )
5 then
6 dbms_output.put_line( 'X=A' );
7 else
8 dbms_output.put_line( 'NOT X=A' );
9 end if;
10
11 if NOT( x = 'A' )
12 then
13 dbms_output.put_line( 'NOT X=A' );
14 else
15 dbms_output.put_line( 'X=A' );
16 end if;
17 end;
18 /
NOT X=A
X=A

PL/SQL procedure successfully completed.

Pretty bizarre, eh? Starting to understand the behaviour of NULL? Starting to see why misunderstanding NULL can lead to application errors?

While we are discussing NULLs, here a few more useful things.

First, you can use the "set null" command to change how NULL will appear in SQLPLUS. This will not change anything in the database, or equality, it will just change the appearance.

create table atable (last_name varchar(12));
insert into atable (last_name) values ('Smith');
insert into atable (last_name) values (NULL);

select * from atable;

LAST_NAME
------------
Smith


2 rows selected.

set null [NULL];

select * from atable;

LAST_NAME
------------
Smith
[NULL]

2 rows selected.

Also, you can use these two functions, nvl and nvl2:

nvl(expr_1, expr_2)
Returns expr_2 if expr_1 is null and expr_1 otherwise.

nvl2(expr_1, expr_2, expr_3)
Returns expr_3 if expr_1 is null and expr_2 otherwise.

select nvl(last_name, '[NONE]') as last_name from atable;
LAST_NAME
------------
Smith
[NONE]

2 rows selected.

select nvl2(last_name, 'Y: ' || last_name,'N') as last_name from atable;

LAST_NAME
---------------
Y: Smith
N

2 rows selected.

Those of you who, like me, work on many different databases have hopefully seen by now that NULLs are handled differently in Oracle. As a final demonstration, consider how NULLs are handled in Sybase ASE.

In Sybase ASE (and MS SQL), there is a configuration parameter that tells the database whether to treat NULLs as they are defined in the ANSI SQL standard, or whether to "bend the rules" a bit, and allow "NULL = NULL" to be true. Furthermore, the behaviour changes depending on whether you're in a join clause or a search clause.

http://sybasease.blogspot.com/2005/05/nulls-in-sybase-ase.html

That is not the case in Oracle. As confusing as NULLs may be at first, they are consistent. They will behave the same logically everywhere no matter how and where you use them.

So what should we do?
1. Understand that "NULL = NULL" and "NULL <> NULL" are both false because NULL means "I don't know"
2. Use "IS NULL" instead of "= NULL" if you are checking for NULLness.
3. Use "nvl" and "nvl2" if you want to eliminate the possibility of a NULL in a statement by assigning it a (temporary) value.

For future reference, consult Dan Morgan's page on NULL:
http://www.psoug.org/reference/null.html

This is definitely not the last article you'll see on NULL given how often it is misunderstood. I encourage you to include your own experiences of your favourite NULLisms in the "comments" section. And, naturally, I appreciate corrections and clarifications.

And as a final thanks to Tom, here are some pictures from when he visited us in Calgary this past March.
http://www.coug.ab.ca/events/05-mar.htm