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

NULL in Oracle

A column in a table can be defined with the not null constraint.
nvl, nvl2 and lnnvl are SQL constructs that are related to NULL handling.
The set null command in SQL*Plus defines how nulls are displayed in a resultset.

Empty string

Oracle treats the empty string ('') as null. This is not ansi compliant. Consequently, the length of an emtpy string is null, not 0.

Null means unknown value

The value null can be regarded as an unknown value. Therefore, the following select statement returns null:
select 5+7+null+9 from dual;
This is because five plus seven plus an unknown value plus nine is of course unknown as well, hence Oracle returns null. However, aggregate functions such as sum() disregard nulls and return the sum of all non-null values.

Truth table

In the following, I create a truth table for booleans and the operators and and or. I create a table to insert booleans.
create table booleans (
  bool varchar2(5)
Because I cannot store booleans directly in a table, I use varchar2 as the column type and insert the english names for the booleans:
insert into booleans values ('true');
insert into booleans values ('false');
insert into booleans values ('null');
Then, I compare every boolean to every other and print the truth table:
  bool_1   boolean;
  bool_2   boolean;

  bool_and boolean;
  bool_or  boolean;

  res_and  varchar2(5);
  res_or   varchar2(5);

  function string_to_bool(str in varchar2) return boolean is begin
    return case when str = 'true'  then true
                when str = 'false' then false
                when str = 'null'  then null end;

  function bool_to_str(bool in boolean) return varchar2 is begin
    return case when bool =  true   then 'true'
                when bool =  false  then 'false'
                when bool is null   then 'null' end;


  dbms_output.put_line('bool1  bool2|   and     or');

  for b1 in (select bool from booleans) loop
  for b2 in (select bool from booleans) loop

    bool_1 := string_to_bool(b1.bool);
    bool_2 := string_to_bool(b2.bool);

    bool_and := bool_1 AND bool_2;
    bool_or  := bool_1 OR  bool_2;

    res_and  := bool_to_str(bool_and);
    res_or   := bool_to_str(bool_or );

    dbms_output.put_line(lpad(b1.bool, 5) || '  ' ||
                         lpad(b2.bool, 5) || '| ' ||
                         lpad(res_and, 5) || '  ' ||
                         lpad(res_or , 5));
  end loop; end loop;
bool1  bool2|   and     or
 true   true|  true   true
 true  false| false   true
 true   null|  null   true
false   true| false   true
false  false| false  false
false   null| false   null
 null   true|  null   true
 null  false| false   null
 null   null|  null   null
As can be seen, for example, false and null is false. This makes sense because null, being an unknown value, could in this this context either be true or false. Both false and true and false and false are false, hence false and null is certainly false as well. On the other hand, false or null is null because the result is true for false or true and false for false or false, hence the expression's value is unknown, or null.