Friday, June 10, 2005

NULLs in Oracle

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

Comments:
My favorite is the line in Chapter 2 of Oracle's SQL Reference

"(Oracle
currently treats a character value with a length of zero as null. However, this may
not continue to be true in future releases, and Oracle recommends that you do not
treat empty strings the same as nulls.)"
 
I have two questions that are only loosely related to your post...

1. Can I get an RSS feed of Steven Feuerstein's Oracle articles the same way I can now get Ask Tom?


There is a RSS feed on Best Practice PL/SQL with Steven Feuerstein - Most Recent on this link

OTN RSS Feed

Hope that Helps
 
Found a good link that talks about NULL and "NOT IN" on AskTom:

http://asktom.oracle.com/pls/ask/f?p=4950:8:9119946321000023065::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:442029737684
 
This comment has been removed by a blog administrator.
 
Note: Connor McDonald has pointed out that there is a performance overhead to using NVL over just checking "is null". Here is the link:

http://www.oracledba.co.uk/tips/plsql_nvl_costs.htm

I wonder if the same penalty exists using decode? Probably. A good future test.
 
From Amis' Blog: http://technology.amis.nl/blog/index.php?p=646

The july/august edition of Oracle Magazine has a very interesting article by Lex de Haan and Jonathan Gennick about Nulls (‘nothing to worry about’) in the database.

http://www.oracle.com/technology/oramag/oracle/05-jul/o45sql.html
 
Connor's article (linked above) is being discussed on the Dizwell Forum:

http://www.phpbbserver.com/phpbb/viewtopic.php?t=220&mforum=dizwellforum
 
The way I had the first point in your summary explained to me is that when you
compare NULL with any value, using anything other than IS NULL or IS NOT NULL, the result is NULL.

The resulting NULL does not then equate to either TRUE or FALSE.

That way we can clearly deduce that

IF NULL = NULL THEN
something
ELSEIF NOT( NULL = NULL )
something else
END IF;

reduces to

IF NULL THEN
something
ELSEIF NULL
something else
END IF;

and so will never do anything.



1. Understand that NULL compared with any value results in NULL, no matter what the comparison is (other than IS NULL or IS NOT NULL).
 
I have got a problem with some older version of Oracle because when I run some select statement with value=DECODE('','',value,'') condition on one instance it works without problem but on another one it doesn't work.

I have tried to execute the query on the instance where it doesn't work:
select Decode ('','',value,'') from dual.

And it works. Where can be the problem? My email is: marcin.firla@apriso.com.pl
 
The people at Oracle are idiots for defining null this way. Of course null has a value and it is hex '00'. You can see it in the ascii table duh. And If you still want to take the stance that null is undefined then I would still have to say that undefined = undefined or nothing = nothing.

Has anyone said why null cannot be equal to null? What is the benefit of having it not equal? I don't know how many times I have forgotten to add the logic to include the check that both fields are null instead of just being able to say they are equal.

If they wanted the value to be undefined then they should have called it undefined or 0-length because null has a value and it is null.

Then to add that an empty string is differnt than nothing is really adding fucntionality that causes more issues with very limited usefullness. Let the people than need to have a difference between:
1. Null
2. Undefined
3. Empty string
code their own logic and leave the rest of us out of it. They should all be the same and equal in Oracle.


David Trombly
 
convert to empty string will get the same result

declare
x varchar2(1);
begin
if ( x = 'A' )then
dbms_output.put_line( 'X=A' );
else
dbms_output.put_line( 'NOT X=A' );
end if;

if NOT( NVL(x, '') = 'A' ) then
dbms_output.put_line( 'NOT X=A' );
else
dbms_output.put_line( 'X=A' );
end if;
end;

NOT X=A
X=A
 
This comment has been removed by a blog administrator.
 
This comment has been removed by the author.
 
It would be nice if oracle could flag a null comparison as an error as opposed to an empty result.
 
This comment has been removed by a blog administrator.
 
Compared NULL with any value, using anything other than IS NULL or IS NOT NULL, the result is NULL.Video Marketing | App Marketing
 
It is a very interesting topic that you’ve written here. The truth is that I’m not related to this, but I think this is a good opportunity to learn more about it.Digital Marketing Services | Video Marketing | App Marketing
 
This is a good common sense Blog. Very helpful to one who is just finding the resources about this part. It will certainly help educate me.
 
I always enjoy reading such posts which provide knowledge based information like this blog. Keep it up.
 
This is a really great post, thanks for sharing. I’m glad I got a chance to check out your blog!
thanks,
ARA
 
OK I get NULL <> NULL or NULL = NULL cannot be determined.

But why does
where column !=

return no row where column value is NULL.

In this case NULL certainly not equal to
 
Post a Comment

<< Home

This page is powered by Blogger. Isn't yours?