Return NULL When Two Values Are Equal With NULLIF

Use `NULLIF` to convert a specific value to `NULL`, most commonly to prevent division-by-zero errors.

5/5 supported engines validation-green 1 example 2 scenarios

Docker-validated Not currently validation-green

Example 1

Divide values safely by converting a zero divisor to NULL

For alpha, NULLIF(0, 0) returns NULL, so 100 / NULL yields NULL instead of a division-by-zero error. For beta and gamma, the divisor is non-zero so NULLIF returns it unchanged, and normal division proceeds.

Source table data Rows loaded before the example query runs.

Setup

CREATE TABLE metrics (id INT, label VARCHAR(50), VALUE INT, divisor INT);

INSERT INTO
  metrics (id, label, VALUE, divisor)
VALUES
  (1, 'alpha', 100, 0),
  (2, 'beta', 200, 4),
  (3, 'gamma', 150, 3);

Validated query Shared across supported engines.

SQL

SELECT
  label,
  VALUE,
  divisor,
  VALUE / NULLIF(divisor, 0) AS RESULT
FROM
  metrics
ORDER BY
  id;

Expected result Returned rows for the shared example.

label	value	divisor	result
alpha	100	0	NULL
beta	200	4	50
gamma	150	3	50

Output is identical across all engines.

Useful when

Where this command helps.

preventing division-by-zero errors in computed columns
converting a sentinel value to NULL for consistent null handling

Explanation

What the command is doing.

NULLIF(x, y) returns NULL if x equals y, otherwise it returns x. The most common use is value / NULLIF(denominator, 0): when the denominator is zero, NULLIF turns it into NULL, and dividing by NULL returns NULL instead of raising a division-by-zero error.

Return NULL When Two Values Are Equal With NULLIF

Divide values safely by converting a zero divisor to NULL

Where this command helps.

What the command is doing.

Keep moving through nearby tasks.