SQL CASE Statement: CASE WHEN Syntax and Examples

CASE is how you write if/then/else logic in SQL. It checks conditions from top to bottom and returns the value for the first one that is true. Here is the syntax, then worked examples for every place you can use it, with real output.

By Matthew Rathbone · November 11, 2026

A CASE statement (strictly, a CASE expression) returns one value out of several, depending on which condition is true. It works in SELECT, WHERE, ORDER BY, GROUP BY, HAVING and UPDATE, and the syntax is the same in MySQL, PostgreSQL, SQL Server, SQLite and Oracle.

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

Jump to: syntax · sample data · examples · CASE and NULL · SUM and COUNT with CASE · WHERE, ORDER BY, GROUP BY · UPDATE · IF, IIF and friends · mistakes · FAQ

SQL CASE syntax

There are two forms. Both end with END, and in both the ELSE is optional.

Form	Syntax	Use it when
Searched `CASE`	`CASE WHEN amount >= 500 THEN 'large' ELSE 'small' END`	Each branch has its own condition: ranges, several columns, `AND`/`OR`, `IS NULL`. This is the one you will use most
Simple `CASE`	`CASE status WHEN 'S' THEN 'Shipped' ELSE 'Other' END`	You are comparing one column to a list of exact values

Three rules explain almost everything CASE does:

Conditions are checked in order, and the first true one wins. The rest are skipped.
If nothing matches and there is no ELSE, the result is NULL.
CASE returns a single value, so it can go anywhere a column or value can go.

Sample data

Every query below was run on SQLite 3.45 and the output is copied from the real result. The queries use only standard SQL, so they work unchanged in MySQL, PostgreSQL, SQL Server and Oracle.

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, country TEXT, amount REAL, status TEXT, discount_code TEXT);
INSERT INTO orders VALUES
 (1, 'Ana',   'US', 250.00, 'S', 'VIP10'),
 (2, 'Ben',   'CA',  40.00, 'P', NULL),
 (3, 'Chloe', 'US', 120.00, 'S', NULL),
 (4, 'Dev',   'UK',  15.00, 'C', 'WELCOME'),
 (5, 'Elif',  'DE', 980.00, 'S', 'VIP10'),
 (6, 'Femi',  'US',  75.00, 'P', NULL),
 (7, 'Gus',   'CA', 310.00, 'R', NULL),
 (8, 'Hana',  NULL,  60.00, 'S', 'WELCOME');

The status codes are S (shipped), P (pending), C (cancelled) and R (returned).

CASE WHEN examples

1. Put rows into buckets

The classic use: turn a number into a label.

SELECT customer, amount,
       CASE
         WHEN amount >= 500 THEN 'large'
         WHEN amount >= 100 THEN 'medium'
         ELSE 'small'
       END AS order_size
FROM orders;

customer | amount | order_size
Ana      | 250.0  | medium
Ben      | 40.0   | small
Chloe    | 120.0  | medium
Dev      | 15.0   | small
Elif     | 980.0  | large
Femi     | 75.0   | small
Gus      | 310.0  | medium
Hana     | 60.0   | small

Always give the result a name with AS. Without it the column heading is the whole CASE expression (or blank, depending on the database).

2. Simple CASE: turn codes into labels

When every branch compares the same column to an exact value, name the column once after CASE:

SELECT id, status,
       CASE status
         WHEN 'S' THEN 'Shipped'
         WHEN 'P' THEN 'Pending'
         WHEN 'C' THEN 'Cancelled'
         ELSE 'Other'
       END AS status_label
FROM orders;

id | status | status_label
1  | S      | Shipped
2  | P      | Pending
3  | S      | Shipped
4  | C      | Cancelled
5  | S      | Shipped
6  | P      | Pending
7  | R      | Other
8  | S      | Shipped

Order 7 has status R, which is not listed, so it falls through to ELSE.

3. CASE WHEN with multiple conditions

A WHEN can hold any condition you could write in a WHERE clause. Combine checks with AND, OR, IN and IS NULL, and look at more than one column:

SELECT customer, country, amount,
       CASE
         WHEN country = 'US' AND amount >= 100 THEN 'free shipping'
         WHEN country IN ('US', 'CA')          THEN 'standard'
         WHEN country IS NULL                  THEN 'address needed'
         ELSE 'international'
       END AS shipping
FROM orders;

customer | country | amount | shipping
Ana      | US      | 250.0  | free shipping
Ben      | CA      | 40.0   | standard
Chloe    | US      | 120.0  | free shipping
Dev      | UK      | 15.0   | international
Elif     | DE      | 980.0  | international
Femi     | US      | 75.0   | standard
Gus      | CA      | 310.0  | standard
Hana     | NULL    | 60.0   | address needed

Femi is in the US but spent under 100, so the first branch fails and the second one catches that order.

4. The order of WHEN branches matters

Because the first true condition wins, put the most specific test first. Here the branches from example 1 are in the wrong order:

SELECT customer, amount,
       CASE
         WHEN amount >= 100 THEN 'medium'
         WHEN amount >= 500 THEN 'large'
         ELSE 'small'
       END AS order_size
FROM orders
WHERE amount >= 500;

customer | amount | order_size
Elif     | 980.0  | medium

980 is at least 100, so it is labelled medium and the large branch is never reached. No error, just a wrong answer. For ranges, go from the highest threshold down (or the lowest up) and never mix directions.

5. Leaving out ELSE gives NULL

SELECT customer, status,
       CASE WHEN status = 'S' THEN 'done' END AS progress
FROM orders
WHERE id <= 3;

customer | status | progress
Ana      | S      | done
Ben      | P      | NULL
Chloe    | S      | done

That is sometimes exactly what you want (see counting with CASE below). When it is not, add an ELSE so that unexpected values are visible instead of silently blank.

6. Nested CASE

The result of a branch can be another CASE:

SELECT customer, country, amount,
       CASE
         WHEN country = 'US' THEN
           CASE WHEN amount >= 100 THEN 'US - free shipping' ELSE 'US - standard' END
         ELSE 'non-US'
       END AS shipping
FROM orders
WHERE id IN (1, 2, 6);

customer | country | amount | shipping
Ana      | US      | 250.0  | US - free shipping
Ben      | CA      | 40.0   | non-US
Femi     | US      | 75.0   | US - standard

It works, but nesting gets hard to read quickly. A flat CASE with AND conditions, as in example 3, usually says the same thing more clearly.

CASE and NULL

This catches nearly everyone once. A simple CASE compares with =, and NULL = NULL is not true, so WHEN NULL never matches:

SELECT customer, discount_code,
       CASE discount_code WHEN NULL THEN 'none' ELSE 'has code' END AS wrong,
       CASE WHEN discount_code IS NULL THEN 'none' ELSE 'has code' END AS correct
FROM orders
WHERE id <= 3;

customer | discount_code | wrong    | correct
Ana      | VIP10         | has code | has code
Ben      | NULL          | has code | none
Chloe    | NULL          | has code | none

To test for NULL, use the searched form with IS NULL. If all you want is a fallback value for a NULL, COALESCE is shorter: COALESCE(discount_code, 'none'). There is more on how NULL behaves in how to use NULL values in SQL.

SUM and COUNT with CASE (conditional aggregation)

Put a CASE inside an aggregate function to count or total only the rows that meet a condition. It lets you answer several questions in one pass over the table:

SELECT COUNT(*)                                           AS orders,
       SUM(CASE WHEN status = 'S' THEN 1 ELSE 0 END)      AS shipped,
       COUNT(CASE WHEN status = 'P' THEN 1 END)           AS pending,
       SUM(CASE WHEN status = 'S' THEN amount ELSE 0 END) AS shipped_revenue
FROM orders;

orders | shipped | pending | shipped_revenue
8      | 4       | 2       | 1410.0

Two patterns are at work, and they give the same count:

SUM(CASE WHEN ... THEN 1 ELSE 0 END) adds a 1 for each matching row.
COUNT(CASE WHEN ... THEN 1 END) has no ELSE, so non-matching rows become NULL, and COUNT skips NULL. Do not write ELSE 0 here: COUNT would count the zeros too.

Pivot rows into columns

Add a GROUP BY and each CASE becomes a column of a pivot table. This works in every database, including the ones with no PIVOT keyword:

SELECT COALESCE(country, 'unknown') AS country,
       SUM(CASE WHEN status = 'S' THEN amount ELSE 0 END) AS shipped,
       SUM(CASE WHEN status = 'P' THEN amount ELSE 0 END) AS pending,
       SUM(CASE WHEN status IN ('C', 'R') THEN amount ELSE 0 END) AS lost
FROM orders
GROUP BY country
ORDER BY country;

country | shipped | pending | lost
CA      | 0       | 40.0    | 310.0
DE      | 980.0   | 0       | 0
UK      | 0       | 0       | 15.0
US      | 370.0   | 75.0    | 0
unknown | 60.0    | 0       | 0

See how to pivot in SQL for more on this technique, and COUNT DISTINCT for counting unique values that meet a condition.

Avoid dividing by zero

CASE can wrap aggregates as well as sit inside them. Here it returns NULL instead of dividing when a country has no shipped orders:

SELECT country,
       COUNT(*) AS orders,
       SUM(CASE WHEN status = 'R' THEN 1 ELSE 0 END) AS returned,
       CASE WHEN SUM(CASE WHEN status = 'S' THEN 1 ELSE 0 END) = 0 THEN NULL
            ELSE SUM(amount) / SUM(CASE WHEN status = 'S' THEN 1 ELSE 0 END)
       END AS revenue_per_shipped
FROM orders
WHERE country IS NOT NULL
GROUP BY country;

country | orders | returned | revenue_per_shipped
CA      | 2      | 1        | NULL
DE      | 1      | 0        | 980.0
UK      | 1      | 0        | NULL
US      | 3      | 0        | 222.5

CASE in WHERE, ORDER BY, GROUP BY and HAVING

CASE in a WHERE clause

Use it when the value you compare against depends on the row. Here US orders need to reach 100 and everything else needs 300:

SELECT customer, country, amount
FROM orders
WHERE amount >= CASE WHEN country = 'US' THEN 100 ELSE 300 END;

customer | country | amount
Ana      | US      | 250.0
Chloe    | US      | 120.0
Elif     | DE      | 980.0
Gus      | CA      | 310.0

You can often write the same filter with plain AND/OR, which is easier for the database to match to an index:

WHERE (country = 'US' AND amount >= 100)
   OR ((country <> 'US' OR country IS NULL) AND amount >= 300)

Note the extra IS NULL check. The ELSE in the CASE version covers rows with no country for free; the AND/OR version has to say so. On small tables, pick whichever reads better.

CASE in ORDER BY: a custom sort order

Alphabetical order is rarely the order people want for statuses or priorities. Map each value to a number and sort by that:

SELECT id, customer, status
FROM orders
ORDER BY CASE status
           WHEN 'P' THEN 1
           WHEN 'S' THEN 2
           WHEN 'R' THEN 3
           ELSE 4
         END,
         id;

id | customer | status
2  | Ben      | P
6  | Femi     | P
1  | Ana      | S
3  | Chloe    | S
5  | Elif     | S
8  | Hana     | S
7  | Gus      | R
4  | Dev      | C

Pending orders come first, then shipped, returned and everything else.

CASE in GROUP BY

Group by the buckets from example 1 to count how many orders fall in each:

SELECT CASE
         WHEN amount >= 500 THEN 'large'
         WHEN amount >= 100 THEN 'medium'
         ELSE 'small'
       END AS order_size,
       COUNT(*)    AS orders,
       SUM(amount) AS revenue
FROM orders
GROUP BY CASE
         WHEN amount >= 500 THEN 'large'
         WHEN amount >= 100 THEN 'medium'
         ELSE 'small'
       END
ORDER BY revenue DESC;

order_size | orders | revenue
large      | 1      | 980.0
medium     | 3      | 680.0
small      | 4      | 190.0

Repeating the whole expression in GROUP BY is the form that works everywhere. MySQL, PostgreSQL and SQLite also accept the alias (GROUP BY order_size); SQL Server and Oracle (before 23ai) do not. To avoid the repetition in any database, compute the label in a CTE or subquery and group by it in the outer query.

CASE in HAVING

HAVING filters groups, so a conditional aggregate fits naturally. Countries with at least two shipped orders:

SELECT country, COUNT(*) AS orders
FROM orders
GROUP BY country
HAVING SUM(CASE WHEN status = 'S' THEN 1 ELSE 0 END) >= 2;

country | orders
US      | 3

UPDATE with CASE

A CASE in the SET clause applies a different change to different rows in one statement:

UPDATE orders
SET amount = CASE
               WHEN discount_code = 'VIP10'   THEN ROUND(amount * 0.90, 2)
               WHEN discount_code = 'WELCOME' THEN ROUND(amount - 5, 2)
               ELSE amount
             END;

SELECT id, customer, discount_code, amount FROM orders;

id | customer | discount_code | amount
1  | Ana      | VIP10         | 225.0
2  | Ben      | NULL          | 40.0
3  | Chloe    | NULL          | 120.0
4  | Dev      | WELCOME       | 10.0
5  | Elif     | VIP10         | 882.0
6  | Femi     | NULL          | 75.0
7  | Gus      | NULL          | 310.0
8  | Hana     | WELCOME       | 55.0

Do not forget ELSE amount. Without it, every row that matches no branch has its amount set to NULL. A safer habit is to add WHERE discount_code IN ('VIP10', 'WELCOME') as well, so that the statement only touches the rows it means to. More in how to use UPDATE in SQL.

CASE vs IF, IIF, DECODE and COALESCE

CASE is part of standard SQL and behaves the same everywhere. Most databases also have shortcuts for simple situations:

Database	Shortcut	Equivalent CASE
MySQL	`IF(amount >= 100, 'big', 'small')`	`CASE WHEN amount >= 100 THEN 'big' ELSE 'small' END`
SQL Server, SQLite	`IIF(amount >= 100, 'big', 'small')`
PostgreSQL	None. Use `CASE`
Oracle	`DECODE(status, 'S', 'Shipped', 'Other')`	`CASE status WHEN 'S' THEN 'Shipped' ELSE 'Other' END`
All	`COALESCE(a, b)`	`CASE WHEN a IS NOT NULL THEN a ELSE b END`
All	`NULLIF(a, b)`	`CASE WHEN a = b THEN NULL ELSE a END`
PostgreSQL, SQLite	`COUNT(*) FILTER (WHERE status = 'S')`	`COUNT(CASE WHEN status = 'S' THEN 1 END)`

The shortcuts are fine for a single yes/no test. Reach for CASE when there are three or more outcomes, or when the query has to run on more than one database. The official references are worth a bookmark: PostgreSQL, SQL Server, MySQL and SQLite.

One naming trap: SQL Server, MySQL and Oracle also have an IF ... ELSE statement for stored procedures and scripts. That controls which statements run. It cannot go inside a SELECT; CASE can.

Common mistakes

Forgetting END. Every CASE needs one, and it is the most common cause of a syntax error near FROM or AS.
Branches in the wrong order. The first true condition wins, so WHEN amount >= 100 placed before WHEN amount >= 500 swallows the large orders (example 4).
Writing WHEN NULL or = NULL. Neither ever matches. Use WHEN col IS NULL.
No ELSE in an UPDATE. Unmatched rows are set to NULL.
COUNT(CASE ... ELSE 0 END). COUNT counts zeros. Drop the ELSE, or use SUM.
Mixing data types across branches. CASE WHEN amount >= 100 THEN amount ELSE 'small' END returns a number in one branch and text in another. SQLite and MySQL allow it; PostgreSQL rejects it, and SQL Server tries to convert 'small' to a number and fails when it reaches such a row. Make every branch the same type, with CAST if you need to.
Mixing the two forms. CASE status WHEN status = 'S' THEN ... compares status to the result of status = 'S'. Either name the column after CASE or put full conditions after WHEN, not both.
Using the alias in WHERE. WHERE order_size = 'large' fails because WHERE runs before SELECT names the column. Repeat the expression or wrap the query in a CTE.

FAQ

What is a CASE statement in SQL?

It is SQL's if/then/else. CASE checks a list of conditions in order and returns the value attached to the first one that is true, or the ELSE value if none are.

What is the difference between CASE and CASE WHEN?

They are the same thing. "CASE WHEN" usually means the searched form (CASE WHEN condition THEN ...), as opposed to the simple form (CASE column WHEN value THEN ...).

Can I use multiple conditions in a CASE WHEN?

Yes, in two ways: add more WHEN branches for more outcomes, and combine tests inside one WHEN with AND, OR and IN.

Can I use CASE in a WHERE clause?

Yes. CASE returns a value, so compare it to something: WHERE amount >= CASE WHEN country = 'US' THEN 100 ELSE 300 END. Plain AND/OR is often clearer.

What does CASE return if no condition is true?

The ELSE value. If there is no ELSE, it returns NULL.

Is it a CASE statement or a CASE expression?

Technically an expression, because it produces a value. Most people call it a statement, and both names refer to the same thing in a query. (Oracle PL/SQL and MySQL stored procedures also have a separate CASE statement for procedural code, which ends with END CASE.)

Does SQL have an IF statement?

Inside a query, CASE is the portable answer. MySQL adds an IF() function and SQL Server and SQLite add IIF(), each for a single true/false test.

Does CASE stop at the first match?

Yes. Later branches are not returned even if they are also true, which is why branch order matters.

How do I count rows that meet a condition?

SUM(CASE WHEN condition THEN 1 ELSE 0 END) or COUNT(CASE WHEN condition THEN 1 END).

Practice

Reading about CASE only gets you so far. Try the free interactive CASE lesson in your browser, where you write the query and get instant feedback, then work through the SQL practice problems. CASE shows up constantly in SQL interview questions, usually paired with GROUP BY or window functions. The SQL cheat sheet has the syntax on one page.

SQL CASE Statement: CASE WHEN Explained (With Examples)