Home » SQL & PL/SQL » SQL & PL/SQL » Null comparison
Null comparison [message #246579] Thu, 21 June 2007 06:35 Go to next message
caliguardo
Messages: 107
Registered: February 2007
Location: Chennai
Senior Member

select decode(null,null,'true','false') from dual;

This Returns true. Here i have have not given null as a string.
How is this possible?.
Re: Null comparison [message #246580 is a reply to message #246579] Thu, 21 June 2007 06:46 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
SQL Reference, Function DECODE:
Quote:
In a DECODE function, Oracle considers two nulls to be equivalent. If expr is null, then Oracle returns the result of the first search that is also null.
Re: Null comparison [message #246581 is a reply to message #246580] Thu, 21 June 2007 06:56 Go to previous messageGo to next message
caliguardo
Messages: 107
Registered: February 2007
Location: Chennai
Senior Member

Very Well. Got u .Thanks.
Re: Null comparison [message #246585 is a reply to message #246579] Thu, 21 June 2007 07:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
NULL is of VARCHAR2 datatype.
Weird, isn't it?
One of the Oracle's magical stuff!

Regards
Michel


icon5.gif  Re: Null comparison [message #246589 is a reply to message #246585] Thu, 21 June 2007 07:15 Go to previous messageGo to next message
caliguardo
Messages: 107
Registered: February 2007
Location: Chennai
Senior Member

But it cannot be included in any of the data types. right? Since even '' is a null. as far as the word is considered it is varchar2 but the value is of no type.

Also it can be boolean too.

[Updated on: Thu, 21 June 2007 07:16]

Report message to a moderator

Re: Null comparison [message #246593 is a reply to message #246589] Thu, 21 June 2007 07:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes and no.
As I said, it is one of the weird things from Oracle:
SQL> select null c from dual
  2  union all
  3  select 't' from dual;
C
-

t

2 rows selected.

SQL> select null c from dual
  2  union all
  3  select 1 from dual;
select null c from dual
       *
ERROR at line 1:
ORA-01790: expression must have same datatype as corresponding expression

That was in 8.1.7.4 and below. It is now better handled in later versions but it clearly show that Oracle see null first as a VARCHAR2 even if it can compare with any type:
SQL> select * from dual where 1 is null;

no rows selected

SQL> select * from dual where 1 is not null;
D
-
X

1 row selected.

Regards
Michel
Re: Null comparison [message #246599 is a reply to message #246593] Thu, 21 June 2007 07:42 Go to previous messageGo to next message
caliguardo
Messages: 107
Registered: February 2007
Location: Chennai
Senior Member

what can be the difference between ' ' and null?

A space is of which data type? Varchar2?can't be i guess.



Re: Null comparison [message #246600 is a reply to message #246599] Thu, 21 June 2007 07:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, a space is VARCHAR datatype. What else?
A space is different from null.
An empty string is null.

Regards
Michel

Re: Null comparison [message #246606 is a reply to message #246600] Thu, 21 June 2007 07:59 Go to previous message
caliguardo
Messages: 107
Registered: February 2007
Location: Chennai
Senior Member

interesting!!! Razz
Previous Topic: Regarding Object level privileges........
Next Topic: utl_file.put_line
Goto Forum:
  


Current Time: Sun Dec 04 06:22:20 CST 2016

Total time taken to generate the page: 0.05475 seconds