CASE

Objectives

By the end of this lesson, you should be able to:

• Write conditional logic inside a query with CASE WHEN ... THEN ... ELSE ... END
• Use CASE to label rows or bucket values into categories
• Use CASE inside an aggregate function for conditional counting

💡 Why this matters: “Label each employee’s salary as High, Medium, or Standard” isn’t a filter, every row stays in the result, it’s a value that depends on a condition. CASE is SQL’s if/else, usable directly inside a SELECT list.

⚠️ A note on verification: every statement and result in this lesson was run against a real, live PostgreSQL 18 database, using the employees table from earlier lessons.

Basic CASE

SELECT first_name, salary,
  CASE
    WHEN salary >= 90000 THEN 'High'
    WHEN salary >= 70000 THEN 'Medium'
    ELSE 'Standard'
  END AS salary_tier
FROM employees
ORDER BY first_name;

 first_name |  salary  | salary_tier
------------+----------+--------------
 Alexis     | 71000.00 | Medium
 Devon      | 58000.00 | Standard
 Erin       | 74000.00 | Medium
 Jordan     | 61000.00 | Standard
 Maya       | 67000.00 | Standard
 Priya      | 89000.00 | Medium
 Sam        | 63000.00 | Standard
 Taylor     | 95000.00 | High

CASE evaluates each WHEN condition in order, top to bottom, and returns the value after the first THEN whose condition is true. ELSE catches everything that didn’t match any WHEN, END closes the expression. Taylor (95000) matches the first condition and gets 'High', Erin (74000) fails the first condition but matches the second, getting 'Medium', Devon (58000) matches neither and falls through to 'Standard'. Order matters here: conditions are checked top to bottom, and the first match wins, even if a later condition would also technically be true.

CASE Inside an Aggregate

SELECT
  COUNT(CASE WHEN salary >= 80000 THEN 1 END) AS high_earners,
  COUNT(CASE WHEN salary < 80000 THEN 1 END) AS other_earners
FROM employees;

 high_earners | other_earners
---------------+----------------
             2 |              6

Combining CASE with COUNT() is a common pattern for conditional counting within a single row of output, rather than needing separate queries (or GROUP BY) for each condition. COUNT() counts non-NULL values (Module 8), and a CASE with no matching WHEN and no ELSE returns NULL by default, so only rows matching the condition get counted.

Try It

1. Write a query labeling each employee’s hire_date as 'Veteran' (hired before 2020) or 'Recent' (hired 2020 or later), using CASE.
2. Write a query using CASE to label each employee’s department as 'Has Department' or 'Unassigned' depending on whether department IS NULL.
3. Write a query using COUNT(CASE WHEN ...) to count how many employees are in Engineering versus every other department, in one row of output.
4. Explain, in your own words, what happens if two WHEN conditions in the same CASE expression could both be true for the same row.

Recap

• CASE WHEN condition THEN value ... ELSE default END evaluates conditions top to bottom, returning the first match.
• CASE can appear anywhere a value can, most commonly in a SELECT list, to label or bucket rows.
• Combining CASE with an aggregate function (like COUNT(CASE WHEN ... THEN 1 END)) enables conditional counting in a single query.

Next lesson: COALESCE and NULLIF, two more tools for handling NULL values gracefully.