CASE Statement: Searched & Simple

SQL has no IF inside a SELECT — but it has something better: the CASE expression. With it you can label players "Elite", "Solid" or "Developing", convert "India" to "IN", or turn a condition into a 1/0 flag — all row by row.

Definition

A CASE expression evaluates a list of conditions in order and returns the result of the first condition that is TRUE. If no condition is TRUE, it returns the ELSE value — or NULL if there is no ELSE.

• Expression — CASE produces one value per row, just like a single-row function, so it can be used in SELECT, WHERE, ORDER BY, GROUP BY and inside aggregates.
• In order — conditions are checked from top to bottom.
• First TRUE wins — once a condition matches, the rest are skipped for that row.

Syntax

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN condition3 THEN result3
    ELSE default_result
END AS new_column

Example — derive a new category column

SELECT
    name,
    runs,
    CASE
        WHEN runs >= 700 THEN 'Elite'
        WHEN runs >= 400 THEN 'Solid'
        ELSE 'Developing'
    END AS category
FROM players;

How it works: step 1 — check conditions top to bottom; step 2 — the first TRUE one wins; step 3 — no match → ELSE. Kohli's 750 matches >= 700 and stops there, even though >= 400 is also true. CASE categorises values into groups as a brand-new column.

The two forms of CASE

Both give the same result for equality checks — pick the one that is easier to read.

Example — simple CASE to standardise values

SELECT
    name,
    CASE country
        WHEN 'India'     THEN 'IN'
        WHEN 'England'   THEN 'ENG'
        WHEN 'Australia' THEN 'AUS'
        ELSE 'N/A'
    END AS country_code
FROM players;

Read the country, map it to a short code — messy values become one clean format.

CASE — 3 rules to remember

1. Order matters. SQL checks WHEN from top to bottom and the first TRUE one wins. Put the strictest condition (700) before the looser one (400). If you swap them:

SELECT
    name,
    runs,
    CASE
        WHEN runs >= 400 THEN 'Solid'
        WHEN runs >= 700 THEN 'Elite'
        ELSE 'Developing'
    END AS category
FROM players;

Nobody is 'Elite' any more — 750 already matched >= 400, so the >= 700 line is never reached.

2. ELSE is optional. No match and no ELSE? You get NULL:

SELECT
    name,
    runs,
    CASE WHEN runs >= 700 THEN 'Elite' END AS tag
FROM players;

Add ELSE 'N/A' (or another default) when NULL isn't what you want.

3. Same data type. All THEN/ELSE results must be compatible — don't mix text with numbers. CASE WHEN runs >= 700 THEN 'Elite' ELSE 0 END fails in SQL Server ("Conversion failed when converting the varchar value 'Elite' to data type int"), because SQL Server tries to make everything an INT.

Common mistakes

• ❌ Forgetting END → syntax error.
• ❌ Loose condition first (>= 400 before >= 700) → wrong categories.
• ❌ CASE team WHEN NULL THEN 'Unsold' END — the simple form uses =, and team = NULL is never TRUE. ✅ CASE WHEN team IS NULL THEN 'Unsold' ELSE team END.
• ❌ Mixing types in THEN/ELSE → conversion errors.
• ❌ Overlapping ranges with gaps: WHEN runs > 700 … WHEN runs < 700 — exactly 700 falls through to ELSE.

Interview tip

Q: What is the difference between a simple and a searched CASE? A simple CASE compares one expression against a list of values using equality (CASE country WHEN 'India' THEN …). A searched CASE evaluates independent Boolean conditions (CASE WHEN runs >= 700 AND country = 'India' THEN …), so it supports ranges, IS NULL, LIKE and multiple columns. Every simple CASE can be rewritten as a searched CASE, but not the other way round.