OraFAQ Forum: SQL & PL/SQL » The Book Of Null

Home » SQL & PL/SQL » SQL & PL/SQL » The Book Of Null

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

The Book Of Null [message #600816]

Tue, 12 November 2013 16:13

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

Perhaps the Book Of Null really should be written (any volunteers here?)
From Googling around, I think that Oracle has gone away from the SQL standard. A concatenation with NULL should return NULL, and some third party databases do just that. And of course NULL is not equal to anything, certainly not to another NULL. However, Oracle's implementation is documented and consistent, in both SQL and PL/SQL. See these examples:

orclz>
orclz> select * from dual where 'a'||null = 'a'||'';

D
-
X

orclz> select * from dual where 'a'='a' and null=null;

no rows selected

orclz> select * from dual where null=null;

no rows selected

orclz> select * from dual where null='';

no rows selected

orclz> select * from dual where ''='';

no rows selected

orclz> var v varchar2(1)
orclz> exec :v:=null

PL/SQL procedure successfully completed.

orclz> select * from dual where :v='';

no rows selected

orclz> exec :v:='';

PL/SQL procedure successfully completed.

orclz> select * from dual where :v=:v;

no rows selected

orclz>

Report message to a moderator

Re: Difference between null & Empty string [message #600876 is a reply to message #600816]

Wed, 13 November 2013 08:17

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

Adding to the Book Of Null, decode does not follow the rules:

SQL> select decode(null,null,'null equals null','not does not equal null') from dual;

DECODE(NULL,NULL
----------------
null equals null

Report message to a moderator

Re: Difference between null & Empty string [message #600878 is a reply to message #600876]

Wed, 13 November 2013 08:31

cookiemonster
Messages: 13922
Registered: September 2008
Location: Rainy Manchester

Senior Member

And there's this little oddity:

SQL> DECLARE
  2  
  3  chara  CHAR(1);
  4  charb  CHAR(1);
  5  vara   VARCHAR2(1);
  6  varb   VARCHAR2(1);
  7  
  8  BEGIN
  9  
 10    chara := '';
 11    charb := NULL;
 12    vara := '';
 13    varb := NULL;
 14  
 15    dbms_output.put_line('chara $'||chara||'$');
 16    dbms_output.put_line('charb $'||charb||'$');
 17    dbms_output.put_line('vara $'||vara||'$');
 18    dbms_output.put_line('varb $'||varb||'$');
 19  
 20  END;
 21  /
 
chara $ $
charb $$
vara $$
varb $$
 
PL/SQL procedure successfully completed
 
SQL>

chara is set to a space, the rest are null.

Report message to a moderator

Re: Difference between null & Empty string [message #600880 is a reply to message #600878]

Wed, 13 November 2013 08:44

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

More "oddities":

DECLARE
    flag CHAR(2);
    PROCEDURE check_null (p_flag IN CHAR)
      IS
      BEGIN
        IF p_flag = '  '
          THEN
            dbms_output.put_line ('flag is equal to ''  ''');
          ELSIF p_flag IS NULL
            THEN
              dbms_output.put_line ('flag is null');
            ELSE
              dbms_output.put_line ('other');
        END IF;
      END;
BEGIN
    flag := '';
    check_null (flag);
    flag := NULL;
    check_null (flag);
END;
/
flag is equal to '  '
flag is null

PL/SQL procedure successfully completed.

alter session set events '10932 trace name context forever, level 16384'
/

Session altered.

DECLARE
    flag CHAR(2);
    PROCEDURE check_null (p_flag IN CHAR)
      IS
      BEGIN
        IF p_flag = '  '
          THEN
            dbms_output.put_line ('flag is equal to ''  ''');
          ELSIF p_flag IS NULL
            THEN
              dbms_output.put_line ('flag is null');
            ELSE
              dbms_output.put_line ('other');
        END IF;
      END;
BEGIN
    flag := '';
    check_null (flag);
    flag := NULL;
    check_null (flag);
END;
/
flag is null
flag is null

PL/SQL procedure successfully completed.

SCOTT@orcl >

SY.

Report message to a moderator

Re: Difference between null & Empty string [message #600897 is a reply to message #600880]

Wed, 13 November 2013 11:56

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

I hope I did not miss if the following oddity has been already addressed :

Another oddity is with the comparison in a IF-ELSE block :


   if ( x = y ) 
   then 
      ......
   end if;

   if ( a <> b )
   then 
      ......
   end if;

The condition would fail when there is a situation of a NULL.

A link from asktom, http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:17320984423926

Report message to a moderator

Re: The Book Of Null [message #600904 is a reply to message #600897]

Wed, 13 November 2013 12:22

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

One should mention that Oracle does provide the sys_op_map_nonnull function that lets you compare nulls,

orclz>  select * from dual where sys_op_map_nonnull(null)=sys_op_map_nonnull(null);

D
-
X

orclz>

I had some help with Michel on the way this is used in MVs a few months ago,
http://www.orafaq.com/forum/mv/msg/182360/558928/#msg_558928

--update: I split this lot off from OP's question, I don't think it was helping er.

[Updated on: Wed, 13 November 2013 12:23]

Report message to a moderator

Re: The Book Of Null [message #600908 is a reply to message #600904]

Wed, 13 November 2013 12:58

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

John Watson wrote on Wed, 13 November 2013 13:22

One should mention that Oracle does provide the sys_op_map_nonnull function that lets you compare nulls

Well, I'd be very careful with sys_op_map_nonnull:

SCOTT@orcl > select sys_op_map_nonnull(to_char(null)) from dual;

SY
--
FF

SCOTT@orcl > select 1 from dual where sys_op_map_nonnull(null) = 'FF';

         1
----------
         1

SCOTT@orcl >

SY.
P.S. Even though it works completely different from sys_op_map_nonnull, I think LNNVL is also worth mentioning.

Report message to a moderator

Previous Topic:	Difference between null & Empty string
Next Topic:	Query to find users logged in lasttime

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed May 08 12:25:42 CDT 2024