Difference between SQL NVL and 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;
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
- Happy Birthday in Hawaiian – 3 easy waysHappy birthday in Hawaiian Happy birthday in Hawaiian means “Hauʻoli Lā Hānau“. This terms are used most often because they are the standard phrases and can always be used. In social networks, but also...
- How do you say happy birthday in Yugoslavian?If you look at the official languages of the successor states of Yugoslavia today, Serbo-Croatian has disappeared. Instead, they speak Bosnian, Croatian, Serbian and Montenegrin. Nevertheless, everyone can still understand everyone. Happy birthday in...
- Fallout 76 – Vendor – Great tipsYou 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...
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.
2 Responses
[…] Difference between SQL NVL and NVL2 – Blogofant […]
[…] 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 […]