CASE

Hook: CASE is SQL's traffic cop: it looks at each row, decides which lane it belongs in, and sends back one clean result.

Question: What is CASE in SQL, and how do you use it in SELECT and filtering?

Answer: CASE is an expression that returns a value based on conditions. You use it to turn raw rows into labels like high, medium, or low, or to choose different values in ORDER BY, SELECT, UPDATE, and other clauses. The big idea is that SQL checks the WHEN clauses from top to bottom and returns the first match; if nothing matches, ELSE is used, and if ELSE is missing, the result is NULL.

Interview-Ready Answer: I use CASE when I need row-by-row conditional logic in SQL. It is an expression, not a control-flow statement, so it returns one value per row based on the first matching WHEN. There are two forms: simple CASE for equality checks and searched CASE for full conditions. One important detail is that if I omit ELSE, SQL returns NULL, so I usually add ELSE to avoid surprise nulls.

What CASE is

Detailed Explanation: CASE has been part of the SQL standard since SQL-92. Think of it as a small decision machine inside a query: it does not loop, it does not change program flow, and it does not create new rows. It simply chooses one output value for each row.

1. SQL reads one row from the input.
2. It evaluates the WHEN clauses from top to bottom.
3. As soon as one condition is true, it stops and returns that THEN value.
4. If no condition matches, it returns ELSE.
5. If there is no ELSE, the result is NULL.

Two forms you should know

Use simple CASE when you are comparing one expression to fixed values. Use searched CASE when you need real logic like greater than, between, or IS NULL. In interviews, searched CASE is often the safer answer because it handles more cases.

Why order matters

The first matching branch wins. That means overlapping conditions must be ordered carefully: put the most specific rule first, then broader ones later. For example, if amount >= 100 comes before amount >= 50, a 120 order is labeled correctly. If you reverse them, the 120 order is still matched by the first broader rule and never reaches the later one.

Performance and edge cases

Conceptually, CASE is O(n) per row where n is the number of WHEN clauses, because SQL may test conditions until it finds a match. In practice, a CASE with 3 to 8 branches is usually cheap compared with scanning thousands or millions of rows. The real cost is usually the table scan, join, or sort around it, not the CASE itself.

• NULL gotcha: CASE x WHEN NULL THEN ... does not match NULL, because NULL means unknown, not equal.
• Type coercion: all THEN/ELSE results should be compatible types; many databases coerce them to one common type.
• Default behavior: omitted ELSE means NULL, which can silently break reports or grouping.
• No side effects: SQL expressions should be pure; do not rely on hidden work in later branches.

When to use it: use CASE to create labels, buckets, display text, conditional sorting, and safe fallbacks. If your goal is real row filtering, a plain WHERE predicate is usually clearer; CASE is best when you need to transform a value before showing or sorting it.

Real-World Story: Imagine a checkout service at an e-commerce company. The analytics team wants every order labeled as vip, standard, budget, or missing_data so the dashboard can track revenue mix. A CASE expression does that inside the reporting query without changing the raw table.

What goes wrong when someone misunderstands CASE? A developer forgets the ELSE branch, and orders with a new status or NULL amount fall through to NULL. The dashboard suddenly shows a blank category, the BI tool groups those rows into an unlabeled bucket, and the finance team thinks revenue disappeared. The symptoms are easy to spot: null category counts spike, scheduled report logs show unexpected NULLs, and customer support sees mismatched totals between the order page and the monthly report.

The fix is simple but important: make the rules explicit, put the most specific WHEN first, and always decide what the fallback should be. In production SQL, that small habit prevents silent data quality bugs that are painful to debug later.

Follow-up & Tricky Questions:

• What is the difference between simple CASE and searched CASE? Simple CASE compares one expression to fixed values with equality. Searched CASE lets you write full predicates like >, BETWEEN, and IS NULL, so it is more flexible.
• What happens if I omit ELSE? SQL returns NULL when no WHEN matches. That is legal, but it can create silent missing values in reports and dashboards.
• Does CASE stop after the first match? Logically, yes: the first true WHEN wins and later branches are skipped. That is why branch order matters when conditions overlap.
• Can I use CASE in ORDER BY? Yes. It is a common way to sort important rows first, like putting cancelled or urgent items at the end or beginning.
• Can CASE be used in WHERE? Yes, because CASE is an expression, but for pure filtering a direct WHERE condition is often clearer. CASE is better when the filter depends on a computed category.
• Why does WHEN NULL not match? Because NULL is not equal to anything, even another NULL. Use WHEN x IS NULL in searched CASE instead.
• Can THEN branches return different types? Most databases try to convert them to one common type, but incompatible mixes can fail or produce surprising conversions. Keep THEN and ELSE results type-compatible.
• Is CASE a statement like in programming languages? No. In SQL it is an expression, meaning it returns a value inside a larger query.

Common Mistakes:

• Putting broad WHEN clauses before specific ones. Fix: order branches from most specific to most general.
• Using WHEN NULL to test for missing values. Fix: use IS NULL in searched CASE.
• Forgetting ELSE and accidentally getting NULL. Fix: always choose an explicit fallback unless NULL is truly wanted.
• Using CASE to hide a bad WHERE clause. Fix: filter with WHERE when the goal is to remove rows, and use CASE when the goal is to label or transform them.

Memory Hook: Think of CASE as a stack of folder tabs: SQL checks the tabs from top to bottom, grabs the first folder that fits, and stops there.

Cheat Sheet:

• CASE returns one value per row.
• Simple CASE = equality checks.
• Searched CASE = real conditions.
• First match wins.
• Missing ELSE means NULL.
• Use IS NULL, not WHEN NULL.

Practice Tasks:

• Write a CASE that labels orders as small, medium, or large by amount.
• Add a CASE to sort refunded or cancelled orders last in an ORDER BY clause.
• Rewrite a simple CASE into a searched CASE so it handles NULL correctly.