⚡ CrackLab
beginner~3h

CASE Expressions, Conditional Logic & NULL-Safe Functions

CASE, COALESCE, and NULLIF let a query make decisions row by row instead of pushing that logic into application code — and conditional aggregation (SUM(CASE WHEN ...)) is the single most common way real dashboards compute rates and buckets in one pass.

Every other topic in this course assumes you can filter and shape rows (WHERE, JOIN, GROUP BY) — but real reports also need to classify and branch on data: label a salary as "High/Medium/Low", count how many payments succeeded without a second query, or substitute a fallback when a value is missing. That's exactly what CASE, COALESCE, and NULLIF are for. Skipping this topic means either pushing that logic into application code (extra round trips, harder to keep consistent) or writing verbose, error-prone UNION-based workarounds instead of a single readable expression.

A raw SELECT can filter which rows come back and what columns appear, but it can't natively answer "is this value high or low", "what should I show if this is NULL", or "what percentage of these rows match some condition" — those require conditional logic evaluated per row. Without CASE and its relatives, you'd need separate queries per bucket (one for "High" salaries, one for "Medium", one for "Low") and then stitch the results together in code, which is slower, harder to keep in sync, and impossible to sort or filter on as a single derived column.

CASE — an inline conditional expression: CASE WHEN cond1 THEN val1 WHEN cond2 THEN val2 ELSE default END; evaluates conditions top to bottom and stops at the first match. Simple CASE — CASE col WHEN x THEN ... WHEN y THEN ... END, a shorthand equality-only form. COALESCE(a, b, c, ...) — returns the first non-NULL argument from left to right. NULLIF(a, b) — returns NULL if a = b, otherwise returns a; the inverse of COALESCE, used to introduce a NULL (commonly to guard against division by zero). Conditional aggregation — combining CASE with SUM/COUNT/AVG to compute per-condition totals in one grouped query instead of one query per condition. FILTER (WHERE ...) — PostgreSQL's alternative to CASE-inside-an-aggregate, applying an extra predicate to a single aggregate call without touching the rest of the row.

CASE is an expression, not a statement — it can appear anywhere a column reference could: in SELECT, WHERE, ORDER BY, GROUP BY, or inside another function call. There are two forms: the searched form (CASE WHEN salary > 80000 THEN 'High' ... END), which supports arbitrary boolean conditions, and the simple form (CASE department_id WHEN 1 THEN 'Eng' WHEN 2 THEN 'Sales' END), which only tests equality against one expression but reads a bit cleaner when that's all you need.

Conditional aggregation

The pattern SUM(CASE WHEN status = 'Success' THEN 1 ELSE 0 END) turns a per-row condition into a per-row 0/1, and the surrounding SUM (or AVG, for a straight percentage) rolls that up across the group — this is how a single query computes a success rate, a cancellation rate, or a "first half vs second half" comparison without a self-join or a subquery per bucket.

COALESCE vs NULLIF

COALESCE hides a NULL by substituting a fallback (COALESCE(SUM(o.total_amount), 0) turns a customer-with-no-orders' NULL sum into a real 0). NULLIF does the opposite — it manufactures a NULL on purpose, most often to make a division safe: revenue / NULLIF(hours, 0) returns NULL instead of raising a divide-by-zero error when hours is 0.

FILTER as an alternative to CASE inside an aggregate

PostgreSQL's COUNT(*) FILTER (WHERE condition) is often clearer than SUM(CASE WHEN condition THEN 1 ELSE 0 END) for a plain count, and multiple FILTER clauses in the same SELECT read more like separate named metrics than one shared CASE expression would.

💻 Code example

-- Conditional aggregation: one query, two rates
SELECT
    ROUND(
        100.0 * SUM(CASE WHEN payment_status = 'Success' THEN 1 ELSE 0 END)
        / COUNT(*), 2
    ) AS success_rate,
    COUNT(*) FILTER (WHERE payment_status = 'Failed') AS failed_count
FROM payments;

-- COALESCE for a safe default, NULLIF for a safe division
SELECT
    customer_id,
    COALESCE(SUM(total_amount), 0) AS total_spent,
    ROUND(revenue / NULLIF(total_hours, 0), 2) AS revenue_per_hour
FROM ...;

The PostgreSQL planner evaluates a CASE expression's branches lazily, left to right, and stops at the first WHEN whose condition is true — later branches are never evaluated for that row, which matters if a later branch would otherwise error (e.g. a division that's only safe under an earlier, already-excluded condition). Inside an aggregate, SUM(CASE WHEN cond THEN expr ELSE 0 END) and COUNT(*) FILTER (WHERE cond) are typically compiled to equivalent execution plans in modern PostgreSQL — FILTER is mostly a readability improvement, not a performance one. COALESCE and NULLIF are implemented as simple built-in functions with no special planner treatment; they don't prevent an index from being used on the underlying column the way wrapping a column in most other functions would (e.g. UPPER(email) = 'X'), because they're typically applied to the output of an aggregate or a already-selected value, not to a raw indexed column inside a WHERE predicate.

Step 1: Identify the branching logic in plain English first ("High if >= 80000, Medium if >= 50000, else Low") — this maps directly onto CASE WHEN order.

Step 2: Write branches in the CASE from most specific/highest to least, since the first matching WHEN wins and later ones are never checked.

Step 3: Always include an ELSE — without one, rows that match no branch silently return NULL, which usually isn't what you want.

Step 4: For a rate or percentage across a group, wrap the CASE in SUM (or AVG for a direct percentage) and GROUP BY the dimension you're comparing across.

Step 5: Wrap any LEFT JOIN-derived aggregate that could legitimately be NULL (a customer with zero orders) in COALESCE before returning it to the caller.

Step 6: Wrap any denominator that could legitimately be zero in NULLIF before dividing.

  • Always add an ELSE branch to CASE, even if it's just ELSE NULL — an implicit NULL default is easy to forget and hard to debug later.

  • Order CASE WHEN branches from most restrictive to least — a salary >= 50000 branch placed before salary >= 80000 would incorrectly swallow every high earner into the lower bucket.

  • Prefer FILTER (WHERE ...) over SUM(CASE WHEN ... THEN 1 ELSE 0 END) for simple conditional counts in PostgreSQL — it's shorter and makes the intent ("count of X") immediately obvious to a reader.

  • Multiply by 100.0, not 100, before dividing when computing a percentage — the .0 forces floating-point division so the result isn't silently truncated to an integer.

  • Use COALESCE at the outermost layer of a query, not buried inside a subquery, so it's obvious to anyone reading the final SELECT list which columns can never come back NULL.

  • Forgetting the ELSE branch and being surprised later by unexpected NULLs in a report.

  • Ordering CASE WHEN branches from lowest threshold to highest, so a row that should hit a later, more specific branch matches an earlier, looser one first.

  • Using salary / 100 for a percentage instead of 100.0 * salary / total — integer division in some contexts truncates the decimal portion entirely, silently returning 0.

  • Reaching for COALESCE to "fix" a query that's returning fewer rows than expected — COALESCE only replaces a NULL value, it can't bring back a row a JOIN already dropped; that's a job for LEFT JOIN, not COALESCE.

  • Dividing without NULLIF and getting a runtime "division by zero" error the one time a denominator happens to be 0 in production, even though it worked fine in every test you ran.

  • Conditional aggregation with CASE/FILTER computes multiple metrics in a single pass over the data — this is almost always faster than running one query per metric and combining the results in application code, since the table (or index) is only scanned once.

  • A CASE expression inside a WHERE clause on a large table can prevent index usage the same way any function wrapping a column does — if a query is slow, check whether the CASE logic can be rewritten as a plain range/equality predicate on the raw column instead.

  • COALESCE and NULLIF themselves are cheap, constant-time operations per row — they're rarely the bottleneck in a slow query; the surrounding JOINs and aggregates almost always dominate the cost.

CASE, COALESCE, and NULLIF don't introduce any injection risk on their own, but if any branch condition or fallback value is built by string-concatenating raw user input into the SQL text (rather than passed as a bound parameter), that's the same SQL injection risk as any other hand-built query — always parameterize values that came from outside the application, never interpolate them directly into a CASE WHEN condition.

If a percentage or rate computed via conditional aggregation looks wrong in production, check first whether the denominator (COUNT(*)) is being computed over the same filtered row set as the numerator — a WHERE clause added later that only some engineers remembered to test against is a common source of a rate that quietly drifts from what a dashboard used to show. Unexpected NULLs appearing in a report that previously never had them are usually traceable to a missing ELSE branch or a missing COALESCE around a newly-introduced LEFT JOIN.

  • Centralize repeated CASE-based classification logic (salary bands, status groupings) in a VIEW or a well-named helper so every report applies the exact same thresholds instead of each author redefining slightly different bucket boundaries.

  • When conditional aggregation feeds a customer-facing dashboard, add a test asserting that the sum of all CASE-based buckets equals the unconditioned COUNT()/SUM() — a silently missing ELSE or an overlapping condition is otherwise invisible until someone notices the numbers don't add up.

  • Document the exact threshold values used in salary bands, rate calculations, etc. next to the query itself (or in the view definition) — these numbers tend to be business decisions that change, and a comment saves the next engineer from having to reverse-engineer intent from a raw >= 80000.

  • Write a query that labels every order as 'Small' (< 5000), 'Medium' (5000-20000), or 'Large' (> 20000), then count how many orders fall in each band using conditional aggregation in a single query.

  • Compute the percentage of orders in each status (Pending/Shipped/Delivered/Cancelled) out of all orders, using conditional aggregation rather than four separate queries.

  • Write a query that safely computes average revenue per hour worked for every employee, using COALESCE and NULLIF so that an employee with no orders or no logged hours never causes a NULL or a division error.

  • Rewrite a SUM(CASE WHEN status = 'Cancelled' THEN 1 ELSE 0 END) count using COUNT(*) FILTER (WHERE status = 'Cancelled') instead, and compare the query plans with EXPLAIN.

  • CASE is an expression usable anywhere a value is expected — SELECT, WHERE, ORDER BY, or inside another function.

  • CASE WHEN branches are evaluated top to bottom; the first true condition wins and later branches are skipped — order matters.

  • Always include an ELSE branch to avoid silent, unexpected NULLs.

  • SUM(CASE WHEN cond THEN 1 ELSE 0 END) or COUNT(*) FILTER (WHERE cond) computes a conditional count/rate in a single pass — prefer this over one query per condition.

  • COALESCE(value, fallback) replaces a NULL value; it cannot restore a row a JOIN already dropped.

  • NULLIF(a, b) returns NULL when a = b — the standard guard against division by zero.

  • Multiply by 100.0, not 100, before dividing to force floating-point (not truncated integer) percentages.

Want a visual for this concept?

Generate a diagram tailored to “CASE Expressions, Conditional Logic & NULL-Safe Functions” — the AI picks whichever visual (flowchart, comparison, sequence, etc.) best fits.

Sign in to generate a visual →

Practice quiz

Next Step

Continue to JOINs — INNER, LEFT, RIGHT, FULL, SELF, CROSS →← Back to all SQL chapters