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 the ELSE value, or NULL when there is no ELSE.

Practice

Practice CASE WHEN Practice encoding a business rule Practice CASE buckets

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 or COUNT 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 the SUM(CASE ...) form there.

Lesson

Calculated Columns in SQL

Create derived values in a SELECT list.

Cheatsheet

SQL CASE Expressions Cheatsheet

Searched and simple CASE, plus conditional aggregation.

Interview

SQL CTE Interview Questions

Practice WITH clauses and step-by-step reports.

Mistake

Why My Percentage Comes Out 0

Avoid truncating fractional results.