SQL CASE WHEN Statement: Syntax, Real Examples and Common Mistakes

Most reports need some logic: label orders as large or small, count paid and pending orders side by side, or show urgent items first. SQL CASE WHEN handles all of this directly inside a query, and it works across all the major databases.

We ran every query here in SQLite 3.50, and the results shown come from that run. CASE is part of standard SQL, so it behaves the same way in PostgreSQL, MySQL, SQL Server and Oracle for these examples.

What is CASE WHEN?

CASE is an expression, not a separate command. It evaluates conditions in order and returns a value, so it can go anywhere a value is allowed: in SELECT, WHERE, GROUP BY, ORDER BY, or inside functions. It always ends with END.

CASE
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  ELSE fallback
END

The sample table

CREATE TABLE orders (id INT, customer TEXT, total INT, status TEXT);

INSERT INTO orders VALUES
 (1,'Asha',1200,'paid'), (2,'Ben',300,'pending'),
 (3,'Asha',800,'paid'), (4,'Chitra',2500,'shipped'),
 (5,'Dev',150,'cancelled'), (6,'Ben',NULL,'pending');

Notice that order 6 has no total. That row matters later on.

Searched CASE

This is the more flexible form, since each WHEN has a full condition of its own:

SELECT id, total,
       CASE
         WHEN total >= 1000 THEN 'large'
         WHEN total >= 500 THEN 'medium'
         ELSE 'small'
       END AS size
FROM orders;

-- 1 | 1200 | large
-- 2 |  300 | small
-- 3 |  800 | medium
-- 4 | 2500 | large
-- 5 |  150 | small
-- 6 | NULL | small

Look at the last row. Order 6 has no total, yet it is labelled “small”. Both comparisons with NULL were not true, so it fell through to ELSE. We show how to fix this in the NULL section. Always give the result a name using AS, or you may get an awkward column name.

Simple CASE

When you are comparing one column with fixed values, the shorter form reads better:

SELECT id,
       CASE status
         WHEN 'paid' THEN 'Complete'
         WHEN 'shipped' THEN 'Complete'
         WHEN 'pending' THEN 'Waiting'
       END AS label
FROM orders;

-- 1 | Complete
-- 2 | Waiting
-- 3 | Complete
-- 4 | Complete
-- 5 | NULL
-- 6 | Waiting

Order matters

CASE stops at the first true condition, and this is the most common logic bug. Swap the two conditions from our earlier query:

SELECT id, total,
       CASE
         WHEN total >= 500 THEN 'medium'
         WHEN total >= 1000 THEN 'large'
         ELSE 'small'
       END AS size
FROM orders WHERE id IN (1, 4);

-- 1 | 1200 | medium
-- 4 | 2500 | medium

Now nothing can ever be “large”. A total of 2500 is also at least 500, so the first branch wins and the second is never reached. No error appears, only wrong labels. For ranges, put the most restrictive condition first, or write complete ranges, such as total >= 500 AND total < 1000.

What happens with no ELSE?

In our simple CASE example above, order 5 was cancelled, and no branch handled that, so the result was NULL. Sometimes that is exactly what you want, as the next section shows. But when building labels, a clear ELSE 'other' is usually better, since it makes unexpected values visible rather than leaving blanks in a report.

Pivot-style reports with SUM

This is one of the most useful patterns in everyday SQL. Wrapping CASE in an aggregate turns rows into columns, with one row per customer:

SELECT customer,
       SUM(CASE WHEN status = 'paid' THEN total ELSE 0 END) AS paid,
       SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS pending_orders
FROM orders
GROUP BY customer
ORDER BY customer;

-- Asha   | 2000 | 0
-- Ben    |    0 | 2
-- Chitra |    0 | 0
-- Dev    |    0 | 0

One query now gives both paid revenue and the number of pending orders, with no need for separate queries or joins. The pattern works in nearly every database, unlike the dedicated PIVOT syntax that only some offer. If GROUP BY is new to you, read SQL GROUP BY and HAVING.

Conditional counts

COUNT skips NULLs, so a CASE with no ELSE counts only the rows that match:

SELECT COUNT(*) AS all_orders,
       COUNT(CASE WHEN status = 'paid' THEN 1 END) AS paid_orders,
       COUNT(CASE WHEN total >= 1000 THEN 1 END) AS large_orders
FROM orders;

-- 6 | 2 | 2

Be careful: if you add ELSE 0 inside a COUNT, every row gets counted, because 0 is not NULL. Use ELSE 0 with SUM, and leave it out with COUNT. PostgreSQL and SQLite also support COUNT(*) FILTER (WHERE ...), which reads nicely, but it is not available everywhere.

Grouping by a CASE

You can create your own buckets, and then summarize them:

SELECT CASE WHEN total >= 1000 THEN 'large' ELSE 'other' END AS bucket,
       COUNT(*)
FROM orders
GROUP BY bucket
ORDER BY bucket;

-- large | 2
-- other | 4

Grouping by the alias works in SQLite, PostgreSQL and MySQL. SQL Server does not allow it, so you would have to repeat the whole expression, or put the query in a CTE or subquery first.

Custom sorting with ORDER BY

Alphabetical order is rarely what a business wants. CASE lets you define your own order:

SELECT id, status FROM orders
ORDER BY CASE status
           WHEN 'pending' THEN 1
           WHEN 'paid' THEN 2
           ELSE 3
         END, id;

-- 2 | pending
-- 6 | pending
-- 1 | paid
-- 3 | paid
-- 4 | shipped
-- 5 | cancelled

Pending orders come first, which is handy for a work queue. The second sort column, id, keeps the rows in a predictable order within each group.

CASE in UPDATE

CASE lets one statement apply different changes to different rows. Here, small pending orders are flagged for review:

UPDATE orders
SET status = CASE
               WHEN status = 'pending' AND total < 500 THEN 'review'
               ELSE status
             END;

SELECT id, status FROM orders WHERE customer = 'Ben';

-- 2 | review
-- 6 | pending

The ELSE status keeps other rows unchanged. Leaving it out is dangerous, because unmatched rows would be set to NULL. Without a WHERE clause this statement also touches every row, so on large tables add a filter, and consider wrapping the change in a transaction so that you can check the result before you commit. Notice too that order 6 did not change, because its NULL total did not satisfy total < 500.

NULL traps

NULL causes two separate problems with CASE. First, the simple form compares using equality, and nothing equals NULL, not even NULL itself:

SELECT CASE NULL WHEN NULL THEN 'matched' ELSE 'not matched' END;

-- not matched

So CASE col WHEN NULL never matches. Use the searched form with IS NULL instead. Second, as we saw with order 6, a NULL value slips past comparisons and lands in ELSE. The fix is to check for it first:

SELECT id,
       CASE
         WHEN total IS NULL THEN 'unknown'
         WHEN total >= 1000 THEN 'large'
         ELSE 'other'
       END AS size
FROM orders WHERE id IN (1, 2, 6);

-- 1 | large
-- 2 | other
-- 6 | unknown

Order 6 now gets an honest label. Our guide to SQL NULL values covers this behaviour in full.

CASE vs other options

That last option deserves a thought. If a CASE grows to thirty branches and appears in five queries, move the mapping into a table and join to it. Then a change is a single row update, rather than a code change in five places.

Common mistakes

• Wrong condition order. A broad condition first hides the narrower ones.
• Forgetting END. This is the most frequent syntax error.
• Using WHEN NULL. It never matches, so use WHEN col IS NULL.
• Adding ELSE 0 inside COUNT. It counts all rows.
• Omitting ELSE in an UPDATE. It can wipe values with NULL.
• Filtering by CASE in WHERE. It can stop an index from being used. See our guide to SQL query optimization.

Final thoughts

CASE WHEN is a small feature that does a great deal of work. Remember three rules: the first true branch wins, unmatched rows become NULL without an ELSE, and NULL needs its own check. To practise, take a report you currently build with several queries, and try to produce it as one query using SUM(CASE ...).

Frequently asked questions

Keep learning