How Oracle 10g evaluates NULL in boolean expressions

Question

if not (i_ReLaunch = 1 and (dt_enddate is not null))

How this epression will be evaluated in Oracle 10g when the input value of the i_ReLaunch = null and the value of the dt_enddate is not null it is entering the loop. According to the rules in normal c# and all it should not enter the loop as it will be as follows with the values.

If( not(false and (true)) = if not( false) =if( true) which implies it should enters the loop

But it is not happening

Can someone let me know if i am wrong at any place

Answer 1

Boolean operations with NULL value in Oracle return UNKNOWN - not true or false. So you have something like this:

If( not(UNKNOWN and (true)) = if not( UNKNOWN) =if( UNKNOWN )

In this case, IF will treat UNKNOWN as false.

If i_relaunch can be null, then you need to use some of NULL handling functions(NVL, NVL2, NULLIF, COALESCE, LNNVL) to be sure that you have correct result.

See these article for more information:

• Nulls: Nothing to Worry About
• Fundamentals of PL/SQL. Scroll down to - Handling Null Values in Comparisons and Conditional Statements