SQL CASE Expression — Conditional Logic in SELECT & ORDER BY

CASE Branching

Value flows through WHEN branches

SQL evaluates branches top to bottom and returns the result of the first true condition.

Schema Context

users ↔ orders ER

orders samplejson

1| order_id | customer_id | status   | total_amount |
2|----------|-------------|----------|--------------|
3| 1001     | 1           | paid     | 49.99        |
4| 1002     | 1           | shipped  | 12.50        |
5| 1003     | 3           | pending  | 8.00         |

users samplejson

1| user_id | email               | is_active |
2|---------|---------------------|-----------|
3| 1       | [email protected]   | true      |
4| 2       | [email protected]     | false     |
5| 3       | [email protected]   | true      |

Searched CASE — Independent Conditions

Order size tiers by amountjson

1SELECT
2  order_id,
3  total_amount,
4  CASE
5    WHEN total_amount >= 40 THEN 'large'
6    WHEN total_amount >= 15 THEN 'medium'
7    ELSE 'small'
8  END AS size_tier
9FROM orders;

Expected outputjson

1| order_id | total_amount | size_tier |
2|---|---|---|
3| 1001 | 49.99 | large |
4| 1002 | 12.50 | small |
5| 1003 | 8.00 | small |

Simple CASE — Match One Expression

Style	Syntax	Best for
Searched CASE	CASE WHEN condition THEN …	Ranges, comparisons, multiple columns
Simple CASE	CASE col WHEN value THEN …	Mapping known values (status codes)

Human-readable order statusjson

1SELECT
2  order_id,
3  status,
4  CASE status
5    WHEN 'paid' THEN 'Payment received'
6    WHEN 'shipped' THEN 'On the way'
7    WHEN 'pending' THEN 'Awaiting payment'
8    ELSE 'Unknown'
9  END AS status_label
10FROM orders;

Expected outputjson

1| order_id | status | status_label |
2|---|---|---|
3| 1001 | paid | Payment received |
4| 1002 | shipped | On the way |
5| 1003 | pending | Awaiting payment |

CASE in SELECT with JOIN

User activity label on joined rowsjson

1SELECT
2  u.email,
3  o.order_id,
4  CASE
5    WHEN o.order_id IS NULL THEN 'No orders'
6    WHEN o.total_amount >= 40 THEN 'Big spender order'
7    ELSE 'Regular order'
8  END AS order_category
9FROM users u
10INNER JOIN orders o ON u.user_id = o.customer_id;

Expected outputjson

1| email | order_id | order_category |
2|---|---|---|
3| [email protected] | 1001 | Big spender order |
4| [email protected] | 1002 | Regular order |
5| [email protected] | 1003 | Regular order |

CASE in ORDER BY

Sort by business priority instead of alphabetical status names.

Pending orders first, then paid, then shippedjson

1SELECT order_id, status, total_amount
2FROM orders
3ORDER BY
4  CASE status
5    WHEN 'pending' THEN 1
6    WHEN 'paid' THEN 2
7    WHEN 'shipped' THEN 3
8    ELSE 4
9  END,
10  total_amount DESC;

Expected outputjson

1| order_id | status | total_amount |
2|---|---|---|
3| 1003 | pending | 8.00 |
4| 1001 | paid | 49.99 |
5| 1002 | shipped | 12.50 |

CASE with Aggregates (Preview)

Paid revenue only (conditional SUM)json

1SELECT
2  SUM(CASE WHEN status = 'paid' THEN total_amount ELSE 0 END) AS paid_revenue,
3  SUM(CASE WHEN status != 'paid' THEN total_amount ELSE 0 END) AS unpaid_revenue
4FROM orders;

Expected outputjson

1| paid_revenue | unpaid_revenue |
2|---|---|
3| 49.99 | 20.50 |

Next step

Combine CASE with GROUP BY to get conditional totals per customer — see GROUP BY & Aggregates.

Common Mistakes

Missing ELSE — unmatched rows get NULL in the CASE column.
Returning different data types across THEN branches (e.g. number vs text) — keep types consistent.
Overlapping WHEN conditions where order matters — first match wins.
Using IF() in portable SQL — prefer CASE for PostgreSQL, SQLite, and SQL Server.

Practice Prompts

Label each user as active or inactive based on is_active.
Sort orders so paid appears before pending before shipped.
Count how many orders are "high value" (>= $40) using CASE inside COUNT (hint: CASE returns 1 or 0).

Try These Tools

SQL Query Explainer

Understand CASE query logic

Open tool

SQL Formatter

Format multi-line CASE blocks

Open tool

Continue Learning

WHERE — Filtering Rows

Filter rows vs label them

SQL COUNT() Function

Count with conditions

GROUP BY & Aggregates

Conditional aggregates

Frequently Asked Questions

What is a CASE expression?

SQL's conditional logic — like if/else. It evaluates conditions and returns a value from the first matching branch.

What is the difference between simple and searched CASE?

Simple CASE compares one expression to values: CASE status WHEN 'paid' THEN …. Searched CASE uses independent conditions: CASE WHEN total_amount >= 50 THEN ….

Can CASE be used in ORDER BY?

Yes. CASE in ORDER BY lets you define custom sort priority (e.g. pending before paid).

Should I always include ELSE?

Recommended. Without ELSE, non-matching rows return NULL for that column.

Is CASE the same as IF()?

Logically similar, but CASE is standard SQL. IF() exists in MySQL only; prefer CASE for portability.