How to Use NULLIF Function in Snowflake: Syntax and Examples
In Snowflake, use the
NULLIF(expr1, expr2) function to return NULL if expr1 equals expr2; otherwise, it returns expr1. This helps replace specific values with NULL in queries easily.Syntax
The NULLIF function takes two expressions and compares them. If both are equal, it returns NULL. If they differ, it returns the first expression.
- expr1: The first value or expression to check.
- expr2: The second value or expression to compare with the first.
sql
NULLIF(expr1, expr2)
Example
This example shows how NULLIF returns NULL when two values are equal, and returns the first value when they differ.
sql
SELECT NULLIF(10, 10) AS result_equal, NULLIF('apple', 'orange') AS result_different;
Output
RESULT_EQUAL | RESULT_DIFFERENT
------------ | ---------------
NULL | apple
Common Pitfalls
One common mistake is expecting NULLIF to return expr2 when values differ; it always returns expr1 if they are not equal. Also, be careful when expr1 or expr2 can be NULL because comparisons with NULL result in UNKNOWN, so NULLIF will return expr1 in that case.
sql
/* Wrong: expecting expr2 when values differ */ SELECT NULLIF(5, 3) AS wrong_usage; -- returns 5, not 3 /* Correct: NULLIF returns expr1 if not equal */ SELECT NULLIF(5, 3) AS correct_usage; -- returns 5
Output
WRONG_USAGE
-----------
5
CORRECT_USAGE
-------------
5
Quick Reference
| Usage | Description |
|---|---|
| NULLIF(expr1, expr2) | Returns NULL if expr1 equals expr2; otherwise returns expr1 |
| expr1 = expr2 | Result is NULL |
| expr1 != expr2 | Result is expr1 |
| expr1 or expr2 is NULL | Result is expr1 (no NULL returned) |
Key Takeaways
Use NULLIF(expr1, expr2) to return NULL when both expressions are equal.
NULLIF returns the first expression if values differ or if either is NULL.
NULLIF is useful to replace specific values with NULL in queries.
Do not expect NULLIF to return the second expression when values differ.
Be cautious with NULL comparisons as they do not trigger NULLIF to return NULL.
