What’s the difference between NVL and NULLIF?
These two similarly named functions do very different things. Here’s a comparison.
Equivalent CASE expr | Arg types | |
NULLIF(a,b) |
CASE WHEN a = b THEN NULL ELSE a END |
Both args must of same datatype or both numeric. |
NVL(a,b) |
CASE WHEN a IS NULL THEN b ELSE a END |
Implicit datatype conversion when possible. |
NULLIF
Documentation quote:
NULLIF compares expr1 and expr2. If they are equal, then the function returns null. If they are not equal, then the function returns expr1. You cannot specify the literal NULL for expr1.
NVL
Documentation quote:
NVL lets you replace null (returned as a blank) with a string in the results of a query. If expr1 is null, then NVL returns expr2. If expr1 is not null, then NVL returns expr1.
Today’s image is a null set pendant from surlyramics.com
A nice use for NULLIF is the avoidance of “divide by zero” errors
select numerator / nullif(divisor,0) from my_table;
Nice tip, thank you Connor!
That is cleaner and more readable than using case or decode eg. decode(divisor,0,null,numerator/divisor)