|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.