SQL / NULL AND THREE-VALUED LOGIC
Splitting safe division with NULLIF
Guard any divisor that can be zero with NULLIF so the ratio comes back NULL for those rows instead of aborting the statement or inventing a zero.
What you will learn
- Write x / NULLIF(y, 0) so a zero divisor yields NULL instead of aborting the query
- Guard the denominator, and wrap the whole aggregate when it is SUM() or COUNT()
- Read the NULL as 'this ratio does not exist', not as zero percent
- Multiply by 1.0 or cast first: NULLIF keeps the integer type and integer division
Understanding Splitting safe division with NULLIF
PostgreSQL, Oracle and SQL Server treat x / 0 as an error, and an error kills the whole statement: one campaign with zero impressions means the query returns no rows at all, not one bad row among good ones. MySQL and SQLite instead hand back NULL, so the same SQL behaves differently depending on where it runs. NULLIF(a, b) returns NULL when a = b and returns a otherwise, so NULLIF(impressions, 0) produces NULL on exactly the rows that would have divided by zero, and NULL propagates through the arithmetic so the row survives with an unknown ratio.
The NULL you get back is not a swallowed error, it is the honest answer. A click-through rate for a campaign with zero impressions is not 0 percent; 0 percent would claim the ad was shown and nobody clicked, which is a different fact about the world. There is no rate to report, and 'no value' is exactly what NULL stands for. Leaving the unknown in place also means aggregates like AVG skip those rows on their own instead of averaging in a number you made up at the division step.
NULLIF is defined as CASE WHEN a = b THEN NULL ELSE a END, and that expansion explains its three quirks. It compares with =, so it fires only on an exact match against the value you name, which is why you must guard the denominator: NULLIF around the numerator changes nothing about the division. Its first argument appears twice in the expansion, so a volatile or expensive expression there can be evaluated twice. And its result type comes from that first argument, so NULLIF(impressions, 0) is still an integer, and two integers still divide with truncation.
Filtering is the other common reaction to a zero divisor, and it is not equivalent. WHERE impressions <> 0 or HAVING SUM(units) > 0 makes the error go away by removing rows, which changes how many rows the report has; NULLIF changes one value and keeps the row. Choose the filter only when those rows genuinely do not belong in the result.
-- PostgreSQL
WITH campaign(name, clicks, impressions) AS (
VALUES ('flash_deal', 0, 0),
('newsletter', 63, 1400),
('spring_sale', 120, 4000)
)
SELECT name,
impressions,
ROUND(100.0 * clicks / NULLIF(impressions, 0), 2) AS ctr_pct
FROM campaign
ORDER BY name;A zero denominator has no answer, so replace the zero with NULL and let the unknown flow through the arithmetic rather than letting the engine abort the statement or reporting a fabricated zero.
Worked examples
Without the guard, one row kills the query
Shows that an unguarded zero denominator aborts the whole statement in PostgreSQL, not just the offending row.
WITH campaign(name, clicks, impressions) AS (
VALUES ('flash_deal', 0, 0),
('newsletter', 63, 1400)
)
SELECT name, 100.0 * clicks / impressions AS ctr_pct
FROM campaign;Example explained
Line 1The VALUES list gives 'flash_deal' zero impressions, which is a normal state for a campaign that has not been served yet.
Line 2100.0 * clicks / impressions is evaluated row by row, and when the executor reaches the zero denominator it raises an error that aborts the statement.
Line 3No result set comes back at all: the 'newsletter' row that would have computed fine is never returned.
Line 4In MySQL and SQLite the same query returns NULL for that row instead of erroring, so writing NULLIF is also what makes the behaviour identical everywhere.
Guarding an aggregate denominator
Applies NULLIF to SUM() so a group whose total is zero still appears in the result with an unknown ratio.
WITH orders(region, revenue, units) AS (
VALUES ('north', 300.00, 12),
('north', 700.00, 28),
('south', 0.00, 0)
)
SELECT region,
ROUND(SUM(revenue) / NULLIF(SUM(units), 0), 2) AS revenue_per_unit
FROM orders
GROUP BY region
ORDER BY region;Example explained
Line 1NULLIF wraps SUM(units), not units, so the guard is applied once per group after aggregation.
Line 2For 'south' the only row has 0 units, so SUM(units) is 0, NULLIF turns it into NULL, and the division returns NULL.
Line 3The 'south' group is still listed: NULLIF replaced a value, it did not remove a row the way HAVING SUM(units) > 0 would.
Line 4revenue is numeric in the VALUES list, so this is numeric division and ROUND(..., 2) has decimals to keep.
NULLIF does not change the type
Demonstrates that NULLIF stops the error but leaves integer division intact, so the numerator still has to be numeric.
SELECT 7 / NULLIF(2, 0) AS integer_division,
ROUND(7.0 / NULLIF(2, 0), 2) AS numeric_division;Example explained
Line 1NULLIF(2, 0) returns its first argument unchanged because 2 <> 0, and that argument is still an integer.
Line 27 / <integer> is integer division, so PostgreSQL truncates 3.5 to 3 even though nothing about the guard failed.
Line 3Writing 7.0 pushes the expression into numeric arithmetic, and ROUND then reports 3.50.
Line 4The lesson is that NULLIF fixes the zero-divisor problem only; precision is a separate decision you still have to make.
Important notes
The integer modulo operator has the same trap, so total % NULLIF(bucket_count, 0) is needed for exactly the same reason that % 0 raises a division-by-zero error.
Because NULLIF expands to a CASE that names its first argument twice, guard a plain column or aggregate rather than a subquery or a volatile call like nextval(), which could be evaluated twice.
Common mistakes
Guarding the wrong operand: NULLIF(clicks, 0) / impressions still raises 'division by zero' on the zero-impression row, and it now turns honest zero-click rows into NULL.
Adding WHERE impressions <> 0 to make the error go away: the query runs, but the zero-impression campaigns disappear from the output, so row counts and totals stop matching the source table.
Treating the resulting NULL as if it were 0 when sorting: ORDER BY ctr_pct DESC puts those rows first in PostgreSQL, because NULLs sort as the largest values by default, so an unmeasurable campaign tops the leaderboard until you add NULLS LAST.
Try it yourself
Change, predict, then run
In a browser Postgres console, build a VALUES list of five products with a price and a quantity where two rows have quantity 0, then write one query returning price per unit rounded to two decimals for all five rows using NULLIF. Remove the NULLIF and confirm you get no rows back at all rather than three good rows.
Open the SQL workspaceCheck your understanding
On PostgreSQL you run SELECT region, SUM(revenue) / NULLIF(SUM(units), 0) FROM orders GROUP BY region, and one region's units sum to 0. What does the result look like?
- The statement fails with 'division by zero', because NULLIF only protects row-level values, not aggregates
- That region is missing from the result, since NULLIF drops rows whose denominator is zero
- That region is listed with a NULL value, and the other regions compute normally
- That region is listed with 0, because NULLIF substitutes a zero-safe denominator of 1
Show answer
NULLIF(SUM(units), 0) evaluates to NULL for that group, and arithmetic involving NULL is NULL, so the group is returned with an unknown ratio while every other group divides normally. Option 2 is tempting because that is what HAVING SUM(units) > 0 would do, but NULLIF only changes a value; it never changes which rows come back.