NULLIF usage and examples

Overview

NULLIF(a, b) returns NULL when both expressions are equal and otherwise returns a. It is useful for converting sentinel values to NULL or preventing divide-by-zero with value / NULLIF(divisor, 0). Later aggregates follow NULL rules, so confirm that ignoring those values matches the metric definition.

Basic syntax

SELECT NULLIF(discount, 0) FROM orders;

Examples

NULLIF example

The example turns zero discounts into NULL while preserving nonzero discounts, making the equality behavior visible.

Sample table

Table：orders

orders
discount
0
50
100
0

Run result

result
NULLIF(discount, 0)
NULL
50
100
NULL

Common issues

How can NULLIF prevent division by zero?

Use value / NULLIF(divisor, 0). A zero divisor becomes NULL, so the expression returns NULL instead of dividing by zero.

What does NULLIF return when values are equal?

It returns NULL when both expressions compare equal; otherwise it returns the first expression. Type and collation rules still apply.

Does converting a value to NULL affect aggregates?

Yes. COUNT(column), SUM, and AVG normally ignore NULL, so confirm that excluding those values is intended.

Keep reading

CASE WHEN COALESCE IFNULL CAST

NULLIF