Tri-state boolean

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:

left right and or
true true true true
true false false true
false true false true
false false false false

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

left right 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

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.

Leave a Reply

Your email address will not be published.