SQL CASE Statement Explained — Syntax, Real-World Patterns and Pitfalls

-- ============================================================
-- SETUP: a small orders table we'll use throughout this file
-- ============================================================
CREATE TABLE orders (
    order_id     INT PRIMARY KEY,
    customer_id  INT,
    total_amount DECIMAL(10, 2),
    status_code  CHAR(1)   -- 'P' = Pending, 'S' = Shipped, 'D' = Delivered, 'C' = Cancelled
);

INSERT INTO orders VALUES
    (1, 101, 25.00,  'P'),
    (2, 102, 340.50, 'S'),
    (3, 103, 12.99,  'D'),
    (4, 104, 980.00, 'D'),
    (5, 105, 55.75,  'C'),
    (6, 106, 150.00, 'S'),
    (7, 107, 3200.00,'D');

-- ============================================================
-- SIMPLE CASE: one column, checked against fixed values
-- Perfect for translating status codes into readable labels
-- ============================================================
SELECT
    order_id,
    total_amount,
    CASE status_code              -- name the column ONCE here
        WHEN 'P' THEN 'Pending'
        WHEN 'S' THEN 'Shipped'
        WHEN 'D' THEN 'Delivered'
        WHEN 'C' THEN 'Cancelled'
        ELSE 'Unknown'            -- always include ELSE — see Gotchas section
    END AS order_status           -- alias the result column
FROM orders;

-- ============================================================
-- SEARCHED CASE: a fresh boolean per WHEN — handles ranges
-- Bucketing order value into a customer tier
-- ============================================================
SELECT
    order_id,
    total_amount,
    CASE
        WHEN total_amount < 50.00              THEN 'Small'
        WHEN total_amount BETWEEN 50.00 AND 499.99 THEN 'Medium'  -- BETWEEN is inclusive on both ends
        WHEN total_amount >= 500.00            THEN 'Large'
        ELSE 'Uncategorised'                   -- covers NULLs and any gap in logic
    END AS order_tier
FROM orders
ORDER BY total_amount;

-- ============================================================
-- GOAL: produce a one-row summary dashboard from the orders
-- table without running multiple separate queries
-- ============================================================
SELECT
    -- Total number of orders regardless of status
    COUNT(*)                                              AS total_orders,

    -- Count only delivered orders (NULLs from ELSE are ignored by COUNT)
    COUNT(CASE WHEN status_code = 'D' THEN 1 END)         AS delivered_count,

    -- Count only pending or shipped (in-flight) orders
    COUNT(CASE WHEN status_code IN ('P', 'S') THEN 1 END) AS in_flight_count,

    -- Sum revenue from delivered orders only
    SUM(CASE WHEN status_code = 'D' THEN total_amount ELSE 0 END)  AS delivered_revenue,

    -- Average order value for Large-tier orders only
    AVG(CASE WHEN total_amount >= 500.00 THEN total_amount END)    AS avg_large_order_value

FROM orders;

-- ============================================================
-- BONUS: break it down per customer tier using GROUP BY
-- Shows how CASE + GROUP BY replaces multiple UNION queries
-- ============================================================
SELECT
    CASE
        WHEN total_amount < 50.00              THEN 'Small'
        WHEN total_amount BETWEEN 50.00 AND 499.99 THEN 'Medium'
        WHEN total_amount >= 500.00            THEN 'Large'
    END                          AS order_tier,
    COUNT(*)                     AS order_count,
    ROUND(SUM(total_amount), 2)  AS tier_revenue,
    ROUND(AVG(total_amount), 2)  AS avg_order_value
FROM orders
GROUP BY
    -- You must repeat the CASE expression in GROUP BY in standard SQL
    -- (some databases like PostgreSQL allow referencing the alias, most don't)
    CASE
        WHEN total_amount < 50.00              THEN 'Small'
        WHEN total_amount BETWEEN 50.00 AND 499.99 THEN 'Medium'
        WHEN total_amount >= 500.00            THEN 'Large'
    END
ORDER BY tier_revenue DESC;

-- ============================================================
-- SCENARIO: support queue — show orders in business priority:
--   1st: Pending  (needs action NOW)
--   2nd: Shipped  (in transit, watch for issues)
--   3rd: Delivered (resolved, low urgency)
--   4th: Cancelled (archive, lowest priority)
-- ============================================================
SELECT
    order_id,
    customer_id,
    total_amount,
    status_code,
    -- Translate the code to a label in SELECT for the UI
    CASE status_code
        WHEN 'P' THEN 'Pending'
        WHEN 'S' THEN 'Shipped'
        WHEN 'D' THEN 'Delivered'
        WHEN 'C' THEN 'Cancelled'
        ELSE 'Unknown'
    END AS order_status
FROM orders
ORDER BY
    -- This CASE is ONLY for sorting — it never appears in the result set
    CASE status_code
        WHEN 'P' THEN 1   -- highest priority
        WHEN 'S' THEN 2
        WHEN 'D' THEN 3
        WHEN 'C' THEN 4   -- lowest priority
        ELSE 5            -- unknown statuses sink to the bottom
    END ASC,
    total_amount DESC;    -- within the same status, show largest orders first

-- ============================================================
-- Add a row with a NULL status to expose the silent-NULL bug
-- ============================================================
INSERT INTO orders VALUES (8, 108, 75.00, NULL);

-- ============================================================
-- BAD: no ELSE, no NULL check — order 8 returns NULL silently
-- This NULL will corrupt any SUM or COUNT that uses this column
-- ============================================================
SELECT
    order_id,
    CASE status_code
        WHEN 'P' THEN 'Pending'
        WHEN 'S' THEN 'Shipped'
        WHEN 'D' THEN 'Delivered'
        WHEN 'C' THEN 'Cancelled'
        -- NO ELSE — order 8 returns NULL here, no warning, no error
    END AS order_status_bad
FROM orders
WHERE order_id = 8;
-- Returns: NULL  ← silent, dangerous

-- ============================================================
-- GOOD: explicit NULL check first, then ELSE as a safety net
-- ============================================================
SELECT
    order_id,
    CASE
        WHEN status_code IS NULL THEN 'Status Not Set'  -- catch NULLs explicitly FIRST
        WHEN status_code = 'P'   THEN 'Pending'
        WHEN status_code = 'S'   THEN 'Shipped'
        WHEN status_code = 'D'   THEN 'Delivered'
        WHEN status_code = 'C'   THEN 'Cancelled'
        ELSE 'Unknown Status: ' || status_code  -- catches future status codes you haven't handled yet
    END AS order_status_safe
FROM orders;

-- ============================================================
-- PROOF: missing ELSE corrupts SUM — compare these two results
-- ============================================================
SELECT
    -- SUM treats NULL as 0, so this silently drops order 8's amount
    SUM(CASE WHEN status_code IN ('P','S','D','C') THEN total_amount END)  AS revenue_missing_else,
    -- ELSE 0 makes intent explicit and produces the correct total
    SUM(CASE WHEN status_code IN ('P','S','D','C') THEN total_amount ELSE 0 END) AS revenue_with_else
FROM orders;