As you can see, the NVL function replaces a NULL with whatever value you desire.
The nvl function only has two parameters while the nvl
parameter has three arguments. The nvl2 like like combining
an nvl with a decode because you can transform a value:
NVL ( expr1 , expr2 ): If expr1
is null, then NVL returns expr2. If expr1 is not null, then NVL returns
NVL2 ( expr1 , expr2 , expr3 ):
If expr1 is null, then NVL2 returns expr3. If expr1 is not null, then
NVL2 returns expr2
As we see, the vanilla
takes a NULL value and replaces it with a printable, useable value, such as
a zero or spaces:
select nvl(b.buffer_gets,0) - NVL replaces a NULL value with a zero
select nvl(current_status, ?Not disclosed?) - NVL replaces a NULL value
with a string
Conversely, the NVL2 clause accepts three arguments,
but ALWAYS transforms the input argument.
select NVL2(supplier_city, 'Completed', 'n/a') from
In this example, these statements are equivalent because
the nvl2 re-sets the input argument back to the original value:
select nvl(commission_pct,0) 2 from employees;
select nvl2(commission_pct,commission_pct,0) 2 from
Using the Null-Value Clause with Oracle SQL
The null value clause of Oracle SQL arises from the three valued logic
that is the foundation of the SQL language. Essentially, three values
logic understands that a missing value (NULL inside Oracle), is NOT the same
as the default for that data type. For example, a NULL salary for an
employee is not the same thing as a salary of zero.
Hence, we need to be
able to display data from Oracle tables, and quickly convert null values
Now, let's look at another important application of the NVL BIF.
The outer join problem with NULL values
The null value issue is even more problematic when two tables are joined
via an "outer join". In an outer join, we have a condition where two
tables are joined together but there may not always be a corresponding row
in both tables. When an outer join is requested, Oracle will display
all of the rows of the joining table, even if there is not a matching row in
the other table. For these rows, we must handle the NULL values that
are returned by the Oracle query.
notes on advanced NULL operators in Oracle SQL.
Remember, that you can create an
index on NULL
values, using the nvl operator