NVL vs NULLIF

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

2 comments

Leave a Reply

Your email address will not be published. Required fields are marked *