CASE

Syntax

How it works

CASE is SQL's if/then/else. It is an expression, not a function, so it has no parentheses of its own and can appear anywhere a value is allowed: in SELECT, WHERE, ORDER BY, GROUP BY, HAVING, UPDATE and inside other functions. It walks its branches from top to bottom and returns the result of the first one that matches.

There are two forms. The searched form, CASE WHEN condition THEN result ... END, evaluates a full boolean test in each branch and is the more flexible one. The simple form, CASE expression WHEN value THEN result ... END, compares one expression for equality against a list of values, which reads cleanly when you are mapping a single column to labels.

If no branch matches and there is no ELSE, CASE returns NULL. That NULL behaviour is what makes CASE the engine behind conditional aggregation: because COUNT() and SUM() ignore NULLs, wrapping a condition in CASE lets you count or total only the rows you care about in a single pass. See COALESCE() when you specifically want to replace NULLs with a default.

Examples

Bucket values into labels (searched CASE in SELECT)

SELECT name, score,
       CASE
         WHEN score >= 90 THEN 'A'
         WHEN score >= 80 THEN 'B'
         WHEN score >= 70 THEN 'C'
         ELSE 'F'
       END AS grade
FROM students;

name  | score | grade
------+-------+------
 Ali  |    93 | A
 Sara |    81 | B
 Omar |    64 | F

Map a code to text (simple CASE)

SELECT id,
       CASE status
         WHEN 'P' THEN 'Paid'
         WHEN 'U' THEN 'Unpaid'
         WHEN 'R' THEN 'Refunded'
         ELSE 'Unknown'
       END AS status_label
FROM orders;

id | status_label
---+-------------
 1 | Paid
 2 | Unpaid
 3 | Unknown

Conditional aggregation (CASE inside COUNT and SUM)

-- Paid vs unpaid totals in one pass over the table
SELECT
  COUNT(CASE WHEN status = 'paid' THEN 1 END)        AS paid_orders,
  SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END) AS paid_revenue
FROM orders;

paid_orders | paid_revenue
------------+-------------
       1301 |       84250

Custom sort order (CASE in ORDER BY)

-- Force a business priority, not alphabetical
SELECT id, priority
FROM tickets
ORDER BY
  CASE priority
    WHEN 'high'   THEN 1
    WHEN 'medium' THEN 2
    WHEN 'low'    THEN 3
    ELSE 4
  END;

id | priority
---+---------
 7 | high
 3 | medium
 9 | low

Conditional UPDATE (CASE in SET)

UPDATE employees
SET salary = CASE
               WHEN rating = 'top'  THEN salary * 1.10
               WHEN rating = 'good' THEN salary * 1.05
               ELSE salary
             END;

-- top performers +10%, good performers +5%,
-- everyone else unchanged, in a single statement

Common mistakes

-- Missing END: syntax error
SELECT id,
       CASE WHEN amount > 100 THEN 'big'
            ELSE 'small'
FROM orders;

SELECT id,
       CASE WHEN amount > 100 THEN 'big'
            ELSE 'small'
       END AS size
FROM orders;

-- Simple CASE uses equality, and x = NULL is never true,
-- so this branch never matches. Result is always 'has value'.
SELECT CASE phone
         WHEN NULL THEN 'no phone'
         ELSE 'has value'
       END
FROM customers;

-- Use the searched form with IS NULL
SELECT CASE
         WHEN phone IS NULL THEN 'no phone'
         ELSE 'has value'
       END
FROM customers;

-- Mixed result types force a coercion. Here numbers and
-- text collide, which errors or returns text unexpectedly.
SELECT CASE WHEN in_stock THEN qty
            ELSE 'none'
       END
FROM products;

-- Keep all branches the same type
SELECT CASE WHEN in_stock THEN qty
            ELSE 0
       END
FROM products;

Performance

CASE is evaluated once per row, and its conditions are checked top to bottom until one matches. Put the cheapest and most likely conditions first so most rows short circuit early instead of falling through every branch.

Avoid putting a heavy correlated subquery inside a CASE branch, because it can run for every row and every branch that is reached. Where possible compute the value once (a JOIN or a CTE) and reference it, rather than repeating an expensive lookup in multiple branches.

A CASE in a WHERE or ORDER BY that wraps an indexed column usually makes the expression non sargable, so the index cannot be used. If sort or filter performance matters, consider a persisted computed column or restructuring the predicate so the raw column is exposed to the optimiser.

Interview questions

SELECT CASE WHEN x IS NULL THEN 'null' ELSE 'not null' END
FROM t;

SELECT
  SUM(CASE WHEN status = 'paid'  THEN 1 ELSE 0 END) AS paid,
  SUM(CASE WHEN status <> 'paid' THEN 1 ELSE 0 END) AS unpaid
FROM orders;

SELECT year,
  SUM(CASE WHEN quarter = 'Q1' THEN amount END) AS q1,
  SUM(CASE WHEN quarter = 'Q2' THEN amount END) AS q2,
  SUM(CASE WHEN quarter = 'Q3' THEN amount END) AS q3,
  SUM(CASE WHEN quarter = 'Q4' THEN amount END) AS q4
FROM sales
GROUP BY year;