NULLIF()
Returns NULL if two expressions are equal; otherwise returns the first.
Description
NULLIF returns NULL when its two arguments are equal, and returns the first
argument otherwise. It's the cleanest way to turn a sentinel value (like an
empty string or a zero) into a real NULL so it's ignored by aggregates and
joins.
Syntax
NULLIF(expr1, expr2)Parameters
| Name | Description | Optional |
|---|---|---|
| expr1 | Returned when the two differ. | No |
| expr2 | Compared to expr1; equal means return NULL. | No |
Return Type
Returns the same type as expr1.
Examples
Employees & Departments
Loading database engine...
Employees & Departments
Loading database engine...
Clean sentinels
NULLIF(status, '') converts empty strings to NULL so they don't skew
COUNT/AVG or match ='' by accident.
Common Mistakes
- Argument order matters. The first argument is what's returned; the second is the trigger for NULL.
- Type mismatch. The two arguments should be comparable types.
- Confusing with COALESCE.
NULLIFmakes equal values NULL;COALESCEreplaces NULLs with a fallback.
Related Functions
See also: COALESCE, CAST.