SQL Tuning - Using NULL Values

SQL Tuning - Using NULL Values : 

When inexperienced programmers first encounter a null valued column or an equation involving a null constant, they are generally surprised and confused by the outcome. Programmers, developers, and analysts should all have a complete understanding of the null value and its properties. Once understood, null is no different from (or more mystical than) any other database column.

In general:

Null is never equal to (=) anything (including zero, space, or null).

Null is never NOT equal to (!= or <>) anything.

Null is never less than (<) or less than or equal to (<=) anything.

Null is never greater than (>) or greater than or equal to (>=) anything.
Programmers should never directly compare null to anything else. If you perform a comparison to a null value, the record will be rejected.

As with all good rules, there are a few exceptions. Null is in fact equal to null in the following limited situations:

DECODE parameters (where NULL is a value within the comparison list)
   SELECT  DECODE (sex,'M','Male','F','Female',NULL,'Unknown') Sex
   FROM    emp
   WHERE   emp_no = 1234
   EMP_NO    SEX
   ------    -----
    1234     Male
GROUP BY parameters (where several of rows have a column with a null value)
   SELECT  sex, count(*)
   FROM    emp
   GROUP BY sex
   SEX     Count(*)
   -----   --------
   M         3214
   F          956
   null        12
DISTINCT parameters (where a number of rows have a column with a null value)
   SELECT  count(DISTINCT sex)
   FROM    emp
   Count(distinct *)
   -----------------
          3
UNION/MINUS/INTERSECT columns (where a number of rows have a column with a null value)
   SELECT  emp_no,  sex
   FROM    emp
   MINUS
   SELECT  emp_no,  null
   FROM    emp_history
   sql> No rows selected
Note:  Oracle's treatment of null conforms to ANSI standards.