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.

Babyalbum - UNSER ERSTES GEMEINSAMES JAHR: Die schönsten Momente und Erinnerungen - ein bezauberndes Erinnerungsalbum zur Geburt (PAPERISH Babybuch) (PAPERISH Kinderbuch)

 (2419)

14,95 € (as of 11. April 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.)

Das Kind in dir muss Heimat finden: Der Schlüssel zur Lösung (fast) aller Probleme

 (7729)

14,99 € (as of 11. April 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.)

instax Square Monochrome Film 10 Aufnahmen

 (13894)

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

Fire TV Stick 4K Ultra HD mit Alexa-Sprachfernbedienung

 (73856)

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

RuoCherg RGB Mauspad, 800 x 300 mm Gaming Mousepad mit 12 Beleuchtungs-Modi, Wasserdicht Anti Rutsch Mouse Matte für Computer PC Professionelle Gamer

 (8262)

17,89 € (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.)

Achtsamkeit für dich: 50 Karma-Kärtchen | Schön gestaltete Achtsamkeitskarten in Geschenkbox zur Stressbewältigung im Alltag (Achtsamkeitskärtchen)

 (4312)

4,99 € (as of 11. April 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.)

Das NEINhorn

 (9716)

13,00 € (as of 11. April 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.)

Apple AirPods mit kabelgebundenem Ladecase

 (95304)

159,80 € (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.)

Kindle Paperwhite, wasserfest, 6 Zoll (15 cm) großes hochauflösendes Display, 8 GB – mit Werbung - Schwarz

 (32672)

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

HUAWEI Watch GT 2 Smartwatch (42 mm Full-Color-AMOLED Touchscreen, SpO2-Monitoring, Herzfrequenzmessung, Musik Wiedergabe, 5ATM wasserdicht, GPS) Chestnut Red [Exklusiv +5 EUR Gutschein]

 (11068)

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