All about NULL in Oracle - Part IV

NULLs affect IN and EXISTS

An IN is functionally equivalent to the = ANY clause , which means an OR Condition.

SQL> Select 1 From Dual where 1 in (1, NULL);

1
----------
1

Because True or NULL will return True.
Same Exists is also same as IN..It returns True even if NULL is returned by the Subquery.Exists checks whether a row exists..it doesn't check whether it is NULL or Not.

SQL> select 'true' val from dual where exists (select null from dual);

VAL
----
true

The difficult part comes when you use NOT IN.
The NOT IN clause is virtually equivalent to comparing each value with = and failing if any test is FALSE or NULL. It is equivalent to AND of all conditions, even if one of the condition is false or NULL, it wont return.


SQL> select 'true' from dual where 1 not in (null,2);

no rows selected

if you understand the functionalities of IN , EXISTS and NOT IN then you can avoid common errors.

Undocumented Functions

sys_op_map_nonnull() is a undocumented Function to make NULL values equal.
It does not affect the Normal Comparisons.

SQL> Select 1 From Dual Where sys_op_map_nonnull('A') = sys_op_map_nonnull('A');

1
----------
1

SQL> Select 1 From Dual Where sys_op_map_nonnull(NULL) = sys_op_map_nonnull(NULL);

1
----------
1