Conditional Logic Case When Then Else

Why this matters

Data Analysts constantly translate raw values into business-friendly categories: customer segments, risk flags, revenue bands, and quality checks. SQL CASE expressions let you do this directly in your queries without extra tools. You will use CASE to:

• Bucket numbers into tiers (e.g., order size bands).
• Create flags and labels for dashboards (e.g., churn risk, premium vs. standard).
• Fix or standardize messy data (e.g., replace NULLs or inconsistent codes).
• Compute KPIs conditionally (e.g., count only active users, sum revenue for certain regions).

Who this is for

• Aspiring or junior Data Analysts learning SQL for reporting and dashboards.
• Anyone who needs readable, robust logic inside SELECT, WHERE, GROUP BY, and ORDER BY.

Prerequisites

• Basic SQL SELECT, FROM, WHERE.
• Comfort with comparison operators (=, <, >), AND/OR, and NULL behavior.

Concept explained simply

CASE is like IF ➜ THEN logic inside SQL.

-- Searched CASE (most common)
CASE
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  ELSE default_result
END

-- Simple CASE (compares a single expression to many values)
CASE expression
  WHEN value1 THEN result1
  WHEN value2 THEN result2
  ELSE default_result
END

Mental model

• Top to bottom: SQL checks WHEN lines in order and stops at the first match.
• No match? ELSE runs. If no ELSE, result is NULL.
• NULL doesn’t equal anything. Use IS NULL to catch NULLs.
• Use searched CASE for flexible boolean conditions; use simple CASE for neat value-to-label mapping.

Syntax cheat sheet

-- Labeling in SELECT
SELECT
  order_id,
  amount,
  CASE
    WHEN amount IS NULL THEN 'Unknown'
    WHEN amount < 50 THEN 'Small'
    WHEN amount < 200 THEN 'Medium'
    ELSE 'Large'
  END AS size_label
FROM orders;

-- Conditional aggregation
SELECT
  SUM(CASE WHEN status = 'shipped' THEN 1 ELSE 0 END) AS shipped_cnt,
  SUM(CASE WHEN status = 'processing' THEN 1 ELSE 0 END) AS processing_cnt
FROM orders;

-- Using CASE in ORDER BY
SELECT *
FROM orders
ORDER BY CASE WHEN priority = 'high' THEN 1 WHEN priority = 'medium' THEN 2 ELSE 3 END;

Worked examples

-- Goal: Small (<50), Medium (50–199), Large (≥200), Unknown for NULL
SELECT
  order_id,
  amount,
  CASE
    WHEN amount IS NULL THEN 'Unknown'
    WHEN amount < 50 THEN 'Small'
    WHEN amount >= 50 AND amount < 200 THEN 'Medium'
    ELSE 'Large'
  END AS size_label
FROM orders;

SELECT
  customer_id,
  city,
  shipping_state,
  COALESCE(
    CASE
      WHEN city = 'New York' THEN 'NY'
      WHEN city IN ('San Francisco','Los Angeles') THEN 'CA'
      ELSE shipping_state
    END,
    'Unknown'
  ) AS shipping_state_filled
FROM customers;

-- Simple CASE: map status values
CASE status
  WHEN 'shipped' THEN 'Done'
  WHEN 'processing' THEN 'Working'
  WHEN 'cancelled' THEN 'Stopped'
  ELSE 'Other'
END AS status_label,

-- Searched CASE: multi-condition risk logic
CASE
  WHEN amount > 500 AND customer_tier = 'New' THEN 'High risk'
  WHEN amount > 500 THEN 'Elevated'
  ELSE 'Normal'
END AS risk_flag

Practical projects

• Customer segmentation: Build tiers by lifetime spend and recency using CASE and aggregate functions.
• Order health dashboard: Create status labels and risk flags directly in a view with CASE.
• Data cleaning view: Standardize country/state codes and fill missing fields using CASE + COALESCE.

Exercises

Practice on small, clear datasets. Then generalize to your real tables.

Sample rows:
1, 12
2, 55
3, 240
4, NULL
5, 1000

order_id | amount | size_label
1        | 12     | Small
2        | 55     | Medium
3        | 240    | Large
4        | NULL   | Unknown
5        | 1000   | Large

Sample rows:
1, New York, NULL
2, San Francisco, NULL
3, Austin, TX
4, Los Angeles, NULL
5, Miami, NULL

Sample rows:
1, shipped, 75, Returning
2, processing, 650, New
3, cancelled, 40, New
4, shipped, 520, New
5, processing, 300, VIP

Checklist before you run

• Did you order WHEN conditions from most specific to least specific?
• Did you handle NULL explicitly (or via ELSE)?
• Are your label spellings consistent (no typos that break grouping)?
• Did you alias the CASE result with a clear name?

Common mistakes and self-check

• Mistake: Using = NULL.
  • Fix: Use IS NULL or IS NOT NULL.
• Mistake: Overlapping ranges (e.g., <= and < that cover the same numbers).
  • Fix: Make ranges mutually exclusive and ordered.
• Mistake: Forgetting ELSE (unexpected NULLs later).
  • Fix: Add ELSE 'Unknown' or ELSE 0 where appropriate.
• Mistake: Simple CASE for complex conditions.
  • Fix: Use searched CASE when conditions need AND/OR.

Self-check: Run a quick frequency count on your labels to see if any NULL or unexpected values appear.

SELECT label, COUNT(*)
FROM (
  SELECT CASE WHEN ... END AS label FROM your_table
) t
GROUP BY label
ORDER BY COUNT(*) DESC;

Learning path

1. Write 3 labels with searched CASE (include an ELSE).
2. Switch to simple CASE for one column with fixed values.
3. Add CASE to an aggregate to count/sum conditionally.
4. Use CASE in ORDER BY to create a custom sort.
5. Refactor: ensure no overlapping conditions; add NULL handling.

Mini challenge

Create a view that adds two fields to a transactions table:

• amount_band: < 20 ➜ 'Micro', 20–99 ➜ 'Small', 100–499 ➜ 'Mid', ≥ 500 ➜ 'Large', NULL ➜ 'Unknown'
• refund_risk: amount ≥ 200 AND payment_method = 'gift_card' ➜ 'High'; amount ≥ 200 ➜ 'Elevated'; else 'Normal'

Next steps

• Revisit your last report and replace spreadsheet logic with CASE in SQL.
• Pair CASE with aggregates to produce labeled KPIs.
• Proceed to the quick test to validate your understanding.