NULL = NULL ::: sys_op_map_nonnull

Today came across this feature " sys_op_map_nonnull " which allows you to compare NULL = NULL .

Lets make our hands dirty .......

Create the Table :

create table p2 (
c number,
d number primary key);



>desc p2
 Name         Null?    Type
 ---------- -------- ----------------
 C                     NUMBER
 D            NOT NULL NUMBER

Insert the Data :

insert into p1 values (NULL,2);

insert into p2 values (NULL,1);

insert into p2 values (NULL,3);

insert into p2 values (2,4);

>select * from p2;

         C          D

---------- ----------

                    1

                    3

         2          4

Select the data by comparing column C with Column C :

>select * from p2 where c=c;

         C          D

---------- ----------

         2          4

one of the Traditional Way to resolve NULL compare:

>select * from p2 where (c=c or (c is null and c is null));

         C          D

---------- ----------

                    1

                    3

         2          4

Now try with sys_op_map_nonnull,

>select * from p2 where sys_op_map_nonnull(c)=sys_op_map_nonnull(c);

         C          D

---------- ----------

                    1

                    3

         2          4