Understand Nulls values into Oracle (2) : Nulls in Conditions

This second post, we will talk about how Oracle evaluate conditions :

        A condition that evaluates to UNKNOWN acts almost like FALSE. For example, a SELECT statement with a condition in the WHERE clause that evaluates to UNKNOWN returns no rows. However, a condition evaluating to UNKNOWN differs from FALSE in that further operations on an UNKNOWN condition evaluation will evaluate to UNKNOWN. Thus, NOT FALSE evaluates to TRUE, but NOT UNKNOWN evaluates to UNKNOWN.

Table 2-20 shows examples of various evaluations involving nulls in conditions. If the conditions evaluating to UNKNOWN were used in a WHERE clause of a SELECT statement, then no rows would be returned for that query.

Table 2-20 Conditions Containing Nulls

Condition	Value of A		Evaluation
a IS NULL	10	SQL> SELECT 1 FROM DUAL WHERE 10 IS NULL; No rows selected	FALSE
a IS NOT NULL	10	SQL> SELECT 1 FROM DUAL WHERE 10 IS NOT NULL;          1 ----------          1	TRUE
a IS NULL	NULL	SQL> SELECT 1 FROM DUAL WHERE NULL IS  NULL;          1 ----------          1	TRUE
a IS NOT NULL	NULL	SQL> SELECT 1 FROM DUAL WHERE NULL IS NOT  NULL; No rows selected	FALSE
a = NULL	10	SELECT 1 FROM DUAL WHERE NULL=10; No rows selected	UNKNOWN
a != NULL	10	SELECT 1 FROM DUAL WHERE NULL=10; No rows selected	UNKNOWN
a = NULL	NULL	SQL> SELECT 1 FROM DUAL WHERE N!ULL=NULL; No rows selected	UNKNOWN
a != NULL	NULL	SQL> SELECT 1 FROM DUAL WHERE NULL!=NULL; No rows selected	UNKNOWN
a = 10	NULL	SELECT 1 FROM DUAL WHERE NULL=10; No rows selected ucune ligne sÚlectionnÚe	UNKNOWN
a != 10	NULL	SELECT 1 FROM DUAL WHERE NULL!=10; No rows selected	UNKNOWN
NOT(a=NULL)	NULL	SQL>  select * from dual where not(null=null) No rows selected	UNKNOWN

For the truth tables showing the results of logical conditions containing nulls, see Table 7-5, Table 7-6, and Table 7-7.

Nulls with Comparison Conditions

To test for nulls, use only the comparison conditions IS NULL and IS NOT NULL. If you use any other condition with nulls and the result depends on the value of the null, then the result is UNKNOWN. Because null represents a lack of data, a null cannot be equal or unequal to any value or to another null. However, Oracle considers two nulls to be equal when evaluating a DECODE function. Refer toD ECODE for syntax and additional information.

Oracle also considers two nulls to be equal if they appear in compound keys. That is, Oracle considers identical two compound keys containing nulls if all the non-null components of the keys are equal.(Constraints)

In next posts we will continue to explore more behavior related to nulls values .

Last post: Understand Nulls values into Oracle (1)