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

  • Bus connections and buses on Corfu
    Corfu is one of the most beautiful and popular Greek islands, attracting visitors with an impressive mix of picturesque beaches, rich history and vibrant culture. To fully explore the island’s diversity, Corfu’s public busRead more
  • Rhodes Beaches and Rhodes Snorkeling
    Rhodes, the largest of the Dodecanese islands in Greece’s Aegean Sea, is famous for its rich history, archaeological sites and, of course, stunning beaches. The island is also a paradise for snorkelers who want … Read more
  • The Skiathos Bus System: A Comprehensive Guide
    Hello, travel enthusiasts! Today, we are taking you on a different kind of journey by giving you a deep insight into the bus system of Skiathos, the backbone of public transport on this stunning … Read more

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 […]