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
            ?


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