SQL guide · Querying

SQL CASE: If-Then-Else Logic Inside a Query

The CASE expression adds conditional logic to SQL. It evaluates a list of conditions in order and returns the value that belongs to the first one that is true, much like if / else if / else in a programming language. Because it is an expression rather than a statement, it can appear almost anywhere a value is allowed: in the select list, in WHERE, in ORDER BY, in GROUP BY and inside aggregate functions.

There are two forms. The searched form, CASE WHEN condition THEN result ..., accepts any boolean condition and is the most flexible. The simple form, CASE expression WHEN value THEN result ..., compares one expression to several values and is shorter when you only test equality. Both forms end with END, and an optional ELSE supplies the default. If no condition matches and there is no ELSE, the result is NULL.

Typical uses include bucketing numbers into labels such as small, medium and large, translating codes into readable text, and pivoting data by wrapping CASE inside SUM or COUNT to build conditional totals. All result branches should return compatible data types, because the database picks a single result type for the whole expression. Conditions are checked from top to bottom and evaluation stops at the first match, so order the branches from most specific to most general.

Basic syntax

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

-- simple CASE
CASE expression
  WHEN value1 THEN result1
  WHEN value2 THEN result2
  ELSE default_result
END

Each WHEN branch is tested in order and the first true one wins. ELSE is optional but recommended, otherwise unmatched rows produce NULL. Give the whole expression an alias with AS so the column has a useful name.

SQL CASE examples

Label values with a searched CASE

This groups each order into a size label based on its total. The conditions are tested from the top, so an order of 600 is labelled large and never reaches the later branches.

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

Translate codes with a simple CASE

When you only compare one column to fixed values, the simple form is shorter. It turns a status code into readable text for a report.

SELECT order_id,
       CASE status
         WHEN 'P' THEN 'Pending'
         WHEN 'S' THEN 'Shipped'
         WHEN 'D' THEN 'Delivered'
         ELSE 'Unknown'
       END AS status_label
FROM orders;

Conditional aggregation

Placing CASE inside an aggregate lets you count or sum only rows that meet a condition. This produces one row per customer with separate counts for shipped and pending orders.

SELECT customer_id,
       SUM(CASE WHEN status = 'S' THEN 1 ELSE 0 END) AS shipped_orders,
       SUM(CASE WHEN status = 'P' THEN 1 ELSE 0 END) AS pending_orders
FROM orders
GROUP BY customer_id;

Custom sort order with CASE

A CASE in ORDER BY defines an order that does not match alphabetical order. Here urgent tickets come first, then normal, then low.

SELECT ticket_id, priority
FROM tickets
ORDER BY CASE priority
           WHEN 'urgent' THEN 1
           WHEN 'normal' THEN 2
           ELSE 3
         END;

Where SQL dialects differ

PostgreSQL: Follows the standard CASE syntax. All result branches must resolve to a common type, otherwise PostgreSQL raises an error rather than converting silently. For conditional counts, the FILTER (WHERE ...) clause on an aggregate is a shorter alternative to wrapping CASE inside it.

MySQL: Standard CASE syntax is supported. MySQL also offers the IF(condition, a, b) function as a shorthand for a two-way test. Be careful with mixed string and number branches, since implicit conversion can change comparison results.

SQL Server: Supports both forms of CASE, plus IIF(condition, a, b) from SQL Server 2012. The result type follows data type precedence, which can turn a string branch into a number and cause a conversion error.

SQLite: Supports both forms of CASE with the standard syntax. SQLite is loosely typed, so branches of different types do not raise errors, though comparisons still follow type affinity rules.

Oracle: Supports searched and simple CASE expressions in SQL since Oracle 9i. Older code often uses DECODE(expr, search, result, ..., default), which is Oracle-specific and treats two nulls as equal.

Common mistakes

Omitting ELSE and then being surprised by NULL values for rows that match no condition.
Putting a broad condition before a narrow one. Because the first true branch wins, WHEN score >= 50 placed first hides WHEN score >= 90.
Using the simple form to test for NULL. CASE col WHEN NULL never matches, because it compares with =; use CASE WHEN col IS NULL instead.
Mixing result types, such as a number in one branch and text in another, which causes conversion errors or unexpected casting.
Forgetting the closing END, which gives a syntax error that often points at the next clause rather than the real problem.

Related SQL guides

Browse all SQL guides

Need help writing the SQL?

Describe your tables and the result you want, then review the generated query against your database before running it.

Try the AI SQL generator