conversion

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

Loading playground environment...

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. NULLIF makes equal values NULL; COALESCE replaces NULLs with a fallback.