DocsAdvancedCASE Expressions

CASE Expressions

Advanced

Concept

CASE is SQL's conditional expression, similar to if-else in programming languages. It evaluates conditions in order and returns the value associated with the first true condition. If no condition matches and there is no ELSE, it returns NULL. CASE can be used in SELECT, WHERE, ORDER BY, GROUP BY, and even inside aggregate functions.

There are two forms: the searched CASE (CASE WHEN condition THEN result) which evaluates arbitrary Boolean expressions, and the simple CASE (CASE expression WHEN value THEN result) which compares a single expression against multiple values. The searched form is more flexible and more commonly used.

CASE is extremely useful for data transformation, categorization, and conditional aggregation. You can create labeled buckets (price tiers, age groups), pivot data with conditional SUM, customize sort orders, and handle NULL values — all without leaving SQL.

Syntax

-- Searched CASE
SELECT
  name,
  price,
  CASE
    WHEN price < 10 THEN 'Budget'
    WHEN price < 50 THEN 'Mid-Range'
    WHEN price < 100 THEN 'Premium'
    ELSE 'Luxury'
  END AS price_tier
FROM products;

-- Simple CASE
SELECT
  order_id,
  status,
  CASE status
    WHEN 'pending'   THEN 'Awaiting Processing'
    WHEN 'shipped'   THEN 'In Transit'
    WHEN 'delivered'  THEN 'Completed'
    ELSE 'Unknown'
  END AS status_label
FROM orders;

Practical Example

Using the ecommerce schema (customers, orders, order_items, products)

-- Conditional aggregation: revenue by price tier
SELECT
  CASE
    WHEN p.price < 25  THEN 'Under $25'
    WHEN p.price < 100 THEN '$25-$99'
    ELSE '$100+'
  END AS price_tier,
  COUNT(DISTINCT p.product_id)       AS products,
  SUM(oi.quantity)                   AS units_sold,
  SUM(oi.unit_price * oi.quantity)   AS revenue
FROM products p
JOIN order_items oi ON oi.product_id = p.product_id
GROUP BY
  CASE
    WHEN p.price < 25  THEN 'Under $25'
    WHEN p.price < 100 THEN '$25-$99'
    ELSE '$100+'
  END
ORDER BY revenue DESC;

-- Pivot-style report using CASE inside SUM
SELECT
  DATE_TRUNC('month', order_date) AS month,
  SUM(CASE WHEN status = 'delivered' THEN total_amount ELSE 0 END) AS delivered_rev,
  SUM(CASE WHEN status = 'pending'   THEN total_amount ELSE 0 END) AS pending_rev,
  SUM(CASE WHEN status = 'cancelled' THEN total_amount ELSE 0 END) AS cancelled_rev
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

Common Pitfalls & Tips

1CASE evaluates conditions top-to-bottom and returns the first match. Order your WHEN clauses from most specific to least specific.
2Forgetting the ELSE clause causes unmatched rows to return NULL, which can break downstream calculations. Always include ELSE unless you intentionally want NULL.
3When using CASE in GROUP BY, you must repeat the entire CASE expression — you cannot reference the SELECT alias.

Practice CASE expressions