# CASE

> Express conditional values with CASE and compare conditional aggregation with FILTER.

---

LLMS index: [llms.txt](/en/llms.txt)

---

`CASE` is SQL's conditional expression — the closest thing to an `if/else`. It has two forms. The *searched* `CASE` (`CASE WHEN cond THEN ... END`) evaluates arbitrary boolean conditions. The *simple* `CASE` (`CASE expr WHEN value THEN ... END`) compares an expression to specific values. `CASE` can be used anywhere an expression is allowed — in `SELECT`, `WHERE`, `ORDER BY`, `GROUP BY`, and in expressions inside aggregates.

## What you'll learn {#learn}

- Searched `CASE` for general conditions
- Simple `CASE` for value equality
- `ELSE` and what happens without it
- Using `CASE` to bucket rows for grouping
- The role of `CASE` in conditional aggregation (and when `FILTER` replaces it)

## Example {#example}

```sql {num="42.1" caption="CASE"}
-- Searched CASE (most flexible)
SELECT id, name,
  CASE
    WHEN id <= 2  THEN 'low'
    WHEN id <= 4  THEN 'mid'
    ELSE              'high'
  END AS tier
FROM fruits
ORDER BY id
LIMIT 5;

-- Simple CASE (equality)
SELECT name,
  CASE name
    WHEN 'apricot' THEN 'A'
    WHEN 'blueberry' THEN 'B'
    ELSE '?'
  END AS code
FROM fruits
ORDER BY id
LIMIT 4;

-- CASE in WHERE
SELECT id, name
FROM fruits
WHERE CASE WHEN id < 5 THEN id > 1 ELSE false END
ORDER BY id;

-- CASE for bucketing and counting per bucket
SELECT
  CASE
    WHEN id <= 2 THEN 'low'
    WHEN id <= 4 THEN 'mid'
    ELSE              'high'
  END AS tier,
  count(*) AS cnt
FROM fruits
GROUP BY tier
ORDER BY tier;

-- Conditional aggregation: CASE inside an aggregate vs. FILTER (cleaner)
SELECT
  sum(CASE WHEN total > 30 THEN total ELSE 0 END) AS big_sum_old_style,
  sum(total) FILTER (WHERE total > 30)            AS big_sum_filter
FROM orders_example;
```

The *searched* form is the workhorse. PostgreSQL evaluates conditions top-to-bottom and returns the result of the first true one. If no condition matches and there is no `ELSE`, the result is `NULL` — sometimes useful, sometimes a bug.

The *simple* form is shorthand for equality: `CASE name WHEN 'a' THEN ... END` is the same as `CASE WHEN name = 'a' THEN ... END`. It is concise when you have many discrete values to map.

Conditional aggregation — counting or summing only matching rows — used to require `sum(CASE WHEN cond THEN val ELSE 0 END)`. `sum(val) FILTER (WHERE cond)` expresses the condition directly. It can return NULL where `sum(CASE WHEN cond THEN val ELSE 0 END)` returns 0 (when input rows exist but none match). Use `COALESCE` if zero is the intended empty-result value.

## Run the example {#run}

To run:

```text
$ psql -f source/case.sql postgres
 id |    name    | tier
----+------------+------
  1 | apricot    | low
  2 | blueberry  | low
  3 | chestnut   | mid
  4 | damson     | mid
  5 | elderberry | high
(5 rows)
 ...
```

## Common pitfalls {#pitfalls}

- All `THEN`/`ELSE` branches must agree on a common type — `CASE WHEN ... THEN 1 ELSE 'x' END` is an error. Cast explicitly when mixing types: `CASE WHEN ... THEN 1::text ELSE 'x' END`.
- Forgetting `END` is the most common syntax error.
- Without `ELSE`, the result is `NULL` when no branch matches. If that is surprising, add an explicit `ELSE ...`.
- `CASE` in `WHERE` works but is usually unnecessary — most conditions are clearer as plain boolean expressions.

> [!TIP] Tip
> `CASE` and `COALESCE` overlap but are not the same. `COALESCE(a, b, c)` is shorter for "first non-null"; `CASE` is the right choice when conditions are not just "is null."

> [!NOTE] Try it
> Use `CASE` to map `id` to a label like 'small' / 'medium' / 'large' and count per label. Use `CASE` in `ORDER BY` to sort priorities in a custom order: `ORDER BY CASE priority WHEN 'high' THEN 1 WHEN 'mid' THEN 2 ELSE 3 END`. Then rewrite a `sum(CASE WHEN ...)` as `sum(...) FILTER (WHERE ...)`.

## Downloads {#downloads}

SQL file: [case.sql](/examples/source/case.sql) · [All lesson files](/examples/examples.zip) · [Setup guide](/en/#prerequisites)