What is the difference between simple and searched CASE?

Simple CASE compares one expression against a list of values (CASE status WHEN 'paid' THEN ...). Searched CASE evaluates a full boolean condition in each branch (CASE WHEN total > 500 THEN ...), so it can test ranges and multiple columns. Searched CASE is more flexible and used more often.

Can I use CASE in a WHERE clause?

Yes, because CASE returns a value you can compare. However, it is usually clearer to write the boolean logic directly with AND/OR in WHERE. CASE in WHERE is most useful for conditional filtering that depends on another column.

What does CASE return if no condition matches?

If no WHEN matches and there is no ELSE branch, CASE returns NULL. Adding an ELSE gives you a defined fallback value and avoids unexpected NULLs in your results.

Beginner

SQL CASE Statement: Conditional Logic Explained

The if/else of SQL. Learn searched vs simple CASE, and how to use it inside SELECT, ORDER BY, and aggregates to reshape results without touching application code.

CASE is the closest thing SQL has to an if/else statement. It lets you return different values based on conditions — labelling rows, bucketing numbers into tiers, or turning a column of statuses into separate counts. It works almost anywhere an expression is allowed, which makes it one of the most reusable tools in the language. Keep the SQL cheat sheet handy for the syntax while you read.

What is CASE?

A CASE expression evaluates conditions in order and returns the value from the first one that is true. If nothing matches and there is no ELSE, it returns NULL. There are two forms: searched CASE (the flexible one) and simple CASE (a shorthand for equality checks).

-- The two shapes of CASE
CASE WHEN condition THEN result ... ELSE fallback END   -- searched
CASE column WHEN value THEN result ... ELSE fallback END -- simple

Searched CASE

Searched CASE evaluates a full boolean condition in each WHEN. This is the form you will reach for most often because each branch can test anything — ranges, multiple columns, or compound logic.

-- Tier each order by its total
SELECT
  id,
  total,
  CASE
    WHEN total >= 500 THEN 'High'
    WHEN total >= 100 THEN 'Medium'
    ELSE 'Low'
  END AS order_tier
FROM orders;

Conditions are checked top to bottom, so order matters. Put the most specific or highest thresholds first — once a WHEN matches, the rest are skipped.

How CASE evaluates — Branches are tested in order. The first one that is true returns its value and evaluation stops — later branches and ELSE are never reached.

Simple CASE

Simple CASE compares one expression against a list of values. It is cleaner than searched CASE when you are only checking equality against a single column.

-- Map status codes to friendly labels
SELECT
  id,
  CASE status
    WHEN 'paid'     THEN 'Paid'
    WHEN 'pending'  THEN 'Awaiting payment'
    WHEN 'refunded' THEN 'Refunded'
    ELSE 'Unknown'
  END AS status_label
FROM orders;

CASE Inside Aggregates

This is where CASE earns its keep. Wrapping CASE inside SUM or COUNT lets you produce several conditional totals in a single pass — the classic "pivot" pattern that turns rows into columns.

-- Count orders per status, side by side
SELECT
  COUNT(*) FILTER (WHERE status = 'paid')    AS paid,
  COUNT(*) FILTER (WHERE status = 'pending') AS pending,
  SUM(CASE WHEN status = 'refunded' THEN total ELSE 0 END) AS refunded_value
FROM orders;

PostgreSQL also offers FILTER (WHERE ...) as a cleaner alternative to COUNT(CASE WHEN ... THEN 1 END). Both work; FILTER reads better and is PostgreSQL-specific.

CASE in ORDER BY

Because CASE returns a value, you can sort by it — useful for custom orderings that do not follow alphabetical or numeric rules.

-- Force a custom status order
SELECT id, status
FROM orders
ORDER BY
  CASE status
    WHEN 'pending'  THEN 1
    WHEN 'paid'     THEN 2
    WHEN 'refunded' THEN 3
    ELSE 4
  END;

Common Mistakes & Tips

Forgetting ELSE — unmatched rows silently become NULL
Ordering WHEN branches wrong — a broad condition first can shadow narrower ones
Using simple CASE to test NULL — it never matches; use searched CASE with IS NULL
Mixing return types (text in one branch, number in another) causes a type error
Repeating a complex CASE in many columns — compute it once in a CTE or subquery

Next, learn how SQL treats missing data — it trips up almost every CASE and JOIN at some point. Read NULL handling in SQL, then practice these patterns on live tables.