Home » SQL & PL/SQL » SQL & PL/SQL » sql null
sql null [message #233409] Thu, 26 April 2007 01:26 Go to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member


what is diff of those two statements

SELECT CASE WHEN NULL=NULL  THEN 'equql'
            WHEN NULL!=NULL THEN 'notequql'
			ELSE 'nothing'
			END 
			FROM dual;
			
SELECT DECODE ( NULL,NULL,'equql','notequql','nothin')FROM dual
Re: sql null [message #233411 is a reply to message #233409] Thu, 26 April 2007 01:31 Go to previous messageGo to next message
martijn
Messages: 283
Registered: December 2006
Location: Netherlands
Senior Member
I believe that you can not use an equality for null values.
It's like saying:
Is this bit of nothing the same as that bit of nothing?

That is kind of akward if you understand what I mean.

So the first statement is (at least in my theory) a bit strange.

Probably decode is a bit smarter and checks for null values.

Re: sql null [message #233415 is a reply to message #233411] Thu, 26 April 2007 01:40 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
ok

i can agree that

is it correct

SELECT DECODE ( NULL,NULL,'equql','notequql','nothin') as null1 FROM dual

null1
.....
equql

Re: sql null [message #233417 is a reply to message #233409] Thu, 26 April 2007 01:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Decode is the only case where NULL can be compare to NULL.
It is an exception.
In any other case, NULL is neither equal nor not equal to NULL. So your results.

Regards
Michel
Re: sql null [message #233423 is a reply to message #233415] Thu, 26 April 2007 02:02 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Your decode probably does not do what you want it to.
DECODE ( NULL,NULL,'equql','notequql','nothin')
          ^
          |- this null is the value to check

               ^
               |- if it is equal to this null,
                     ^ then return this value

                             ^
                             |- if it is equal to this value
                                         ^- then return this value

So, your decode will return 'equql' if your value-to-check (in this case your first NULL) is null, if this value equals 'notequql', then the decode will return 'nothin', in all other cases it will return null.

[Updated on: Thu, 26 April 2007 02:03]

Report message to a moderator

Re: sql null [message #233602 is a reply to message #233409] Thu, 26 April 2007 15:50 Go to previous message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
A much better way to test for null is

select nvl(null,'equql') from dual;

NVL means if the first argument is null, then return the second. If the first argument is not null, then return the first argument.
Previous Topic: Latest Record
Next Topic: Row as Column
Goto Forum:
  


Current Time: Sun Dec 04 13:07:57 CST 2016

Total time taken to generate the page: 0.04711 seconds