Sharon Ben-Moshe is the founder of sqlfmt, a browser-based SQL formatter and validator with misspelled-keyword suggestions across PostgreSQL, MySQL, SQLite, SQL Server, and BigQuery.
SQL CASE statements get messy fast. Here's how to format simple, multi-branch, and nested CASE WHEN expressions to stay readable as logic grows.
Quick answer
The standard layout puts CASE on its own line, indents each WHEN + THEN pair by two to four spaces with THEN on the same line as WHEN, puts ELSE at the WHEN indent level, and closes with END AS alias back at the CASE level. Use inline CASE only for two-branch expressions in ORDER BY or GROUP BY. For any CASE with three or more conditions in a SELECT list, the multi-line layout is non-negotiable — readability collapses fast in the inline version.
FAQs
Should THEN go on the same line as WHEN or the next line?
Keep THEN on the same line as WHEN. The condition and its result belong together — the reader sees both in one eye-pass. Splitting THEN to the next line doubles the visual height of the CASE block without adding clarity. The only case where a split is ever justified is when the condition itself is so long it wraps, making THEN unreadable on the same line.
What is the difference between searched CASE and simple CASE in SQL?
Searched CASE evaluates a full boolean condition in each WHEN branch: CASE WHEN price > 100 THEN 'High' END. Simple CASE compares a single column to a series of literal values: CASE status WHEN 'active' THEN 'Active' END. Use simple CASE when matching one column against a fixed value list — it's more concise. Use searched CASE whenever any branch needs a range, compound condition, or expression.
What happens if a CASE expression has no ELSE clause?
If no WHEN branch matches and there is no ELSE clause, the CASE expression returns NULL. This is a common source of silent bugs — a new status value appears in the data and the CASE quietly returns NULL instead of raising an error. Always include ELSE, even if just ELSE NULL, to make the default explicit and signal to readers that the omission is intentional.
How do you format a CASE statement inside a WHERE clause?
CASE inside WHERE is uncommon and often a sign that the logic belongs in the SELECT list or a CTE instead. If you must use it, apply the same multi-line rules: CASE on its own line, WHEN branches indented, END closing the expression before the comparison operator. In practice, a CASE in WHERE is usually clearer rewritten as two separate conditions joined by OR.
How many levels of nested CASE are acceptable?
Two levels is the practical limit. A CASE inside a THEN expression is manageable if the inner expression is short. Three or more levels hides logic in indentation that should be modeled explicitly. The standard refactor is a CTE that resolves the inner dimension first, leaving the outer CASE with shallow, readable branches.
Try it now
Paste your SQL into sqlfmt — format, validate, and catch typos free in your browser.
SQL's CASE expression is one of the most written and least formatted pieces of SQL. A two-branch expression fits neatly on a single line; a five-branch CASE with compound conditions and a nested sub-CASE becomes an unreadable wall within minutes. The formatting rules are simple once you know them.
This guide covers the two CASE layouts, the specific rules of the standard multi-line style, how to handle CASE inside a SELECT list, the format difference between searched and simple CASE expressions, and when deeply nested CASE is a signal to reach for a CTE instead.
The two CASE layouts
Every CASE expression falls into one of two layout choices: inline (all conditions on one row) or multi-line (each branch on its own row). Both appear in real codebases. The multi-line layout wins on readability in almost every production context — the branches become scannable, the structure becomes visible without counting keywords.
Before: four conditions crammed inline
-- Hard to read: all branches run togetherSELECT user_id, CASE WHEN plan = 'pro' AND status = 'active' THEN 'Pro' WHEN plan = 'free' AND status = 'active' THEN 'Free' WHEN status = 'trial' THEN 'Trial' ELSE 'Inactive' END AS plan_labelFROM accounts;
After: standard multi-line layout
SELECT user_id, CASE WHEN plan = 'pro' AND status = 'active' THEN 'Pro' WHEN plan = 'free' AND status = 'active' THEN 'Free' WHEN status = 'trial' THEN 'Trial' ELSE 'Inactive' END AS plan_labelFROM accounts;
Use inline CASE only when the expression has two short branches and fits comfortably on one line without wrapping. ORDER BY and GROUP BY are the most acceptable places for inline CASE, since the expression is secondary to the main query structure. Everywhere else, default to multi-line.
The standard multi-line format
The multi-line style used by most SQL style guides and auto-formatters follows four rules. Each rule has one purpose: make the branch structure scannable without counting keywords.
CASE on its own line, at the column indentation level
Each WHEN indented 2–4 spaces relative to CASE, with THEN on the same line as WHEN
ELSE at the same indent level as WHEN
END AS alias back at the CASE indent level — this line closes the column definition
The only live debate is whether THEN goes on the same line as WHEN or drops to the next line. Keep THEN on the WHEN line. The condition and its result belong together — the reader sees both in one eye-pass. A THEN on its own line doubles the visual height of the CASE block without adding information.
Standard multi-line CASE in a SELECT list
SELECT order_id, amount, CASE WHEN amount >= 1000 THEN 'Premium' WHEN amount >= 250 THEN 'Standard' WHEN amount >= 50 THEN 'Basic' ELSE 'Micro' END AS order_tier, created_atFROM orders;
CASE in a SELECT list with other columns
When CASE appears alongside other columns in a SELECT list, it takes the same indentation level as those columns — the CASE keyword aligns with user_id, email, and the rest. END AS lands back at that same level and terminates the column definition. No line break between END and AS, and no extra indentation for the alias.
Optional: if multiple WHEN branches have conditions of similar length, aligning THEN vertically makes the result column scannable at a glance. It's not mandatory, but many teams adopt it as a house rule once they see it in action.
Searched vs. simple CASE: format differences
SQL has two syntactically distinct forms of CASE. The searched form — the most common — evaluates a full boolean condition in each WHEN branch. The simple form compares a single column to a series of literal values. Both are documented in the PostgreSQL conditional expressions reference and work the same way across all major dialects. The format differs.
In the simple form, the column being compared goes on the CASE line itself — not inside a WHEN. The WHEN branches then list values rather than conditions. This makes the intent clearer: the reader sees the subject of the comparison once, at the top, then scans the values below it.
Use simple CASE when you're matching one column against a fixed set of values — it's more concise and signals intent faster than the equivalent searched form. Switch to searched CASE the moment any branch needs a compound condition or a range comparison.
Nested CASE: how deep is too deep
Two levels of CASE nesting is the practical readability limit. A CASE inside a THEN expression is manageable if the inner CASE is short. Three levels is almost never worth keeping in a single expression — the logic is hidden in the nesting, and the eye has to track two indentation ladders simultaneously. The fix is a CTE that resolves one dimension at a time, leaving each CASE expression shallow.
CASE in ORDER BY and GROUP BY
Inline CASE is acceptable in ORDER BY and GROUP BY when the expression is short. These positions are secondary to the SELECT list and rarely become the readability bottleneck. If the conditions grow complex, apply the same multi-line rules you would use in a SELECT list.
SQL CASE formatting checklist
Use multi-line layout for any CASE with three or more branches.
Keep THEN on the same line as WHEN — never split them onto separate lines.
Indent WHEN branches 2–4 spaces relative to the CASE keyword.
Put ELSE at the WHEN indent level — and always include it to avoid unexpected NULLs.
Close with END AS alias at the CASE indent level, with no line break between END and AS.
In simple CASE, put the column being compared on the CASE line — not inside a WHEN.
Optionally align THEN vertically across branches of similar condition length — it makes the result column scannable at a glance.
Inline CASE is acceptable in ORDER BY and GROUP BY for short, simple expressions.
Limit nesting to two levels. Refactor anything deeper into a CTE that resolves each dimension separately.
Wrapping up
CASE formatting comes down to one rule: make the branches scannable. Multi-line layout with THEN on the WHEN line, ELSE at the WHEN level, and END AS at the CASE level achieves that for any expression with three or more conditions. These same principles — consistent indentation, aligned keywords, predictable structure — run throughout SQL formatting best practices, and the comma style you choose for your SELECT list determines how the column containing CASE reads when you scroll past it in a review.
sqlfmt formats SQL across BigQuery, PostgreSQL, MySQL, SQLite, and SQL Server — paste a query with tangled CASE logic, pick your dialect, and it applies the multi-line layout automatically. The Pro plan adds live formatting as you type, so CASE branches snap into shape without a separate format pass.
-- Hard to tell where the CASE column begins and endsSELECT user_id, email,CASE WHEN status = 'active' AND trial_end IS NULL THEN 'Active'WHEN status = 'active' AND trial_end IS NOT NULL THEN 'Trial'ELSE 'Inactive'END AS status_label, created_atFROM users;
After: CASE indented as a column, END AS closes it
SELECT user_id, email, CASE WHEN status = 'active' AND trial_end IS NULL THEN 'Active' WHEN status = 'active' AND trial_end IS NOT NULL THEN 'Trial' ELSE 'Inactive' END AS status_label, created_atFROM users;
Searched CASE: each WHEN evaluates a condition
SELECT product_id, CASE WHEN price > 500 THEN 'Premium' WHEN price > 100 THEN 'Mid-range' ELSE 'Budget' END AS price_tierFROM products;
Simple CASE: subject column goes on the CASE line
SELECT product_id, CASE category WHEN 'electronics' THEN 'Electronics' WHEN 'clothing' THEN 'Clothing' WHEN 'food' THEN 'Food & Beverage' ELSE 'Other' END AS category_labelFROM products;
Before: three levels of nesting
-- Hard to follow: logic is buried in nested structureSELECT order_id, CASE WHEN region = 'EMEA' THEN CASE WHEN tier = 'pro' THEN CASE WHEN arr > 10000 THEN 'Enterprise' ELSE 'Pro' END ELSE 'Standard' END ELSE 'Other' END AS segmentFROM orders;
After: refactored with a CTE — each CASE is shallow
WITH tier_label AS ( SELECT order_id, region, CASE WHEN tier = 'pro' AND arr > 10000 THEN 'Enterprise' WHEN tier = 'pro' THEN 'Pro' ELSE 'Standard' END AS pro_tier FROM orders)SELECT order_id, CASE WHEN region = 'EMEA' THEN pro_tier ELSE 'Other' END AS segmentFROM tier_label;
CASE in ORDER BY: inline is fine for short expressions
-- Short simple CASE in ORDER BY: inline is readableSELECT region, SUM(revenue) AS total_revenueFROM salesGROUP BY regionORDER BY CASE region WHEN 'EMEA' THEN 1 WHEN 'APAC' THEN 2 ELSE 3 END;-- Complex conditions: switch to multi-lineORDER BY CASE WHEN status = 'active' AND tier = 'pro' THEN 1 WHEN status = 'active' THEN 2 ELSE 3 END;