本文件测试用到的表和数据.

    DROP TABLE null_test_tab;

    CREATE TABLE null_test_tab (
      id   NUMBER,
      col1 VARCHAR2(10),
      col2 VARCHAR2(10),
      col3 VARCHAR2(10),
      col4 VARCHAR2(10)
    );

    INSERT INTO null_test_tab values (1, 'ONE', 'TWO', 'THREE', 'FOUR');
    INSERT INTO null_test_tab values (2, NULL, 'TWO', 'THREE', 'FOUR');
    INSERT INTO null_test_tab values (3, NULL, NULL, 'THREE', 'FOUR');
    INSERT INTO null_test_tab values (4, NULL, NULL, 'THREE', 'THREE');
    COMMIT;

   查询数据会得到下面的输出结果：

    SQL> SELECT * FROM null_test_tab ORDER BY id;

            ID COL1       COL2       COL3       COL4
    ---------- ---------- ---------- ---------- ----------
             1 ONE        TWO        THREE      FOUR
             2            TWO        THREE      FOUR
             3                       THREE      FOUR
             4                       THREE      THREE

    4 rows selected.

    SQL>

NULL不能用"="或者"!="，否则得不到结果。

    SQL> SELECT * FROM null_test_tab WHERE col1 = NULL ORDER BY id;

    no rows selected

    SQL>

相反，应该用is null或者is not null.

    SQL> SELECT * FROM null_test_tab WHERE col1 IS NULL ORDER BY id;

            ID COL1       COL2       COL3       COL4
    ---------- ---------- ---------- ---------- ----------
             2            TWO        THREE      FOUR
             3                       THREE      FOUR
             4                       THREE      THREE

    3 rows selected.

    SQL>

NVL
    NVL函数会将NULL值替换为一个默认值。假如第一个参数的值为NULL,则返回第二个参数的值。
否则直接返回第一个参数值。

    SQL> SELECT id, NVL(col1, 'ZERO') AS output FROM null_test_tab ORDER BY id;

            ID OUTPUT
    ---------- ----------
                   1 ONE
                   2 ZERO
                   3 ZERO
                   4 ZERO

    4 rows selected.

    SQL>

DECODE
   DECODE函数不是专门用于处理nULL的函数，但可以通过比较NULL来设置默认值。

    SQL> SELECT id, DECODE(col1, NULL, 'ZERO', col1) AS output FROM null_test_tab ORDER BY id;

            ID OUTPUT
    ---------- ----------
             1 ONE
             2 ZERO
             3 ZERO
             4 ZERO

    4 rows selected.

    SQL>

NVL2
    NVL2函数接受三个参数 ，假如第一个参数值不为NULL，则返回第二个参数值；否则返回第三个参数值。

The following query shows NVL2 in action.

    SQL> SELECT id, NVL2(col1, col2, col3) AS output FROM null_test_tab ORDER BY id;

            ID OUTPUT
    ---------- ----------
                   1 TWO
                   2 THREE
                   3 THREE
                   4 THREE

    4 rows selected.

    SQL>

COALESCE
   COALESCE函数返回参数表中第一个非NULL的参数值，如果全部参数值都为NULL,则返回NULL.

    SQL> SELECT id, COALESCE(col1, col2, col3) AS output FROM null_test_tab ORDER BY id;

            ID OUTPUT
    ---------- ----------
             1 ONE
             2 TWO
             3 THREE
             4 THREE

    4 rows selected.

    SQL>

NULLIF
   NULLIF函数接受两个参数，如果两者值相等，则返回NULL,如果不相等，则返回第一个参数值。

    SQL> SELECT id, NULLIF(col3, col4) AS output FROM null_test_tab ORDER BY id;

            ID OUTPUT
    ---------- ----------
             1 THREE
             2 THREE
             3 THREE
             4

    4 rows selected.

    SQL>

LNNVL
 LNNVL 函数判断条件是否为true,如果为false或者 unknown，则返回true. 假如为true,则返回false.

    SQL> SELECT id, col3 FROM null_test_tab WHERE LNNVL(col1 IS NULL) ORDER BY id;

            ID COL3
    ---------- ----------
             1 THREE

    1 row selected.

    SQL> SELECT id, col3 FROM null_test_tab WHERE LNNVL(col2 = 'TWO') ORDER BY id;

            ID COL3
    ---------- ----------
             3 THREE
             4 THREE

    2 rows selected.

    SQL> SELECT id, col3 FROM null_test_tab WHERE LNNVL(col2 != 'TWO') ORDER BY id;

            ID COL3
    ---------- ----------
             1 THREE
             2 THREE
             3 THREE
             4 THREE

    4 rows selected.

    SQL>

NANVL
The NANVL function was introduced in Oracle 10g for use with the BINARY_FLOAT and BINARY_DOUBLE datatypes, which can contain a special "Not a Number" or "NaN" value. The function is similar to NVL, but rather than testing for null it tests for "NaN" values. The following table will be used to demonstrate it.

    DROP TABLE nanvl_test_tab;

    CREATE TABLE nanvl_test_tab (
      id NUMBER,
      col1 BINARY_DOUBLE
    );

    INSERT INTO nanvl_test_tab VALUES (1, 1234.5678);
    INSERT INTO nanvl_test_tab VALUES (2, 'INF');
    INSERT INTO nanvl_test_tab VALUES (3, '-INF');
    INSERT INTO nanvl_test_tab VALUES (4, 'NaN');
    COMMIT;

If we query the table we see the following data.

    SELECT * FROM nanvl_test_tab ORDER BY id;

            ID       COL1
    ---------- ----------
             1 1.235E+003
             2        Inf
             3       -Inf
             4        Nan

    4 rows selected.

    SQL>

Next, we query the data again, but convert any "NaN" values to "0" using the NANVL function.

    SELECT id, col1, NANVL(col1, 0) AS output FROM nanvl_test_tab;

            ID       COL1     OUTPUT
    ---------- ---------- ----------
             1 1.235E+003 1.235E+003
             2        Inf        Inf
             3       -Inf       -Inf
             4        Nan          0

    4 rows selected.

    SQL>