NULL Value and Logical Comparison

Logical comparisons in where clause of queries invloving NULL values will be ignored. We use the following table to illustrate this.

SQL> select * from tbl_test3;

        ID          A          B
---------- ---------- ----------
         1          1          2
         2          1
         3
         4          2          2

SQL> select * from tbl_test3 where a=b;

        ID          A          B
---------- ---------- ----------
         4          2          2

SQL> select * from tbl_test3 where a<>b;

        ID          A          B
---------- ---------- ----------
         1          1          2

It is interesting to notice that 1. NULL=NULL is not true; 2. NULL<>NULL is not true either.

NULL Value and Arithmetic Operations

Any arithmetic operations invlove NULL values will return NULL. We use the following table to illustrate this.

SQL> select * from tbl_test3;

        ID          A          B
---------- ---------- ----------
         1          1          2
         2          1
         3
         4          2          2

SQL> select t.*, a+b from tbl_test3 t;

        ID          A          B        A+B
---------- ---------- ---------- ----------
         1          1          2          3
         2          1
         3
         4          2          2          4