Reference

SQL CASE Expressions

Return different values depending on a condition. It is SQL's if/else.

A CASE expression evaluates conditions top to bottom and returns the value for the first one that is true. If none match, it returns theELSE value, or NULL when there is noELSE.

Searched CASE

Each branch has its own condition. This is the most flexible form.

Bucket a number

select product_name,
       case
         when quantity_on_hand = 0 then 'Out of stock'
         when quantity_on_hand < 10 then 'Low'
         else 'Healthy'
       end as stock_status
from   products

Simple CASE

Compares one expression against several values. It is shorter when checking equality.

Map values

select order_id,
       case status
         when 'shipped'   then 'Done'
         when 'cancelled' then 'Closed'
         else 'In progress'
       end as stage
from   orders

CASE inside aggregates

A common trick: put CASE inside SUM orCOUNT to count rows that match a condition, sometimes called conditional aggregation or a "pivot".

Conditional counts

select count(*)                                          as total,
       sum(case when status = 'shipped' then 1 else 0 end) as shipped,
       count(*) filter (where status = 'shipped')          as shipped_v2
from   orders

Dialect note

The FILTER (WHERE ...) clause shown above is a cleaner alternative to CASE inside an aggregate. It works in DuckDB, Postgres, and SQLite. SQL Server and MySQL do not support it. Use theSUM(CASE ...) form there.

Lesson

Calculated Columns in SQL

Create derived values in a SELECT list.

Lesson

SQL CASE WHEN: Labeling Rows

Add if/else logic to query results.

Lesson

SQL COALESCE: Filling Gaps

Replace NULLs with useful defaults.

Lesson

Encoding Business Rules in SQL

Translate plain-language rules into CASE logic.