COALESCE and NULLIF in SQL
By Owen Middleton · Updated September 2026 · Examples run on PostgreSQL 17
Before this NULL Semantics and IS NULL, CASE WHEN Expressions
Builds toward NULL Handling in Joins and Aggregates, FIRST_VALUE, LAST_VALUE, NTH_VALUE
What are COALESCE and NULLIF in SQL?
COALESCE gives you a fallback when a column is NULL. NULLIF does the opposite: it converts a specific value into NULL when you want SQL to treat it as missing.
You're building a product revenue report. Some products haven't had any sales yet, so the revenue column is NULL for those rows. Aggregate functions like SUM and AVG skip NULL values entirely, which is often fine for calculations. But when you display totals in a report, a NULL looks like a gap in the data, and you'd rather show zero. COALESCE is what you reach for.
How do you replace NULL with a default in SQL?
COALESCE accepts any number of arguments and returns the first one that is not NULL:
SELECT product_name, COALESCE(revenue, 0) AS revenue
FROM productsSQL checks each row's revenue value. If it is not NULL, that value comes through. If it is NULL, SQL returns 0 instead. The output column is always filled.
You can chain as many fallbacks as you need:
SELECT COALESCE(NULL, NULL, 'third option') AS result
SQL works through the list left to right, and the first non-NULL value wins. This is useful when a table has multiple columns that might carry the same piece of information and you want the best available one:
SELECT COALESCE(nickname, first_name, 'Unknown') AS display_name
FROM customersIf nickname exists, use it. If not, try first_name. If that too is NULL, return 'Unknown'. Order matters: COALESCE(a, b) and COALESCE(b, a) produce different results whenever both are non-NULL and unequal, because the leftmost one always wins.
Should you wrap SUM in COALESCE to get zero?
One common application: wrapping an aggregate. SUM returns NULL when every row in a group is NULL. Wrapping it gives you a usable zero: COALESCE(SUM(revenue), 0). A NULL total and a zero total mean different things. NULL means no data existed for that group. Zero means data existed and summed to nothing. Replacing one with the other is a deliberate presentational choice, not a free cleanup.
What does NULLIF do in SQL?
NULLIF takes exactly two arguments and returns NULL if they are equal, or the first argument if they are not:
SELECT NULLIF(status, 'N/A') AS status
FROM ordersIf status is 'N/A', NULLIF converts it to NULL. If status is anything else, it passes through unchanged. This is useful when a column holds a placeholder string standing in for a missing value — converting it to a true NULL lets NULL-aware logic handle it correctly.
How do you avoid a division by zero error in SQL?
The other classic use is protecting against division by zero:
SELECT revenue / NULLIF(num_orders, 0) AS avg_order_value
FROM monthly_summaryIf num_orders is zero, NULLIF returns NULL, and dividing by NULL produces NULL instead of an error. If num_orders is anything else, the division runs normally.
Can NULLIF test anything other than equality?
The one thing that trips people up: NULLIF only tests equality between its two arguments.
You cannot use it to suppress a value based on a range or a more complex condition. For anything beyond simple equality, use CASE WHEN:
CASE WHEN score < 0 THEN NULL ELSE score ENDBoth COALESCE and NULLIF are shorthand for CASE WHEN expressions. COALESCE(a, b) is equivalent to CASE WHEN a IS NOT NULL THEN a ELSE b END. They follow the same NULL rules as CASE WHEN, which is why they handle NULL correctly where a naive = check would not.
You write NULLIF(quantity, 0) and the quantity column contains the value 0. What does NULLIF return?
Practice COALESCE and NULLIF in SQL
Brightlane's customer success team is preparing a regional outreach list and needs every customer to have a displayable city.
Write a query to return each customer's name and a display value for their city.
Assumptions:
- The
customerstable has one row per customer with anameand acity. - Some customers have a missing
cityvalue because they registered without entering one. - A customer with a missing
cityshould appear as'Not on File'; all other customers should show their recordedcity.
Output:
- One row per customer, with columns
nameandcity.
Schema · ecommerce5 tables? = nullable
Run previews · Check grades
Write a query, then run it to see results here.
The full breakdown walks through the shape, each clause, why this approach beats the alternatives, and the trap to avoid.
See the full worked solution9 COALESCE and NULLIF practice problems
Write a query to return each customer's name and a display value for their city.
Write a query to return each employee's name and a display value for their supervisor ID.
Write a query to return each order's ID and its export status.
Write a query to return each customer's name and their best-available location label.
Write a query to return each product's name and its per-unit inventory cost.
Write a query to return each session's ID, user ID, start time, and effective end time.
Write a query to return each user's name and their display plan label.
Write a query to return each user's name and their plan status label.
Write a query to return the name and price-per-unit-in-stock of every qualifying product.
Start learning to practice all 9 COALESCE and NULLIF problems, with instant grading and mastery tracking.
Deeper guides on COALESCE and NULLIF
- swapping a null aggregate for an empty array
Wrap it in coalesce. After a LEFT JOIN you get [null] instead, and coalesce will not fix that one.
- counting rows a null would have hidden
Most skip rows whose input is null. COUNT(*), array_agg and jsonb_agg do not, so two counts disagree.
Common questions about COALESCE and NULLIF
How many arguments can COALESCE take?
As many as you need. It works through them left to right and returns the first that is not NULL, so a chain of fallbacks ending in a literal guarantees the result is never NULL.
Does the order of COALESCE arguments matter?
Yes, and it decides the answer whenever more than one is populated. The leftmost non-NULL value always wins, so putting a nickname before a first name gives a different result from the other way round.
What type does COALESCE return when its arguments differ?
A single type that all of them fit. An integer beside a decimal resolves to numeric, and types that cannot be reconciled are an error rather than a silent conversion. The result has one type however many branches you give it.
