SQL CASE

Every value computed so far has come straight from a column, a function, or a fixed literal. CASE is SQL's conditional expression, essentially if/else logic, usable anywhere a value is expected: SELECT, WHERE, ORDER BY, even inside an aggregate function.

Learning Objectives

• Write a searched CASE expression with multiple WHEN conditions.
• Use CASE inside SELECT to compute a labeled value per row.
• Recognize the simple CASE form as a shorthand for equality checks.

Searched CASE

Conditions are checked top to bottom, the first WHEN that's true wins, the rest are never even evaluated. ELSE is what's returned when none of the WHEN conditions match, and is optional, omitting it returns NULL for unmatched rows instead.

SELECT first_name, salary,
  CASE
    WHEN salary >= 95000 THEN 'High'
    WHEN salary >= 85000 THEN 'Mid'
    ELSE 'Entry'
  END AS salary_band
FROM employees;

Simple CASE: Shorthand for Equality

The simple form, CASE column WHEN value THEN ..., only tests equality against one column, shorter to write, but strictly less flexible. The searched form, CASE WHEN condition THEN ..., can test anything in each WHEN, ranges, multiple columns, functions, which is why most of this lesson uses it.

-- Simple CASE
SELECT name,
  CASE name
    WHEN 'Engineering' THEN 'Tech'
    WHEN 'Research' THEN 'Tech'
    ELSE 'Non-Tech'
  END AS category
FROM departments;

-- Searched CASE, same result
SELECT name,
  CASE
    WHEN name = 'Engineering' THEN 'Tech'
    WHEN name = 'Research' THEN 'Tech'
    ELSE 'Non-Tech'
  END AS category
FROM departments;

CASE Inside an Aggregate

A genuinely common pattern: CASE turns a condition into a 1-or-0 per row, then SUM() adds those up, counting how many rows satisfy a condition without needing a separate query or a GROUP BY. This technique has a name, conditional aggregation, worth recognizing when it shows up in someone else's query.

SELECT
  SUM(CASE WHEN salary >= 95000 THEN 1 ELSE 0 END) AS high_earners,
  COUNT(*) AS total_employees
FROM employees;

Try It Yourself

Write a query labeling each employee as 'Senior' if hired before 2020, otherwise 'Recent'.

SELECT first_name, hire_date,
  CASE
    WHEN hire_date < '2020-01-01' THEN 'Senior'
    ELSE 'Recent'
  END AS tenure
FROM employees;

Frequently Asked Questions

Common Beginner Mistakes

Best Practices

• Use the searched form (CASE WHEN condition) by default, it handles everything the simple form does and more.
• Always include ELSE unless NULL for unmatched rows is genuinely the intended behavior.
• Order WHEN conditions deliberately, since only the first match applies.

Interview Questions

Summary

CASE is SQL's conditional expression, usable anywhere a value is expected. The searched form (CASE WHEN condition) handles any condition; the simple form (CASE column WHEN value) is a shorthand limited to equality on one column. Combined with SUM(), it enables conditional aggregation.

What's Next?

NULL functions next, COALESCE, IFNULL, and ISNULL, for substituting a fallback value whenever a column turns out to be NULL.