Lesson 3 of 5 in this module

13/33 in track

CASE expressions

sql10 min

Overview

CASE WHEN maps values into labels. It is the if/else of SQL, evaluated per row.

On this page8 sections

The table we keep using

The ShopKart status column holds several different words, but the finance team only wants 3 groups: complete, dropped and other. You could write one query per status. CASE labels every row in a single query instead.

What is CASE?

CASE is SQL's if/else. For each row it tests conditions in order and returns the result of the first one that is true. Use it to map codes to labels, bucket numbers and flag rows without leaving the query.

CASE maps a status to a group
order_idorder_statusstatus_groupORD-001paidcompleteORD-002cancelleddroppedORD-003pendingother

paid becomes complete. cancelled becomes dropped. Anything else becomes other.

What are the real statuses?

We use the real orders table, 180 rows. Predict: how many distinct statuses does it hold?

SQLThe statuses in orders
SELECT order_status, COUNT(*) AS orders
FROM orders
GROUP BY order_status
ORDER BY orders DESC;
order_status  orders
paid          93
cancelled     30
pending       29
refunded      28

Four statuses: paid, cancelled, pending and refunded.

Words you will see in this lesson

Six words. The rest of the lesson uses only these.

WordWhat it means
CASESQL's if and else
WHENOne condition inside a CASE
THENThe value to use when the condition is true
ELSEThe value to use when no condition is true
MappingTurning many raw values into a few clean ones
BucketA group that many raw values are put into, such as paid or failed

What we need from it

Why not filter each status separately?

A dashboard needs 3 buckets: complete, dropped and other. Without CASE, analysts run one query per status and paste numbers into a slide. One missed status and the slide is wrong.

CASE puts the mapping in one place. A reviewer reads ELSE 'other' and knows that a new status will show up there instead of disappearing.

Write the mapping once, and let ELSE catch what you did not expect.

Trace it step by step

How does CASE decide?

  1. Test the WHEN conditions from top to bottom.
  2. Return the THEN value of the first condition that is true.
  3. If none is true, return ELSE. With no ELSE, the result is NULL.

Always write ELSE. Unmatched rows should be visible.

PatternExampleUse when
Map labelsWHEN status = 'paid' THEN 'complete'Governed names for dashboards
Bucket numbersWHEN total >= 100 THEN 'high'Size bands, risk scores
Flag rowsWHEN hours_late > 24 THEN 1 ELSE 0Late vs on time

What does the mapping do to real rows?

Predict: what group does a refunded order fall into?

SQLMap statuses to three groups
SELECT
  order_id,
  order_status,
  CASE
    WHEN order_status = 'paid'      THEN 'complete'
    WHEN order_status = 'cancelled' THEN 'dropped'
    ELSE 'other'
  END AS status_group
FROM orders
ORDER BY order_id
LIMIT 6;
order_id  order_status  status_group
ORD-0001  paid          complete
ORD-0002  refunded      other
ORD-0003  paid          complete
ORD-0004  cancelled     dropped
ORD-0005  paid          complete
ORD-0006  paid          complete

Pending and refunded both fall to other. If a new status appears tomorrow, it lands there as well, where you can see it.

Same table, next cut

How many orders are in each group?

CASE can sit inside GROUP BY. This is how dashboards get a label column from raw codes.

SQLCounting by group
SELECT
  CASE
    WHEN order_status = 'paid'      THEN 'complete'
    WHEN order_status = 'cancelled' THEN 'dropped'
    ELSE 'other'
  END AS status_group,
  COUNT(*) AS orders
FROM orders
GROUP BY 1
ORDER BY orders DESC;
status_group  orders
complete      93
other         57
dropped       30

93 complete, 30 dropped, and 57 other, which is pending 29 plus refunded 28. The 3 groups add back to 180, so no row was lost.

Four order statuses go through a CASE expression. Paid becomes complete (93), cancelled becomes dropped (30), and pending and refunded both fall to ELSE and become other (57)
CASE tests the WHEN lines from the top and stops at the first match. ELSE catches every status that matched none.

How do you bucket a number?

Use ranges, with the largest threshold first. WHEN order_total >= 1000 must come before >= 500, or every large order would match the wider branch first and stay there.

SQLBucket order totals
SELECT order_id, order_total,
  CASE
    WHEN order_total >= 1000 THEN 'high'
    WHEN order_total >= 500  THEN 'mid'
    ELSE 'low'
  END AS size_band
FROM orders
ORDER BY order_id
LIMIT 6;
order_id  order_total  size_band
ORD-0001  612.07       mid
ORD-0002  446.7        low
ORD-0003  1104.32      high
ORD-0004  339.9        low
ORD-0005  261.73       low
ORD-0006  431.34       low

Now swap the first two branches and see the damage.

SQLWrong order: high never appears
SELECT
  CASE
    WHEN order_total >= 500  THEN 'mid'
    WHEN order_total >= 1000 THEN 'high'
    ELSE 'low'
  END AS size_band,
  COUNT(*) AS orders
FROM orders
GROUP BY 1
ORDER BY 1;
size_band  orders
low        107
mid        73

There is no 'high' band at all, because every total of 1,000 or more was caught by the >= 500 branch first. Put the strictest condition first.

Watch outFirst match wins

Put the strictest condition first. Wide conditions at the top swallow the rows you meant for later branches.

What goes wrong when you test for NULL with =?

A comparison with NULL is never true. WHEN x = NULL never fires, so the row falls to ELSE.

SQLTesting for NULL
SELECT
  COUNT(*) FILTER (WHERE reading IS NULL) AS missing_readings,
  SUM(CASE WHEN reading = NULL THEN 1 ELSE 0 END)    AS counted_with_equals,
  SUM(CASE WHEN reading IS NULL THEN 1 ELSE 0 END)   AS counted_with_is_null
FROM telemetry;
missing_readings  counted_with_equals  counted_with_is_null
25                0                    25

25 readings are missing, but the equals version counted none of them. Use IS NULL.

What are the two forms of CASE?

Searched CASE has a full condition in each WHEN, as above. Simple CASE compares one expression with listed values and is shorter for exact matches.

SQLSimple CASE on one column
SELECT order_status,
  CASE order_status
    WHEN 'paid' THEN 1
    WHEN 'refunded' THEN 1
    ELSE 0
  END AS counts_toward_revenue_review
FROM orders
GROUP BY order_status
ORDER BY order_status;
order_status  counts_toward_revenue_review
cancelled     0
paid          1
pending       0
refunded      1

How do you count several things in one pass?

Put CASE inside an aggregate. SUM of a 1/0 flag counts rows that meet a condition, so one scan produces several totals side by side.

SQLConditional aggregation
SELECT
  SUM(CASE WHEN order_status = 'paid'      THEN 1 ELSE 0 END) AS paid_orders,
  SUM(CASE WHEN order_status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled_orders,
  ROUND(SUM(CASE WHEN order_status = 'paid' THEN order_total ELSE 0 END), 2) AS paid_revenue
FROM orders;
paid_orders  cancelled_orders  paid_revenue
93           30                43606.63

Three totals from one pass over the table. The ELSE 0 matters: without it a CASE returns NULL for rows that do not match, and a SUM over no matching rows would come back as NULL instead of 0.

RememberPython connection

CASE is the SQL cousin of if/elif/else. In pandas you will see np.where or .map for the same job. In PySpark you will see F.when().otherwise().

How does the whole lesson replay on the status column?

One status column, from codes to labels
  1. orders holds 180 rows with four statuses: paid 93, cancelled 30, pending 29, refunded 28.

    1. 4 statuses
  2. CASE tests paid first. Paid orders become complete, cancelled become dropped, anything else becomes other.

    1. WHEN paid
    2. WHEN cancelled
    3. ELSE
  3. Grouping by the label gives 93, 30 and 57. They add to 180, so no row was lost.

    1. 93 + 30 + 57
  4. Branch order matters. Put the strictest threshold first or the high band disappears.

    1. order of WHEN
  5. NULL needs IS NULL. A test with = NULL never fires.

    1. IS NULL
  6. CASE inside SUM makes several counts in one scan.

    1. conditional aggregation

Six things every data engineer must know about CASE:

  1. CASE returns the result of the first true WHEN, and nothing else is tested.
  2. Always write ELSE. Without it, unmatched rows become NULL.
  3. Order the branches from strictest to widest.
  4. Use IS NULL, never = NULL.
  5. CASE works in SELECT, GROUP BY and inside aggregates such as SUM.
  6. Check that the group counts add up to the table's row count.

Common beginner questions

Can CASE live in WHERE?

Yes, but a plain boolean is usually clearer. CASE is most useful in SELECT lists and in GROUP BY labels.

What if two WHEN clauses are true?

Only the first runs. That is why order is part of the policy.

Is CASE slow?

A CASE expression is evaluated row by row and is usually cheap next to joins and sorts. The bigger risk is a wrong label, not speed. If a query is slow, read its plan first.

What comes next

You can now map and bucket values. The next lesson is deduplication: keep 1 row per event when copies arrive. CASE often labels the winner after you rank the copies.

Practice

Run Sample to bucket order totals. Then complete Exercise: return order_id, order_status, and status_group mapping paid to complete, cancelled to dropped, and everything else to other. Limit 15.

Going further: CASE decides the order too

The lesson used CASE to label rows. It can also decide how rows are sorted. A plain ORDER BY on a text column sorts alphabetically, which is rarely the order a person means. A support team wants urgent tickets first, and the alphabet says otherwise.

Here is a table of 5 tickets. Predict what ORDER BY priority gives, then what the CASE version gives.

The tickets table.

ticket_idpriorityopened
T1low2026-09-01
T2urgent2026-09-03
T3medium2026-09-02
T4high2026-09-02
T5urgent2026-09-01
SQLAlphabetical order
SELECT ticket_id, priority
FROM tickets
ORDER BY priority
ticket_id  priority
T4         high
T1         low
T3         medium
T2         urgent
T5         urgent

Alphabetically urgent lands last. Give each word a rank with CASE and sort by that rank, then by the date opened as a tie-break, so the oldest urgent ticket comes first.

SQLBusiness order with a tie-break
SELECT ticket_id, priority
FROM tickets
ORDER BY CASE priority
           WHEN 'urgent' THEN 1
           WHEN 'high'   THEN 2
           WHEN 'medium' THEN 3
           ELSE 4
         END,
         opened
ticket_id  priority
T5         urgent
T2         urgent
T4         high
T3         medium
T1         low

T5 comes before T2 because both are urgent and T5 was opened first. The CASE value is used only for sorting, and it does not appear in the result unless you also add it to the SELECT list.

Try a new scenario

A new priority called critical is added to the data, and nobody updates the query. Where does it sort, and how would you make the query fail loudly instead?

Work it out, then open the answer

It falls into the ELSE branch, so it sorts with the lowest priority, after low and medium tickets, which is the opposite of what critical means. To fail loudly, drop the ELSE or give it a rank that is easy to spot, then check for rows that return NULL or the ELSE rank before the report is published. A new category should never be silently treated as the least important.

Can I sort by the CASE result without repeating it?

Yes. Put the CASE in the SELECT list with an alias such as priority_rank, then write ORDER BY priority_rank. Most engines accept an alias in ORDER BY. This also lets you see the rank next to each row while you are checking the logic.

Close the lesson and explain it

Without scrolling up, explain why ORDER BY on a text column does not give a business order, and write the CASE expression that fixes it.

Practicals · load into the editor

After you read the theory, run these in the pane on the right. They execute in this tab, no cluster.

Finished this lesson?

Mark it as read when you could explain it to someone else.

Practice this

Same ideas as interview drills. These challenges open in the studio with a dataset and tests already set up.

Rate:
Was this useful?