Oracle 19c SQL

  1. Home
  2. Docs
  3. Oracle 19c SQL
  4. 1 Oracle SQL Basics
  5. 1.3.8 NULL in Oracle

1.3.8 NULL in Oracle

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;

OR

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.

NULL Comparison

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.

ConditionResult
NULL = 10UNKNOWN
NULL <> 10UNKNOWN
NULL = NULLUNKNOWN
NULL <> NULLUNKNOWN
NULL IS NULLTRUE
NULL IS NOT NULLFALSE
10 IS NULLFALSE
10 IS NOT NULLTRUE

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.

Was this article helpful to you? Yes No

How can we help?