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).

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.

Apple 20W USB‑C Power Adapter

 (7363)

21,63 € (as of n/a - More infoProduct prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on [relevant Amazon Site(s), as applicable] at the time of purchase will apply to the purchase of this product.)

Der Junge, der Maulwurf, der Fuchs und das Pferd

 (1893)

22,00 € (as of 17. June 2021 - More infoProduct prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on [relevant Amazon Site(s), as applicable] at the time of purchase will apply to the purchase of this product.)

Anker PowerLine II Lightning auf USB Kabel, strapazierfähig, Apple MFi-Zertifiziert für iPhone XS/XS Max/XR/X/8/8 Plus/SE/7/7 Plus/6s/6/6 Plus/5S/5/iPad Pro (Weiß)

 (41906)

7,99 € (as of n/a - More infoProduct prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on [relevant Amazon Site(s), as applicable] at the time of purchase will apply to the purchase of this product.)

Die kleinste gemeinsame Wirklichkeit: Wahr, falsch, plausibel - die größten Streitfragen wissenschaftlich geprüft

 (671)

20,00 € (as of 17. June 2021 - More infoProduct prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on [relevant Amazon Site(s), as applicable] at the time of purchase will apply to the purchase of this product.)

Die Schule der magischen Tiere – Endlich Ferien 6: Hatice und Mette-Maja (6)

 (81)

12,00 € (as of 17. June 2021 - More infoProduct prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on [relevant Amazon Site(s), as applicable] at the time of purchase will apply to the purchase of this product.)

TP-Link Tapo C200 WLAN IP Kamera Überwachungskamera (Linsenschwenkung- und Neigung, 1080p-Auflösung, 2-Wege-Audio) Weiß + smarte WLAN Steckdose (Fernzugriff, Zeitpläne) weiß

 (7506)

27,99 € (as of n/a - More infoProduct prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on [relevant Amazon Site(s), as applicable] at the time of purchase will apply to the purchase of this product.)

Apple AirPods mit kabellosem Ladecase (2. Generation)

 (16799)

159,00 € (as of n/a - More infoProduct prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on [relevant Amazon Site(s), as applicable] at the time of purchase will apply to the purchase of this product.)

Whiskeyglas, kugelförmige Whisky-Karaffe Globus Segelschiff 930 ml mit Eisstein, 2 Whiskygläser, Geschenke für Männer und Frauen, Vatertagsgeschenk

 (166)

49,99 € (as of n/a - More infoProduct prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on [relevant Amazon Site(s), as applicable] at the time of purchase will apply to the purchase of this product.)

Kneipp Duftwelten Duftkerze Glücklichsein, 145 g

 (2505)

4,49 € (as of n/a - More infoProduct prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on [relevant Amazon Site(s), as applicable] at the time of purchase will apply to the purchase of this product.)

Handgefertigte konservierte Rosen schmuck-Geschenk Box mit Splitter Halskette für sie, Rosen Geschenke für Frauen für Valentinstag, Muttertag, Jahrestag, Geburtstag für Mutter, Schwester, Ehefrau

 (55)

30,99 € (as of n/a - More infoProduct prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on [relevant Amazon Site(s), as applicable] at the time of purchase will apply to the purchase of this product.)