CASE WHEN — Conditional Logic

CASE WHEN is SQL's conditional expression — equivalent to an if-else statement in programming languages. It evaluates a series of conditions in order and returns the result for the first condition that evaluates to TRUE. If no condition matches and there is an ELSE clause, the ELSE result is returned. If no condition matches and there is no ELSE, the expression returns NULL.

The searched CASE form evaluates arbitrary conditions for each WHEN: CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ELSE default END. Each condition can be any Boolean expression — comparisons, range checks, IS NULL, BETWEEN, IN, even subqueries. This is the most flexible and commonly used form.

The simple CASE form compares one expression against multiple equality values: CASE expression WHEN value1 THEN result1 WHEN value2 THEN result2 ELSE default END. CASE payment_method WHEN 'UPI' THEN 'Digital' WHEN 'COD' THEN 'Cash' END is cleaner than CASE WHEN payment_method = 'UPI' THEN 'Digital' WHEN payment_method = 'COD' THEN 'Cash' END. The simple form is syntactic sugar — it is always equivalent to the searched form with equality conditions, but reads more cleanly when all conditions are simple equality checks on the same column.

CASE WHEN evaluates conditions strictly top to bottom and returns the result for the first condition that is TRUE — it does not evaluate subsequent conditions after finding a match. This is called short-circuit evaluation. The order of WHEN clauses is therefore critical to the correctness of the result.

The importance becomes clear with overlapping conditions. Consider classifying products by margin: a product with 45% margin satisfies both margin >= 0.25 and margin >= 0.40. If WHEN margin >= 0.25 THEN 'Medium' appears before WHEN margin >= 0.40 THEN 'High', the 45% margin product is classified as 'Medium' — incorrectly. The correct order puts the more restrictive condition first: WHEN margin >= 0.40 THEN 'High', then WHEN margin >= 0.25 THEN 'Medium'.

The professional rule: always order WHEN conditions from most specific (most restrictive) to least specific (most permissive). For numeric bands, put the highest threshold first. For boolean conditions with overlaps, put the more narrowly defined condition first. For conditions that are mutually exclusive (no overlap), order does not affect correctness but still matters for readability — put the most common or highest-priority case first. Testing your CASE expression against known edge cases — values that sit exactly at boundaries — is the most reliable way to verify the ordering is correct.

Conditional aggregation is the technique of computing multiple different aggregate values in a single query pass by using CASE WHEN inside aggregate functions like SUM, COUNT, and AVG. Instead of running three separate queries with different WHERE conditions and combining the results, you compute all three metrics simultaneously in one GROUP BY query.

The pattern for conditional SUM: SUM(CASE WHEN condition THEN value ELSE 0 END). For each row, if the condition is true the actual value contributes to the sum; if false, 0 contributes (adding nothing). The result is the sum of values only for rows where the condition was true. For conditional COUNT: SUM(CASE WHEN condition THEN 1 ELSE 0 END) — or equivalently COUNT(CASE WHEN condition THEN 1 END) which implicitly skips NULL (the no-ELSE form returns NULL which COUNT ignores).

A concrete example: to get total revenue, delivered revenue, and cancelled revenue in one query — SELECT SUM(total_amount) AS total, SUM(CASE WHEN status = 'Delivered' THEN total_amount ELSE 0 END) AS delivered, SUM(CASE WHEN status = 'Cancelled' THEN total_amount ELSE 0 END) AS cancelled FROM orders GROUP BY store_id. This single query replaces three queries. In data warehousing, this pattern is how pivot tables are built in SQL — each pivot column is one conditional aggregate. At Indian tech companies, conditional aggregation is used constantly for daily business reports, A/B test result analysis, funnel metrics, and any dashboard that shows data split by multiple categories simultaneously.

When no WHEN condition matches and the CASE expression has an ELSE clause, the ELSE result is returned. When no WHEN condition matches and there is no ELSE clause, the CASE expression returns NULL. This is the default behaviour defined by the SQL standard.

The danger of a missing ELSE is that it creates silent NULL values in the result. If a new value is added to a column — a new payment method, a new order status, a new product category — and the CASE expression does not have a WHEN for that value and has no ELSE, the new rows produce NULL in the computed column with no error or warning. This corrupts aggregations (SUM treats NULL as 0-excluded, AVG ignores NULLs), breaks application code that expects a non-null value, and produces misleading reports.

The professional practice: always include an ELSE in every CASE expression. For categorisation use ELSE 'Other' or ELSE 'Unknown' — this makes unexpected values visible. For numeric defaults use ELSE 0. For boolean logic use ELSE FALSE. The ELSE clause acts as a safety net: it guarantees that the CASE expression never returns NULL from a missing match, and makes unexpected values immediately visible in the output rather than silently missing. The only acceptable case for omitting ELSE is when you genuinely want NULL for unmatched rows and your downstream processing handles NULL correctly — which should be explicitly documented in a comment.

Yes, CASE WHEN can be used inside ORDER BY to sort by a custom priority order rather than the natural alphabetical or numeric order of a column's values. This is one of the most useful applications of CASE WHEN in reporting contexts where business priority does not align with alphabetical or numeric sort order.

The technique: assign a numeric rank to each value using CASE WHEN and sort by that rank. ORDER BY CASE status WHEN 'Processing' THEN 1 WHEN 'Returned' THEN 2 WHEN 'Delivered' THEN 3 WHEN 'Cancelled' THEN 4 ELSE 5 END sorts orders by business priority — actionable statuses first — rather than alphabetically (Cancelled, Delivered, Processing, Returned). The numbers 1-4 are invisible in the output; they only control the sort order.

Another common example: sorting loyalty tiers by tier value (Platinum, Gold, Silver, Bronze) rather than alphabetically (Bronze, Gold, Platinum, Silver). ORDER BY CASE loyalty_tier WHEN 'Platinum' THEN 1 WHEN 'Gold' THEN 2 WHEN 'Silver' THEN 3 WHEN 'Bronze' THEN 4 END. This can be combined with additional sort keys: ORDER BY CASE loyalty_tier... END ASC, joined_date DESC — sort by tier priority first, then by most recently joined within each tier. CASE in ORDER BY is also used to put specific rows at the top or bottom: ORDER BY CASE WHEN customer_id = 42 THEN 0 ELSE 1 END sorts one specific row to the top of any result regardless of other sort criteria.

Cause: WHEN conditions are in the wrong order — least restrictive first. CASE evaluates top to bottom and returns the first match. If WHEN margin >= 0.10 THEN 'Low Margin' appears before WHEN margin >= 0.40 THEN 'High Margin', a product with 45% margin matches the first WHEN (0.45 >= 0.10 is TRUE) and is classified as 'Low Margin' immediately. The 'High Margin' WHEN is never reached.

Fix: Order WHEN conditions from most restrictive to least restrictive — highest threshold first: WHEN margin >= 0.40 THEN 'High Margin', WHEN margin >= 0.25 THEN 'Medium Margin', WHEN margin >= 0.10 THEN 'Low Margin', ELSE 'Very Low Margin'. Always verify by running the query for a known edge case — a product with exactly 40% margin should return 'High Margin', one with 39.9% should return 'Medium Margin'.

Cause: No WHEN condition matched for those rows and there is no ELSE clause. NULL is the default return value when CASE has no matching WHEN and no ELSE. This silently happens when: a new value was added to the data that is not covered by any WHEN clause, the WHEN conditions have gaps (values between thresholds that no WHEN handles), or a NULL value in the input column causes all comparisons to evaluate to NULL (NULL compared to anything is NULL).

Fix: Add an explicit ELSE clause to every CASE expression: ELSE 'Other' for text categories, ELSE 0 for numeric defaults, ELSE FALSE for boolean logic. To find which rows are returning NULL: SELECT * FROM table WHERE CASE ... END IS NULL — this identifies the unmatched rows. Check whether the column contains NULL values (which bypass all WHEN conditions) and add a WHEN col IS NULL THEN 'Unknown' as the first WHEN if needed.

Cause: The THEN clauses in a CASE expression return values of incompatible data types. SQL requires all THEN results (and the ELSE result) to be the same type, or types that can be implicitly converted to a common type. CASE WHEN x > 5 THEN 'High' WHEN x > 2 THEN 3 ELSE 'Low' END mixes text ('High', 'Low') with integer (3) — the database cannot determine a single common type.

Fix: Make all THEN and ELSE results the same data type. For text categories: use strings in all branches. For numeric results: use numbers in all branches. If you need to mix, CAST explicitly: CASE WHEN x > 5 THEN 'High' WHEN x > 2 THEN CAST(3 AS VARCHAR) ELSE 'Low' END — though mixing numeric and text values in a CASE result is usually a design problem. Rethink what the CASE is trying to produce and whether a separate numeric column and a separate text column would be clearer.

Cause: The CASE inside SUM is missing the ELSE 0 clause and using NULL instead. SUM(CASE WHEN condition THEN value END) — without ELSE — returns NULL for non-matching rows. SUM ignores NULLs, so the total should be the same as SUM(CASE WHEN condition THEN value ELSE 0 END). However, if the CASE accidentally returns unexpected values for some rows due to a wrong condition, the SUM will be inflated. Also, double-counting occurs when conditions overlap — a row satisfying two conditions appears in two conditional sums.

Fix: Verify the CASE logic by running it as a SELECT column first: SELECT order_id, total_amount, CASE WHEN condition THEN total_amount ELSE 0 END AS conditional_value FROM orders — spot-check that each row's conditional_value is what you expect before wrapping in SUM. For overlap detection: SELECT order_id, COUNT(*) FROM orders WHERE condition1 OR condition2 GROUP BY order_id HAVING COUNT(*) > 1 — any row appearing twice is being double-counted.

Cause: CASE WHEN in WHERE must evaluate to TRUE for the row to be included. A common mistake is having the CASE return a string ('Yes'/'No') and comparing it to TRUE, or having the CASE return a value the WHERE clause does not evaluate as TRUE. WHERE CASE WHEN x > 5 THEN 'Yes' ELSE 'No' END does not filter anything — it evaluates the string 'Yes' or 'No' as a condition, which in PostgreSQL is a type error, and in MySQL both non-empty strings might be truthy.

Fix: CASE in WHERE should either return a boolean: WHERE CASE WHEN x > 5 THEN TRUE ELSE FALSE END, or be compared explicitly: WHERE CASE WHEN x > 5 THEN 'Yes' ELSE 'No' END = 'Yes'. The boolean form is cleaner and more portable. Alternatively, consider whether the CASE in WHERE can be rewritten as a simpler condition or as separate OR conditions — CASE in WHERE is often more complex than needed and a straightforward WHERE clause with AND/OR is more readable.

Write a single query that produces a store performance summary for FreshCart management. For each store show: store_id, city, total delivered orders, total delivered revenue (rounded to 2 decimal places), the revenue from high-value orders (above ₹1,000) as high_value_revenue, a performance_band column using CASE: 'Star' if total delivered revenue above ₹3,000, 'Good' if above ₹1,500, 'Needs Support' otherwise. Sort by total delivered revenue descending.