All data types in the Oracle database implement the null value. Null is a very special value which means ‘Unknown’. That means that the value is really unknown. Null is not equal to null, because both are not known. Therefore you cannot compare them to each other.

Even the boolean value has this null value implemented. Where you should expect a boolean value to hold either true or false, in Oracle a boolean value can also hold null as a value. So a boolean value has three possibilities instead of the two you normally expect.

Normally you would have this truth table for comparing boolean values:

According to this page, you have the following truth table for comparing boolean values:

This means you either have to build you software around these possibilities or you can treat the null value as if it were false, i.e. just test for true value. This is mostly how Oracle PL/SQL treats this value. In an if statement everything that results in null, will mean the else branch will be executed.

I have for example an overloaded function in my utilities package implementing an ifelse function.

  FUNCTION ifelse(
    if_in                          IN      BOOLEAN
  , then_in                        IN      BOOLEAN
  , else_in                        IN      BOOLEAN )
    RETURN BOOLEAN;

this function is overloaded for different datatypes, but the implementation is roughly the same for all versions:

  FUNCTION ifelse(
    if_in                          IN      BOOLEAN
  , then_in                        IN      BOOLEAN
  , else_in                        IN      BOOLEAN )
    RETURN BOOLEAN
  IS
    lv_returnvalue                           BOOLEAN;
  BEGIN
    IF if_in THEN
      lv_returnvalue   := then_in;
    ELSE
      lv_returnvalue   := else_in;
    END IF;

    RETURN lv_returnvalue;
  END;

This implementation treats every value other than true as a false value. So, if I use this function if will have a work around for the null value and I can use boolean values as if they were (normal) two-state booleans. I can, for instance, use this function as a wrapper to work around possible null values in my if-statements.

Where I would have currently have this code:

if (a = b) and (c <> d)  then

where the values of the variables could be null independently, I would make this code:

if bar$util.ifelse((a = b) and (c <> d),true,false)  then

Now I am sure that the null values are treated as if they were false and I don’t have to worry about what the values really are.