All about NULL in Oracle - Part II

Logical Operation

When you use NULL in Logical Operations, it has some intersting result but logical.
Note : Boolean is not a Valid Data Type in SQL...it is valid only in PL/SQL

The Truth table is as follows

Op1 Operator Op2 Result
------+------------+----------+---------
True | AND | NULL | NULL
False | AND | NULL | FALSE
True | OR | NULL | TRUE
False | OR | NULL | NULL
----------------------------------------

So Logically Thinking we can come to the conclusion why two of the above four is returning Boolean.

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.
False And => putting Unknown as True or False...the Result is always 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.

note: In the following Example, Since Boolean is not supported directly in SQL, i have manipulated it using Relational Operators.



SQL> Select 'TRUE' Res From Dual Where (1 = 1) OR (1 > NULL);

RES
----
TRUE


In the above query, '1 = 1' will return True and (1 > NULL) returns NULL ....since True or NULL is True,
whatever is there in the Select is returned.


SQL> Select 'False' Res From Dual Where Not ((1 > 1) and (1 > NULL));

RES
-----
False

The above query is for False and Null Returns False..so applying a NOT Operator will make it True and ouput is returned.