CASE WHEN in SQL

CASE WHEN is SQL's if/else. It checks conditions in order and returns the result for the first one that is true, or the ELSE value if none are. You can use a CASE WHEN expression anywhere SQL expects a value: in SELECT to make a new column, in ORDER BY for a custom sort, inside SUM or COUNT to count only some rows, and in UPDATE to change rows in different ways.

SQL CASE WHEN syntax

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

• Each WHEN has a condition, such as score >= 90, and a THEN result.
• Conditions are checked from the top. The first true one wins, and the rest are skipped.
• ELSE is optional. If no condition is true and there is no ELSE, the result is NULL.
• Every CASE must finish with END. Add AS name after it to name the new column.

This works the same in PostgreSQL, MySQL, SQL Server, SQLite and Oracle, because CASE is part of standard SQL. PostgreSQL's documentation describes it as a generic conditional expression, similar to if/else in other languages.

The simple form

When every condition tests the same column for equality, there is a shorter form. Put the column after CASE, and just the values after each WHEN:

CASE status
  WHEN 'shipped' THEN 'On its way'
  WHEN 'pending' THEN 'Being packed'
  ELSE 'Check with support'
END

The simple form only tests equals. For ranges, such as score >= 90, or anything else, use the full form, often called a searched CASE.

Example 1: a new column in SELECT

Take a table called scores with a name and a test score for each student. Turn each score into a grade:

SELECT name,
       score,
       CASE
         WHEN score IS NULL THEN 'Absent'
         WHEN score >= 90 THEN 'A'
         WHEN score >= 70 THEN 'B'
         WHEN score >= 50 THEN 'C'
         ELSE 'Fail'
       END AS grade
FROM scores;

Two things to notice. Ben's 74 is also more than 50, but he gets a B, because score >= 70 comes first and CASE stops there. So put the strictest condition at the top. And Eli's row needs its own WHEN: without it, NULL >= 90 is neither true nor false, so Eli would fall through every condition to ELSE and be marked as a fail.

A CASE expression sits inside an ordinary SELECT, so it helps to be fluent with those first. Try writing one, then press Mark to check it in the course.

Example 2: counting with CASE WHEN

Put CASE inside SUM to count only the rows that match, all in one query. This is often called conditional aggregation. With an orders table of six orders:

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

Each CASE turns a row into 1 if it matches and 0 if not, and SUM adds them up. The last column adds the amounts of shipped orders only: 20 + 15 + 40 = 75. Add GROUP BY customer_id and you get these counts for every customer, side by side.

Example 3: a custom sort with ORDER BY

Alphabetical order puts cancelled first, then pending, then shipped. To show pending orders first, sort by a number you choose for each status:

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

Example 4: changing rows with UPDATE

CASE can set different new values in a single UPDATE. Here books get 10% off and everything else keeps its price, so a $12 book becomes $10.80:

UPDATE products
SET price = CASE
              WHEN category = 'books' THEN price * 0.9
              ELSE price
            END;

CASE WHEN in WHERE

CASE works in WHERE too, but it is rarely needed: plain AND and OR usually say the same thing more clearly. One real use is guarding a calculation. PostgreSQL's documentation shows WHERE CASE WHEN x <> 0 THEN y/x > 1.5 ELSE false END, which skips the division when x is zero.

Shortcuts: IF and IIF

For a single either-or choice, some databases have a shorter function. MySQL has IF(condition, value_if_true, value_if_false). SQL Server has IIF(condition, value_if_true, value_if_false), which Microsoft describes as a shorthand way of writing a CASE expression. Neither is standard SQL, so CASE is the one to learn: it works everywhere and handles more than two outcomes.

Common mistakes

• Forgetting END. Every CASE needs one, and the error message is often unhelpful.
• Putting the conditions in the wrong order. The first true WHEN wins, so score >= 50 above score >= 90 would give everyone over 50 a C.
• Leaving out ELSE by accident. Rows that match nothing become NULL.
• Testing NULL with equals. WHEN score = NULL is never true. Use WHEN score IS NULL, which also means NULL can't be tested in the simple form.
• Mixing result types. All the THEN and ELSE results should be the same type: PostgreSQL requires them to be convertible to a single type, so don't return a number from one branch and text from another.

New to SQL? Start with what SQL is, then work through the Databases course, which covers SELECT, WHERE, sorting, joins and how apps talk to a database.

Read next

Computer Science · DatabasesWhat is SQL? What it is used for, with simple examplesSQL is the language for asking databases questions. Learn what SQL is, what it is used for, the main commands and how queries work, with examples.