Home/Learn/SQL/CASE Expressions

CASE Expressions

Intermediate

DML & Querying

CASE is SQL's conditional expression; simple CASE compares one value to multiple options, while searched CASE evaluates independent boolean conditions for flexible branching.

Overview

CASE is an expression (returns a value), not a statement — it can appear anywhere an expression is valid: SELECT, WHERE, ORDER BY, GROUP BY, and HAVING. Simple CASE (CASE expr WHEN val1 THEN result1) is similar to a Java switch. Searched CASE (CASE WHEN condition1 THEN result1) is more flexible, allowing any boolean predicate including ranges and function calls. CASE evaluates conditions in order and short-circuits at the first TRUE condition. The return type is determined by the first THEN branch; mixing incompatible types causes a type error. An implicit ELSE NULL applies when no branch matches and no ELSE is specified.

Simple vs Searched CASE

Use simple CASE when comparing one expression to discrete values. Use searched CASE for range checks, compound conditions, or when the conditions involve different columns.

SQL — simple CASE, searched CASE, and CASE in GROUP BY

-- Simple CASE: map status code to display label
SELECT
    id,
    total_amount,
    CASE status
        WHEN 'pending'   THEN 'Awaiting Payment'
        WHEN 'paid'      THEN 'Processing'
        WHEN 'shipped'   THEN 'On the Way'
        WHEN 'delivered' THEN 'Delivered'
        ELSE 'Unknown'
    END AS status_label
FROM orders;

-- Searched CASE: range-based salary banding
SELECT
    id,
    full_name,
    salary,
    CASE
        WHEN salary < 40000                  THEN 'Junior'
        WHEN salary BETWEEN 40000 AND 80000  THEN 'Mid-level'
        WHEN salary BETWEEN 80001 AND 120000 THEN 'Senior'
        ELSE 'Principal'
    END AS salary_band
FROM employees;

-- CASE in GROUP BY for custom bucketing
SELECT
    CASE
        WHEN total_amount < 500   THEN 'small'
        WHEN total_amount < 2000  THEN 'medium'
        ELSE 'large'
    END         AS order_size,
    COUNT(*)    AS order_count,
    SUM(total_amount) AS revenue
FROM orders
GROUP BY 1;

CASE in ORDER BY and Conditional Aggregation

CASE in ORDER BY enables custom sort sequences beyond ASC/DESC. CASE inside aggregate functions (SUM, COUNT) implements conditional aggregation — the pivot pattern — without self-joins.

SQL — CASE in ORDER BY and conditional aggregation (pivot)

-- CASE in ORDER BY: custom status priority sort
SELECT id, status, created_at
FROM orders
ORDER BY
    CASE status
        WHEN 'pending'   THEN 1
        WHEN 'paid'      THEN 2
        WHEN 'shipped'   THEN 3
        WHEN 'delivered' THEN 4
        ELSE 5
    END ASC,
    created_at ASC;

-- Conditional aggregation (pivot pattern): monthly revenue by category
SELECT
    DATE_TRUNC('month', created_at)::DATE            AS month,
    SUM(CASE WHEN category = 'electronics' THEN total_amount ELSE 0 END) AS electronics,
    SUM(CASE WHEN category = 'clothing'    THEN total_amount ELSE 0 END) AS clothing,
    SUM(CASE WHEN category = 'books'       THEN total_amount ELSE 0 END) AS books
FROM orders o
JOIN products p ON p.id = o.product_id
GROUP BY 1
ORDER BY 1;

Key Points to Remember

1CASE is an expression, not a statement — it can appear anywhere an expression is valid.
2Simple CASE compares one value to constants; searched CASE evaluates arbitrary conditions.
3CASE short-circuits: it stops evaluating at the first TRUE branch.
4CASE inside SUM/COUNT implements conditional aggregation (the SQL pivot pattern).
5CASE in ORDER BY enables custom sort sequences beyond simple ASC/DESC.
6Without ELSE, unmatched CASE returns NULL — always add an explicit ELSE.

Interview Questions

What is the difference between simple CASE and searched CASE?

EasyAmazon

How would you pivot rows into columns using SQL without a dedicated PIVOT function?

HardMicrosoft

How can CASE be used inside an aggregate function?

MediumAdobe

What happens when no CASE branch matches and there is no ELSE clause?

EasyGoogle

Ask Aria about CASE Expressions

Your personal AI tutor — ask anything about this concept

Revision Status

Personal Notes

Discussion

Loading discussion…

NULL Handling

String Functions