The SQL CASE Expression Explained Simply (With Examples)

By /

Sooner or later every report needs a little logic. Show “Paid” instead of the status code 1. Label orders as small, medium or large. Count how many customers are from each city in one row. Sort by a custom order that isn’t alphabetical. All of that comes down to one tool: the CASE expression.

If you know IF ... THEN ... ELSE from Excel or any programming language, you already understand the idea. CASE is SQL’s version, and it works almost everywhere in a query.

Flow diagram of a SQL CASE expression labelling order amounts as Large, Medium or Small

The Sample Data

order_idcustomeramountstatus
1Sara6501
2Omar1202
3Lina451
4Ali3003
5Huda801

Status codes: 1 = Paid, 2 = Pending, 3 = Cancelled.

Searched CASE: Conditions in Order

This is the form you’ll use most. Each WHEN has its own condition:

SELECT order_id, customer, amount,
       CASE
           WHEN amount >= 500 THEN 'Large'
           WHEN amount >= 100 THEN 'Medium'
           ELSE 'Small'
       END AS order_size
FROM orders;
order_idcustomeramountorder_size
1Sara650Large
2Omar120Medium
3Lina45Small
4Ali300Medium
5Huda80Small

The key rule: SQL checks the WHEN lines from top to bottom and stops at the first one that’s true. Sara’s 650 is also greater than 100, but she’s already been labelled “Large” by then. That’s why you put the strictest condition first.

If no condition matches and there’s no ELSE, the result is NULL. I almost always include an ELSE, even if it’s just ELSE 'Unknown', so gaps in my logic are easy to spot.

Simple CASE: Matching One Value

When you’re comparing a single column to a list of exact values, there’s a shorter form:

SELECT order_id, customer,
       CASE status
           WHEN 1 THEN 'Paid'
           WHEN 2 THEN 'Pending'
           WHEN 3 THEN 'Cancelled'
           ELSE 'Unknown'
       END AS status_name
FROM orders;

It reads nicely, but it can only test equality. For ranges, NULL checks or several columns, use the searched form.

A tip from real projects: if you find yourself writing the same status CASE in many queries, the codes probably deserve their own lookup table that you can JOIN to. CASE is best for logic that’s specific to one report.

CASE Inside Aggregates (Conditional Counting)

This trick is one of the most useful things in SQL. Put a CASE inside SUM or COUNT to count different things in one pass:

SELECT
    COUNT(*)                                          AS total_orders,
    SUM(CASE WHEN status = 1 THEN 1 ELSE 0 END)       AS paid_orders,
    SUM(CASE WHEN status = 2 THEN 1 ELSE 0 END)       AS pending_orders,
    SUM(CASE WHEN status = 1 THEN amount ELSE 0 END)  AS paid_amount
FROM orders;
total_orderspaid_orderspending_orderspaid_amount
531775

Paid orders are Sara (650), Lina (45) and Huda (80), so the paid amount is 775. Combine this with GROUP BY and you get a pivot-style report, like one row per month with a column per status, without any special pivot syntax.

CASE in ORDER BY: Custom Sorting

Want pending orders at the top, then paid, then cancelled? Alphabetical won’t do it, but CASE can:

SELECT order_id, customer, status
FROM orders
ORDER BY CASE status
             WHEN 2 THEN 1   -- Pending first
             WHEN 1 THEN 2   -- then Paid
             WHEN 3 THEN 3   -- Cancelled last
         END,
         order_id;

CASE in WHERE and UPDATE

You can use CASE anywhere an expression is allowed. A common one is an update that applies different rules to different rows:

-- Different price increases by category
UPDATE products
SET price = price * CASE category
                        WHEN 'Books'      THEN 1.05
                        WHEN 'Stationery' THEN 1.10
                        ELSE 1.00
                    END;

In a WHERE clause, plain AND/OR logic is usually clearer than CASE, and it lets the database use indexes more easily. Keep CASE for the SELECT list, aggregates and sorting.

Watch Out for NULL

A condition like WHEN discount = NULL is never true. Test for null with IS NULL:

CASE
    WHEN discount IS NULL THEN 'No discount'
    WHEN discount >= 20   THEN 'Big discount'
    ELSE 'Small discount'
END

Nulls catch everyone at least once. There’s a whole article on them: NULL in SQL Explained Simply.

Two Forms Side by Side

Simple CASESearched CASE
SyntaxCASE col WHEN value THEN ...CASE WHEN condition THEN ...
Can testEquality with one columnAny condition: ranges, several columns, IS NULL
Best forTranslating codes into labelsGrouping, bands and business rules

Common Mistakes

  • Putting conditions in the wrong order, so a broad WHEN catches rows meant for a later one.
  • Forgetting END. Every CASE needs one.
  • Returning different data types from different branches, like a number in one and text in another. Keep them all the same type.
  • Leaving out ELSE and then being surprised by NULL results.

Conclusion

CASE brings if/then/else logic into SQL. Use the searched form for conditions, the simple form for mapping codes to labels, and remember that the first matching WHEN wins. Inside SUM and COUNT it becomes a reporting superpower.

Take one of your existing reports and see whether a few CASE expressions could replace several separate queries. They usually can.

Scroll to Top