René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback
 

NULLs as operands [Oracle SQL]

A null value compared to any other value, including nulls, is null. This is demonstrated in the following.
First a table is created:
create table operand (
  op   number
);
The table is filled with two non-null values and a null:
insert into operand values (  11);
insert into operand values (  42);
insert into operand values (null);
Then, the table is cross joined with itself so that each value can be compared to each other.
select op_left.op                                   "op left",
  case when op_left.op = op_right.op then ' = ' 
       when op_left.op > op_right.op then ' > '
       when op_left.op < op_right.op then ' < '
                                     else ' ? ' end "rel",
       op_right.op                                  "op right"
from
  operand op_left cross join operand op_right;


As the result shows: any null value is neither <, = or > than the other value, which is indicated by a ?:
   op left rel   op right
---------- --- ----------
        11  =          11
        11  <          42
        11  ?
        42  >          11
        42  =          42
        42  ?
            ?          11
            ?          42
            ?
See also sys_op_map_nonnull.