CASE WHEN

Intermediate

⏱️ 12 mins read

What You'll Learn

CASE WHEN adds conditional logic inside SQL — like if/else statements. It can be used in SELECT, WHERE, ORDER BY, and GROUP BY. Two forms: simple CASE (compares one value) and searched CASE (evaluates conditions). Always end with END.

Syntax

CASE
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  ELSE default_result
END

Example

-- Categorize employees by salary
SELECT name, salary,
  CASE
    WHEN salary >= 100000 THEN 'Senior'
    WHEN salary >= 60000  THEN 'Mid-Level'
    ELSE 'Junior'
  END AS level
FROM employees;

-- Pivot-style: count by status in one row
SELECT
  SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) AS completed,
  SUM(CASE WHEN status = 'pending'   THEN 1 ELSE 0 END) AS pending,
  SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled
FROM orders;

-- Custom sort order
SELECT * FROM tasks
ORDER BY CASE priority
  WHEN 'High'   THEN 1
  WHEN 'Medium' THEN 2
  ELSE 3
END;

Common Mistakes

Forgetting the END keyword. Also: CASE evaluates conditions top-to-bottom and returns on the first TRUE match — order matters. If ELSE is omitted and no condition matches, CASE returns NULL.

Interview Tips

The SUM(CASE WHEN...) pattern is essential for pivoting data — turning rows into columns. Interviewers at analytics companies ask this frequently. Know both the simple and searched CASE syntax.

Practice

Classify orders as 'Small' (<$50), 'Medium' ($50-$200), or 'Large' (>$200). Count orders per category and show the total revenue per category.

Back to Full Roadmap