---
title: "NULLIF"
description: "NULLIF returns NULL if two expressions are equal, and otherwise returns the first expression -- commonly used to avoid divide-by-zero errors or convert sentinel values into NULL."
canonical: "https://motherduck.com/glossary/nullif/"
related:
  - title: "SQL Assistant | MotherDuck Docs"
    url: "https://motherduck.com/docs/sql-reference/motherduck-sql-reference/ai-functions/sql-assistant/"
  - title: "SQL Golf: 5 SQL Tricks You Should (Probably) Never Use"
    url: "https://motherduck.com/blog/its-a-sql-golf-quackmas/"
  - title: "What's New in DuckDB 1.5! | MotherDuck"
    url: "https://motherduck.com/videos/whats-new-duckdb-15/"
---

# NULLIF

> NULLIF returns NULL if two expressions are equal, and otherwise returns the first expression -- commonly used to avoid divide-by-zero errors or convert sentinel values into NULL.

## Overview
`NULLIF(expr1, expr2)` compares two expressions: if they're equal, it returns `NULL`; otherwise, it returns `expr1`. It's shorthand for `CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END`, and it's the conceptual inverse of `COALESCE`, which replaces `NULL` with a value instead of replacing a value with `NULL`.

```sql
SELECT NULLIF(5, 5);     -- NULL
SELECT NULLIF(5, 3);     -- 5
```

## Avoiding divide-by-zero errors
The most common real-world use of `NULLIF` is preventing division errors by turning a zero denominator into `NULL`, which propagates cleanly instead of raising an error:

```sql
SELECT
  revenue,
  clicks,
  revenue / NULLIF(clicks, 0) AS revenue_per_click
FROM campaign_stats;
```
When `clicks` is `0`, `NULLIF(clicks, 0)` returns `NULL`, and `revenue / NULL` evaluates to `NULL` rather than throwing a division-by-zero error -- letting the query keep running and letting downstream logic (e.g. `COALESCE(..., 0)`) decide how to display it.

## Converting sentinel values to NULL
Legacy systems sometimes use sentinel values like `-1`, `'N/A'`, or `'unknown'` to represent missing data instead of a real `NULL`. `NULLIF` converts these into proper `NULL`s so they're excluded from aggregates and treated consistently:

```sql
SELECT NULLIF(status_code, -1) AS status_code
FROM legacy_events;
```

## Combining with COALESCE
`NULLIF` and `COALESCE` are often chained together -- for example, normalizing a sentinel value to `NULL` and then substituting a friendlier default:

```sql
SELECT COALESCE(NULLIF(status_code, -1), 0) AS status_code
FROM legacy_events;
```

## DuckDB notes
`NULLIF` in DuckDB follows the ANSI SQL standard exactly; no DuckDB-specific behavior differs from other engines.