Guard Against Divide by Zero — COALESCE and NULLIF in SQL

The problem

Brightlane's inventory team is calculating a per-unit inventory cost — the listed price divided by the units currently in stock — for every product in the catalog.

Write a query to return each product's name and its per-unit inventory cost.

Assumptions:

The products table has one row per product with a name, a price, and a stock_qty.
Some products have a stock_qty of 0 because they are currently sold out.
For a product with a stock_qty of 0, the per-unit inventory cost is undefined and should appear as a missing value.

Output:

One row per product, with columns name and per_unit_cost. Sold-out products will show a missing value in the second column.

Schema · ecommerce 5 tables

categories

id integer

name text

parent_id? integer

products

id integer

name text

category_id integer

price numeric

stock_qty integer

attributes? jsonb

order_items

id integer

order_id integer

product_id integer

quantity integer

unit_price numeric

customers

id integer

name text

email text

city? text

country text

created_at timestamptz

is_active boolean

orders

id integer

customer_id integer

ordered_at timestamptz

status text

total_amount numeric

Check answerShift Ctrl ↵

Run previews · Check grades

Write a query, then run it to see results here.

Worked solution Try it yourself first

Solution query

SELECT
  name,
  price / NULLIF(stock_qty, 0) AS per_unit_cost
FROM
  products

The shape

NULLIF(stock_qty, 0) converts a zero stock quantity into NULL before the division happens, so the divisor is never literally 0. The arithmetic then either returns a real cost-per-unit or returns NULL. The query never crashes.

Clause by clause

SELECT name, price / NULLIF(stock_qty, 0) AS per_unit_cost returns each product's name and its per-unit inventory cost. The inner NULLIF(stock_qty, 0) is evaluated first: for a sold-out product, stock_qty is 0, the two arguments match, and NULLIF returns NULL; for every other product it returns the recorded stock_qty. The division then runs against either a real number or against NULL, and any arithmetic with a NULL operand returns NULL.
FROM products reads every row. Sold-out products stay in the result with a missing per_unit_cost, which is exactly the output spec.

The trap

Without NULLIF, the query raises a division-by-zero error the moment PostgreSQL hits a sold-out product, and the entire result set fails. The error is not silent and it is not partial. The query returns nothing at all. NULLIF is the canonical guard for this exact case: it converts the value that would crash the expression into the value that propagates harmlessly through it. The rule is general. Any time a denominator could legitimately be zero, wrap it in NULLIF(denominator, 0) and let NULL semantics carry the missing result through.

You practiced NULLIF(denominator, 0) to guard a division — when the denominator is 0, the calculation yields missing instead of an error.

Return each product's name and its per-unit inventory cost

The shape

Clause by clause

The trap

Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.