Functions ⑤ — CASE for Multi-Condition Branching

Data Used in This Article — the staff Table

The IIF covered in the previous article had only one condition, so it could only do a two-way true/false branch. This article's CASE expression evaluates three or more conditions in order and can return a different value for each. It is the basic syntax for writing "if-elseif-else" inside SQL, and it is the most common branching construct in real work — report categorization, region grouping, grade ranking, and so on.

The subject is the same staff table (10 rows) as before. Through exercises that build region classification on the city column, pay-band classification on salary, and generation classification on birthday, you'll try the two CASE forms and their application to compound conditions in order.

Before the exercises, let's confirm the column definitions and a data sample of the staff table.

① Use PRAGMA table_info(staff); to check column names, types, and the primary key.

② Use SELECT * FROM staff LIMIT 5; to preview the first 5 rows.

CASE Has Two Ways to Write It

The CASE expression has two forms — the simple form and the searched form — and both can produce the same result. Two rules are common to both.

• The final `END` is required — forgetting it is a syntax error.
• `ELSE` is optional — if omitted, rows matching no WHEN become NULL. To avoid NULL, state a default explicitly with ELSE.

Form ① Simple Form — Equality Comparison on a Column

The simple form is written CASE column WHEN value1 THEN result1 WHEN value2 THEN result2 ... ELSE default END. Right after CASE you place one column to compare, and to the right of WHEN you write a value to test the column for equality against. The test is equality (`=`) only, and you can write only one value to the right of WHEN — multiple values like WHEN ('Tokyo', 'Osaka') are not allowed. It suits a simple column = value substitution and is shorter to write.

-- Simple form — replace city with a region name
SELECT name, city,
  CASE city
    WHEN 'Tokyo' THEN 'Kanto'
    WHEN 'Yokohama' THEN 'Kanto'
    WHEN 'Osaka' THEN 'Kansai'
    WHEN 'Kyoto' THEN 'Kansai'
    ELSE 'Other'
  END AS region
FROM staff;

Imagine the requirement "display the city column as a short code". (The explanation appears once you run it correctly.)

① From the staff table, take name and city.

② Using the simple CASE form, add a 3rd column aliased city_code that is 'TOK' if city is Tokyo, 'OSA' if Osaka, 'KYO' if Kyoto, 'YOK' if Yokohama, 'SAP' if Sapporo, and the original city value if none match.

Form ② Searched Form — Write Any Condition Freely

The searched form is written CASE WHEN expr1 THEN result1 WHEN expr2 THEN result2 ... ELSE default END. Right after CASE you write nothing, and to the right of each WHEN you put the condition itself. Comparison operators like salary >= 6000000, LIKE such as name LIKE '%Tanaka', IN, and AND / OR compound conditions — anything you can write in WHERE works here. WHEN is evaluated top to bottom, and the value is decided by the first branch that becomes TRUE. Because it is so expressive, this is the form mainly used in practice.

-- Searched form — split salary into 2 bands
SELECT name, salary,
  CASE
    WHEN salary >= 5000000 THEN 'High'
    ELSE 'Standard'
  END AS pay_band
FROM staff;

Imagine the requirement "classify staff into 3 tiers (Junior / Mid / Senior) by salary".

① From the staff table, take name and salary.

② Using the searched CASE form, add a 3rd column aliased tier that is 'Senior' if salary is 6,000,000 or more, 'Mid' if 4,500,000 or more but under 6,000,000, and 'Junior' if under that.

Combining with Functions and Compound Conditions for More Complex Branching

A CASE WHEN clause can hold expressions combining comparison operators, LIKE, IN, AND / OR, and function calls. You can pull the birth year with strftime('%Y', birthday) learned in Functions ① date functions for generation classification; since name is in "given surname" order, name LIKE '%Tanaka' (suffix match) classifies people whose surname is Tanaka; and you can write conditions combining multiple columns with AND.

The example below pulls the birth year in 10-year units and labels people born in the 1990s as 'Gen Z' and the 1980s as 'Gen X-Y'. Because strftime returns a string, the comparisons are done against string literals ('1990' / '1999').

-- 1) Label birth year in 5-year buckets
SELECT name, birthday,
  CASE
    WHEN strftime('%Y', birthday) BETWEEN '1985' AND '1989' THEN 'Late 1980s'
    WHEN strftime('%Y', birthday) BETWEEN '1990' AND '1994' THEN 'Early 1990s'
    ELSE 'Other'
  END AS cohort
FROM staff;

-- 2) Compound condition: high salary AND based in Tokyo
SELECT name, city, salary,
  CASE
    WHEN salary >= 6000000 AND city = 'Tokyo' THEN 'Tokyo senior'
    WHEN salary >= 6000000 THEN 'Senior'
    ELSE 'Other'
  END AS tag
FROM staff;

Imagine the requirement "split all staff into generations by birth year in 10-year units".

① From the staff table, take name and birthday.

② Using the searched CASE form, add a 3rd column aliased generation that is 'Gen Z' if the birth year pulled from birthday is in the 1990s, 'Gen X-Y' if in the 1980s, and 'Other' otherwise.

Imagine the requirement "show the TOP 5 by salary on a ranking screen, each annotated with a pay-band label".

① From the staff table, take name and salary.

② Using the searched CASE form, add a 3rd column aliased tier with the same pay-band labels as Practice 2 ('Senior' if salary is 6,000,000 or more, 'Mid' if 4,500,000 or more, 'Junior' otherwise).

③ Sort by `salary` descending and limit to the first 5 rows.