NULL

All scalar functions (except REPLACE, NVL, and CONCAT) return null when given a null argument. For example, consider a query that averages the five values 1000, null, null, null, and 2000. Such a query ignores the nulls and calculates the average to be (1000+2000)/2 = 1500. The expression NVL(COMM,0) returns 0 if COMM is null or the value of COMM if it is not null.

Any arithmetic expression containing null values evaluates to NULL.

To test for nulls, use only the comparison conditions IS NULL and IS NOT NULL.

When working with nulls, you can avoid some common mistakes by keeping in mind the following rule:

§  Simple comparisons involving nulls always yield NULL.

§  Applying the logical operator NOT to a null yields NULL.

§  In conditional control statements, if the condition yields NULL, its associated sequence of statements is not executed

a)       x:=5;

y:= NULL;

...

IF x! = y THEN   -- Yields NULL, not TRUE

                    Sequence of statements;  -- not executed

END IF;

b)       a:= NULL;

b:= NULL;

...

IF a=b THEN  --Yields NULL, not TRUE

                    Sequence of statements; -- not executed

END IF;