Bird
Raised Fist0
SnowflakeHow-ToBeginner · 3 min read

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

UsageDescription
NULLIF(expr1, expr2)Returns NULL if expr1 equals expr2; otherwise returns expr1
expr1 = expr2Result is NULL
expr1 != expr2Result is expr1
expr1 or expr2 is NULLResult 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.