SQL NULLIF: Syntax, Examples and the Divide-by-Zero Fix

NULLIF compares two values. If they are equal it returns NULL; if they differ it returns the first value unchanged:

SELECT NULLIF(100, 100)     AS same_numbers,
       NULLIF(100, 50)      AS different_numbers,
       NULLIF('SQL', 'SQL')   AS same_text,
       NULLIF('SQL', 'MySQL') AS different_text;

▶ Run this query in the SQL Playground

NULLIF(value1, value2)

-- means exactly the same as
CASE WHEN value1 = value2 THEN NULL ELSE value1 END

Dividing by zero is an error in PostgreSQL, SQL Server and Oracle, and stops the whole query. Wrapping the divisor in NULLIF(divisor, 0) turns a zero into NULL first. Anything divided by NULL is NULL, so the row gets a NULL instead of the query failing.

A realistic case: the share of successful payments per payment method. If a method had no payments yet, the divisor would be 0:

SELECT method,
       COUNT(*) AS payments,
       SUM(status = 'success') AS successful,
       ROUND(100.0 * SUM(status = 'success') / NULLIF(COUNT(*), 0), 1) AS success_pct
FROM payments
GROUP BY method
ORDER BY success_pct DESC;

▶ Run this query in the SQL Playground

Here no method has zero payments, so NULLIF changes nothing; it is there so the query stays safe when the data changes. The next example shows the case where it matters, using the sample rows below (respondents B and E have zero visits):

WITH survey(respondent, city, score, visits) AS (
  VALUES ('A', 'Pune',    8, 4),
         ('B', 'N/A',     0, 0),
         ('C', 'Delhi',   9, 3),
         ('D', 'Unknown', 0, 2),
         ('E', 'Pune',    6, 0)
)
SELECT respondent, score, visits,
       ROUND(1.0 * score / NULLIF(visits, 0), 2) AS score_per_visit
FROM survey;

▶ Run this query in the SQL Playground

Data often contains values that really mean "unknown": 0 for a missing score, 'N/A' or 'Unknown' for a missing city. Left as they are, they distort counts and averages. NULLIF turns them into NULL, which aggregates skip:

WITH survey(respondent, city, score, visits) AS (
  VALUES ('A', 'Pune',    8, 4),
         ('B', 'N/A',     0, 0),
         ('C', 'Delhi',   9, 3),
         ('D', 'Unknown', 0, 2),
         ('E', 'Pune',    6, 0)
)
SELECT respondent,
       NULLIF(NULLIF(city, 'N/A'), 'Unknown') AS city,
       NULLIF(score, 0) AS score
FROM survey;

▶ Run this query in the SQL Playground

NULLIF compares with one value, so two placeholders need two nested calls. For a longer list, a CASE with IN ('N/A', 'Unknown', '-') is easier to read.

Effect on averages and counts

WITH survey(respondent, city, score, visits) AS (
  VALUES ('A', 'Pune',    8, 4),
         ('B', 'N/A',     0, 0),
         ('C', 'Delhi',   9, 3),
         ('D', 'Unknown', 0, 2),
         ('E', 'Pune',    6, 0)
)
SELECT AVG(score)            AS avg_with_zeros,
       ROUND(AVG(NULLIF(score, 0)), 2) AS avg_without_zeros,
       COUNT(NULLIF(city, 'N/A')) AS cities_not_na
FROM survey;

▶ Run this query in the SQL Playground

With the zeros counted as real scores the average is 4.6; treating them as missing gives 7.67. Which one is right depends on what 0 means in your data, which is a question to ask before you clean it.

On real Playground data

7 of the 200 orders have a discount of exactly 0. If you want "the average discount among orders that had one", NULLIF removes them from the average:

SELECT SUM(discount = 0) AS no_discount,
       ROUND(AVG(discount), 4) AS avg_all_orders,
       ROUND(AVG(NULLIF(discount, 0)), 4) AS avg_discounted_orders
FROM orders;

▶ Run this query in the SQL Playground

NULLIF and COALESCE work in opposite directions: NULLIF turns a value into NULL, COALESCE turns NULL into a value. Combined, they replace one value with another. Here a 0 score becomes "no score" (NULL) and then a label:

WITH survey(respondent, city, score, visits) AS (
  VALUES ('A', 'Pune',    8, 4),
         ('B', 'N/A',     0, 0),
         ('C', 'Delhi',   9, 3),
         ('D', 'Unknown', 0, 2),
         ('E', 'Pune',    6, 0)
)
SELECT respondent, score,
       COALESCE(CAST(NULLIF(score, 0) AS TEXT), 'not answered') AS score_label,
       COALESCE(1.0 * score / NULLIF(visits, 0), 0) AS score_per_visit_or_0
FROM survey;

▶ Run this query in the SQL Playground

NULLIF works with any type, as long as both arguments are comparable, including text and dates. Here it blanks out the status of orders that are still pending:

SELECT status,
       NULLIF(status, 'pending') AS status_after_pending,
       COUNT(*) AS orders
FROM orders
GROUP BY status
ORDER BY orders DESC;

▶ Run this query in the SQL Playground

Because NULLIF only tests equality, any other condition needs CASE WHEN. These two expressions are the same:

NULLIF(status, 'pending')
CASE WHEN status = 'pending' THEN NULL ELSE status END

And this one has no NULLIF equivalent, because it tests a range, not equality:

CASE WHEN discount < 0.05 THEN NULL ELSE discount END

The names look alike but NULLIF is the only one that creates NULLs. The others all replace them.

Five errors and how to avoid them.

SELECT NULLIF(0, discount)
FROM orders;

SELECT NULLIF(discount, 0)
FROM orders;

SELECT NULLIF(score, 0) / visits
FROM survey;

SELECT score / NULLIF(visits, 0)
FROM survey;

SELECT score / NULLIF(visits, 0)
FROM survey;

SELECT 1.0 * score / NULLIF(visits, 0)
FROM survey;

SELECT NULLIF(shipped_date, 'not shipped')
FROM orders;

SELECT COALESCE(shipped_date, 'not shipped')
FROM orders;

-- 0 = "scored zero"
SELECT AVG(NULLIF(score, 0)) FROM tests;

-- keep real zeros
SELECT AVG(score) FROM tests;

6 exercises on the real Playground tables.

Write each query yourself first. Every solution has been run against the Playground database.

1. 1Easy Show the 8 orders with the smallest discounts, showing NULL instead of 0 for orders without a discount. Table: orders

   Show solution

   SQLite · runs in the Playground

   SELECT id, total, NULLIF(discount, 0) AS discount
   FROM orders
   ORDER BY orders.discount, id
   LIMIT 8;

   ▶ Run this query in the SQL Playground

   NULLIF(discount, 0) returns NULL for the zero-discount orders and the discount for the rest.

2. 2Easy Count the orders that are not in "pending" status, using NULLIF inside COUNT. Table: orders

   Show solution

   SQLite · runs in the Playground

   SELECT COUNT(NULLIF(status, 'pending')) AS not_pending
   FROM orders;

   ▶ Run this query in the SQL Playground

   Pending rows become NULL and COUNT skips them.

3. 3Medium Show each product's margin as a percentage of its cost, safely. Show the 6 highest. Table: products

   Show solution

   SQLite · runs in the Playground

   SELECT name, price, cost,
          ROUND(100.0 * (price - cost) / NULLIF(cost, 0), 1) AS markup_pct
   FROM products
   ORDER BY markup_pct DESC
   LIMIT 6;

   ▶ Run this query in the SQL Playground

   No product has a cost of 0 today, but NULLIF keeps the query safe if one is added.

4. 4Medium For the first 8 customers, show the average order value: total spent divided by the number of orders, with 0 for customers who never ordered. Table: users, orders

   Show solution

   SQLite · runs in the Playground

   SELECT u.id, u.first_name,
          COUNT(o.id) AS orders,
          COALESCE(ROUND(SUM(o.total) / NULLIF(COUNT(o.id), 0), 2), 0) AS avg_order_value
   FROM users u
   LEFT JOIN orders o ON o.user_id = u.id
   GROUP BY u.id, u.first_name
   ORDER BY u.id
   LIMIT 8;

   ▶ Run this query in the SQL Playground

   NULLIF protects the division; COALESCE turns the resulting NULL into 0.

5. 5Medium Calculate the average helpful votes per review for each rating, ignoring reviews with 0 helpful votes. Table: reviews

   Show solution

   SQLite · runs in the Playground

   SELECT rating,
          COUNT(*) AS reviews,
          ROUND(AVG(NULLIF(helpful_votes, 0)), 1) AS avg_votes_when_voted
   FROM reviews
   GROUP BY rating
   ORDER BY rating DESC;

   ▶ Run this query in the SQL Playground

   AVG skips the NULLs that NULLIF creates for zero votes.

6. 6Hard Per payment method, show the share of payments that were refunded, guarding the division. Table: payments

   Show solution

   SQLite · runs in the Playground

   SELECT method,
          SUM(status = 'refunded') AS refunded,
          COUNT(*) AS payments,
          ROUND(100.0 * SUM(status = 'refunded') / NULLIF(COUNT(*), 0), 1) AS refund_pct
   FROM payments
   GROUP BY method
   ORDER BY refund_pct DESC;

   ▶ Run this query in the SQL Playground

   SUM of a comparison counts the matching rows in SQLite; elsewhere write SUM(CASE WHEN ... THEN 1 ELSE 0 END).