SQL Case

Adding Conditional Logic

CASE lets a query make decisions row by row. It walks through a list of WHEN conditions in order, and as soon as one is true it returns the matching THEN value and stops checking the rest. If none of the conditions match, it returns the value given in the optional ELSE clause, or NULL when no ELSE is provided.

Because CASE is an expression, you can place it almost anywhere a value is allowed: in the SELECT list to create a derived column, in ORDER BY to control custom sorting, and even inside aggregate functions.

SELECT first_name,
       salary,
       CASE
         WHEN salary >= 80000 THEN 'High'
         WHEN salary >= 50000 THEN 'Medium'
         ELSE 'Entry'
       END AS salary_band
FROM employees;

The conditions are tested top to bottom, so order them from most specific to least. Someone earning 90000 matches the first WHEN and is labelled 'High' without the later conditions ever being checked.

Two Forms of CASE

SQL offers two ways to write a CASE expression. The searched form, shown above, allows a full condition after each WHEN. The simple form compares one expression against a series of values and is handy when you are only checking equality.

SELECT order_id,
       CASE status
         WHEN 'P' THEN 'Pending'
         WHEN 'S' THEN 'Shipped'
         WHEN 'D' THEN 'Delivered'
         ELSE 'Unknown'
       END AS status_label
FROM orders;

CASE with Aggregates

Placing CASE inside an aggregate function is a powerful pattern often called conditional aggregation. By returning 1 or 0 from CASE and summing the result, you can count rows that meet a condition within the same query that counts everything, producing several tallies at once.

SELECT department,
       SUM(CASE WHEN salary >= 80000 THEN 1 ELSE 0 END) AS high_earners,
       SUM(CASE WHEN salary < 50000 THEN 1 ELSE 0 END) AS entry_earners
FROM employees
GROUP BY department;

• Conditions are checked in order; the first true WHEN wins.
• Provide an ELSE to avoid unexpected NULL results.
• CASE can appear in SELECT, WHERE, ORDER BY, and aggregates.
• Every branch should return a compatible data type.