NULL value in Oracle

NULL is the absence of a value or information. NULL can be assigned (to a column or a variable ...) but it can not be equated with anything.
so treating NULL or column which may contain NULL values should be done carefully.
Any equation with NULL value will return NULL, same thing for Adding, subtracting, multiplying and dividing.

Oracle has number of built-in functions to treat NULL values, we will describe some of these functions:

NVL:
NVL ( expr1 , expr2 )
If expr1 is null, then NVL returns expr2. If expr1 is not null, then NVL returns expr1.
The most frequently used function to replace a NULL value with another value (for example 0 in case of number datatype).

SELECT ename, NVL (comm, 0)
  FROM emp;

NVL2:
NVL2 ( expr1 , expr2 , expr3 )
If expr1 is null, then NVL2 returns expr3. If expr1 is not null, then NVL2 returns expr2.

SELECT ename, NVL2 (comm, 'has commission', 'has no commission')
  FROM emp;

COALESCE:
COALESCE(expr[,expr]…)
Returns the first non-null expr in the expression list.

SELECT COALESCE (NULL, 3, 1)
  FROM DUAL;

NULLIF:
NULLIF( expr1, expr2 )
NULLIF function returns NULL if expr1 and expr2 are equal. Otherwise, it returns expr1.

SELECT NULLIF (ename, ename)
  FROM emp;

SELECT NULLIF (ename, ename || ' ')
  FROM emp;

LNNVL: -New in Oracle 10g-
LNNVL(condition)
Return TRUE if the condition is false or unknown and FALSE if the condition is true.
LNNVL provides a concise way to evaluate a condition when one or both operands of the condition may be null. The function can be used only in the WHERE clause of a query.

SELECT COUNT (*)
  FROM emp
 WHERE lnnvl (comm < .2);

Sorting NULL values:
NULL values come last in ascending order and first in descending order but this order can be overridden by using NULLS FIRST/LAST clause.

SELECT   *
    FROM emp
ORDER BY comm DESC NULLS FIRST;

Note:
It is important to remember that any equation with NULL will return NULL, if we did understand this logic we can explain the reason why any query returns no rows if it has NOT IN operator with list of values (or sub-query) contains NULL values.
The following query retuens no rows:

SELECT ename
  FROM emp
 WHERE empno NOT IN (SELECT mgr
                       FROM emp);

To solve this problem we use NVL function:

SELECT ename
  FROM emp
 WHERE empno NOT IN (SELECT NVL (mgr, 0)
                       FROM emp);

OR EXISTS

SELECT ename
  FROM emp e
 WHERE NOT EXISTS (SELECT mgr
                     FROM emp
                    WHERE mgr = e.empno);

Saad,