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

- URL: https://betteranalyst.com/sql/case

SQL guide · Querying

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

- [SQL SELECT](https://betteranalyst.com/sql/select)
- [SQL WHERE](https://betteranalyst.com/sql/where)
- [SQL ORDER BY](https://betteranalyst.com/sql/order-by)
- [SQL GROUP BY](https://betteranalyst.com/sql/group-by)
- [SQL IS NULL](https://betteranalyst.com/sql/is-null)
- [SQL AS (alias)](https://betteranalyst.com/sql/as)

[Browse all SQL guides](https://betteranalyst.com/sql)

## 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](https://betteranalyst.com/ai-sql-query-generator)