Conditional logic: CASE WHEN

Everything we have seen so far extracts data as-is. Often, though, you want to transform it: classify salaries into tiers, mark new hires as "junior", translate a numeric code into a readable label. SQL's standard tool for this is called CASE WHEN.

The syntax: a mini if/else

CASE
  WHEN condition_1 THEN value_1
  WHEN condition_2 THEN value_2
  ...
  ELSE default_value
END

It reads exactly like an if/else if/else in any language: SQL evaluates the conditions in order and stops at the first true one, returning the corresponding value. If none is true, it returns the ELSE value. If you omit the ELSE and no branch matches, it returns NULL.

-- Classify employees by salary tier:
SELECT first_name,
       last_name,
       salary,
       CASE
         WHEN salary >= 50000 THEN 'Senior'
         WHEN salary >= 35000 THEN 'Mid'
         ELSE                       'Junior'
       END AS tier
FROM   employees;

The CASE lives inside the SELECT: it produces a column just like any function, and it is perfectly normal to give it an alias with AS (here tier).

Where to use CASE

Three recurring places:

1. In the SELECT to create derived columns (the example above).
2. In ORDER BY for "custom" sorting:
   SQL

   Copia

   ORDER BY CASE department_id WHEN 1 THEN 0 ELSE 1 END, last_name;
   -- Engineering first, then the rest, alphabetically by last name.

3. Inside aggregate functions to "count conditionally":
   SQL

   Copia

   SELECT
     COUNT(*) AS total,
     SUM(CASE WHEN salary >= 50000 THEN 1 ELSE 0 END) AS senior,
     SUM(CASE WHEN salary <  35000 THEN 1 ELSE 0 END) AS junior
   FROM employees;

   This pattern — SUM(CASE WHEN … THEN 1 ELSE 0 END) — is the most common "conditional count" used in dashboards.

The "compact" form

There is also a shorter variant when you compare a single expression against exact values:

CASE department_id
  WHEN 1 THEN 'Ingegneria'
  WHEN 2 THEN 'Vendite'
  ELSE       'Altro'
END

It is equivalent to CASE WHEN department_id = 1 THEN ... WHEN department_id = 2 THEN ... END, just more compact. It only works for exact equality: if you need >= or another comparison, you have to go back to the extended form.

Try it

Review exercise