Difference between SQL NVL and NVL2

NVL NVL2

NVL VS NVL2

The difference between the SQL functions NVL and NVL2 is, that when using the function NVL2 you can set different values for NULL and NOT NULL. NVL only checks and analyzes the first expression. If the expression is NOT NULL, the function outputs the value of the field. If a NULL value occurs in NVL, the second expression is executed (=output). A quick example (programming code style) how the SQL NVL function works:

IF expr1 IS NOT NULL     
THEN expr1
END IF;

IF exp1 IS NULL
THEN expr2
END IF;
Werbung / Advertisements

When using NVL2 you normally don’t use the first expression to output. This means you can decide which values you output when the first expression IS NULL or IS NOT NULL. So you have two possibilities. Following a short example where the expr1 (expression) is checked. When the expr1 IS NOT NULL, the value of the expr2 (f.e. a field, formula or a static value) is outputted. However, if the expr1 IS NULL the expr3 is outputted (similar to the SQL function NVL).

Current Posts

  • Fallout 76 – Vendor – Great tips
    You thought Fallout 76 only had train station vendors? Far from it. There are many different trading systems in Fallout. The most well-known system is of course the handle with bottle caps, but there...
  • Fallout 76 – Farm Glue
    Glue is one of the most commonly used materials in Fallout 76, especially in the early stages up to level 100. Whether armor or weapons – glue is constantly needed. Glue is also required...
  • Fallout 76 – Farm aluminum
    Aluminium is one of the rare goods in Fallout – but it is needed relatively often. For example, aluminum is needed to repair weapons. But some objects and building materials also need the important...

IF expr1 IS NOT NULL     
THEN expr2
END IF;

IF expr1 IS NULL
THEN expr3
END IF;

Afterwards a short example to demonstrate that you can implement NVL2 functionally equivalent to NVL.

NVL(expr1, expr2) = NVL2(expr1, expr1, expr2)

Application examples

Both functions, NVL and NVL2, are partly necessary for queries. For example, if an IF query is executed, an error can occur withoud definition of NULL values. So f.e. when working with (positiv) numbers you can set the NULL value to -1. If you work with negative as well as positive numbers you can use the MAX or MIN function, to get the highes (lowest) number. When having text files you can use a specific STRING. Also aggregated calculations have problems with NULL values. But try to think yourself: What ist the result of the sum NULL + NULL, or in other words: The sum of undefined plus undefined.

You may also like...

2 Responses

  1. 22. January 2022

    […] Difference between SQL NVL and NVL2 – Blogofant […]

  2. 29. September 2022

    […] default values are necassary. Normally you can use NVL or NVL2 when you get NULL values as return value. But it is also possible, that no row is returned. In this […]