NULL in Oracle database is the absence of data and not empty. In other way, NULL in Oracle database is a value that represent unknown data.
How do you assign NULL for a column value or variable?
You can explicitly assign NULL for a column or variable. If a variable or column is not assigned with any value then Oracle database treats the variable value as NULL.
Var1 NUMBER := NULL;
Var2 VARCHAR2(10) := NULL;
Var3 DATE := NULL;
Var1 NUMBER ;
Var2 VARCHAR2(10) ;
Var3 DATE ;
Do not use NULL to represent a value of zero, because they are not equivalent. Oracle database upto 12c, treats NULL and empty string ( ” ) equivalent. But you must understand, empty string is a character value with a length of zero, whereas NULL is unknown as you don’t assign anything.
That means ”
Var2 VARCHAR2(10) := NULL ” and ”
Var2 VARCHAR2(10) := "" ” both behave the same way in Oracle database. You can visit Oracle site for more info on how Oracle database treats NULL.
Words from Oracle documentation
Oracle Database currently treats a character value with a length of zero as NULL. However, this may not continue to be true in future releases, and Oracle recommends that you do not treat empty strings the same as nulls.
According to Oracle’s architecture NULL is a special kind of internal generated value, which is identified by Oracle’s implicit routines. This value can’t be directly compared on the ANSI operators as a NULL keyword. Oracle provides special operators( IS NULL and IS NOT NULL ) to handle NULLs.
|NULL = 10||UNKNOWN|
|NULL <> 10||UNKNOWN|
|NULL = NULL||UNKNOWN|
|NULL <> NULL||UNKNOWN|
|NULL IS NULL||TRUE|
|NULL IS NOT NULL||FALSE|
|10 IS NULL||FALSE|
|10 IS NOT NULL||TRUE|
Working with NULL in Oracle
Behavior of NULL in Oracle or any databases is very important to understand. Learn here about – Handling NULL in filter condition.