All About NULL in Oracle - Part I

NULL is nothing or unknown. It is not equal to space or zero.
Oracle treats the empty string ('') as null. This is not ANSI SQL Standard.
The length of an empty string('') is null, not 0.

SQL> select length('') from dual; LENGTH('') ---------- SQL> select NVL(to_char(length('')),'NULL') Len From Dual; LEN ---- NULL

Arthimetic Operation

Any Arthimetic Operation(+,-,*,/) on NULL will return NULL..

SQL> Select NVL(To_Char(1+2+3+4+5+NULL),'NULL') Sum from Dual; SUM ---- NULL

Note : NVL(NULL Value Handling function) is a Oracle Built in function to Covnert NULL into some Meaning full value.
Example: NVL(expr1,expr2) => if expr1 is NULL then it Returns Expr2 Else it return expr1.

NVL2 is another function to Handle NULL values.
Example : NVL2(expr1,expr2,expr3) => if expr1 is NOT NULL then it returns expr2 else if expr1 is NULL then Returns expr3.

Relational Operator

Any Comparison of NULL with Relational Operators (> , < , >= , <=, <>) will Return NULL.so if you want to find the number of rows which has a particular column value as NULL. you cannot use '=' operator to check. You need to use a Special operator 'IS' to check for NULL values.

SQL> Select 1 From Dual Where NULL = NULL; no rows selected SQL> Select 1 From Dual Where NULL is NULL; 1 ---------- 1 SQL> Select count(*) from Emp where Comm is NULL; Count ----- 8 You can generate a matrix table which shows all operations by the following code snippet, SQL> Create Table Operand 2 ( 3 num number 4 ); Table created. Insert Non NULL and NULL Values into the table. SQL> insert into operand values ( 11); 1 row created. SQL> insert into operand values ( 42); 1 row created. SQL> insert into operand values (null); 1 row created. SQL> Commit; Commit complete. The Query to Generate the matrix is as follows ANSI SQL Syntax : Select op_left.num "op left", case when op_left.num = op_right.num then ' = ' when op_left.num > op_right.num then ' > ' when op_left.num < op_right.num then ' < ' else ' ? ' end "rel", op_right.num "op right" From operand op_left CROSS JOIN operand op_right; The Output is as follows op left rel op right ---------- --- ---------- 11 = 11 11 < 42 11 ? EMPTY 42 > 11 42 = 42 42 ? EMPTY EMPTY ? 11 EMPTY ? 42 EMPTY ? EMPTY 9 rows selected.