Functions ④ — IF / IIF for Single-Condition Branching

Data Used in This Article — the staff Table

We have covered functions in three articles so far; this one and the next two deal with switching what is displayed based on a condition. From the same column value, you can attach classification labels like "High" or "Normal" depending on whether a condition holds, or fold several values into a single result column.

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.

IF / IIF — Switch a Value on One Condition

The most basic function for switching the displayed value based on a condition is IF / IIF. The syntax is below, and the two are exactly the same in meaning.

- MySQL: IF(condition, value if true, value if false)

- This course's console / SQL Server: IIF(condition, value if true, value if false)

This course's console has no IF function, so the exercises use IIF. Unlike WHERE, it does not filter rows — it only changes the value of the result column, so you use it when you want to "keep every row but change the display label per column value".

-- 1) Quick check with literals
SELECT IIF(100 < 200, 'T', 'F');   -- 'T'
SELECT IIF(500 > 1000, 'T', 'F');  -- 'F'

-- 2) Using a column — 'HQ' if city is Tokyo, otherwise 'Branch'
SELECT name, city,
       IIF(city = 'Tokyo', 'HQ', 'Branch') AS office
FROM staff;

-- Reference: in MySQL, use IF()
--   SELECT IF(city = 'Tokyo', 'HQ', 'Branch') FROM staff;

Imagine a staff list for an executive meeting where you want to "label salaries of 5,000,000 or more as High and the rest as Normal". (The explanation appears once you run it correctly.)

① From the staff table, take name, salary, and `IIF(salary >= 5000000, 'High', 'Normal')` aliased as `rank` — 3 columns in total.

The Difference Between WHERE and IF — Removing Rows vs Changing Values

WHERE and IF both take a condition, but the effect on the result is completely different.

- `WHERE`: keeps only the rows where the condition is TRUE, and removes FALSE / NULL rows

- `IF` / `IIF`: keeps every row and only swaps the column value based on the condition

The two combine. A typical pattern is "show only staff based in Tokyo, and split each person's pay rank into High / Normal": first narrow rows with WHERE, then transform the display with IIF in the SELECT column list.

-- Combine WHERE and IIF
-- 1) Use WHERE to keep only staff based in Osaka
-- 2) Use IIF to classify salary of 6,000,000 or more as 'Senior'
SELECT name, salary,
       IIF(salary >= 6000000, 'Senior', 'General') AS grade
FROM staff
WHERE city = 'Osaka';

Imagine the requirement "list pay ranks for the Tokyo-based members".

① From the staff table, take name, salary, and `IIF(salary >= 5000000, 'High', 'Normal')` aliased as `rank` — 3 columns.

② Narrow to rows where `city` is `'Tokyo'`.

Combining with LIKE and AND, and Stacking ORDER BY / LIMIT

Because IIF's condition accepts the same expressions as WHERE, you can combine it with LIKE (pattern matching) or AND / OR (compound conditions). Branches like "label as Tanaka surname if the name contains Tanaka" or "ace if 30+ and based in Tokyo" fit on one line.

The AS alias on the result column can also be a target of `ORDER BY`, so sorting by the label column produced by IIF and then limiting rows with LIMIT is a basic pattern for report output in practice.

-- 1) IIF + LIKE: label 'Tanaka' if the surname is Tanaka
SELECT name,
       IIF(name LIKE '%Tanaka', 'Tanaka', 'Other') AS surname_check
FROM staff;

-- 2) IIF + AND: compound condition on age-equivalent + city
SELECT name, birthday, city,
       IIF(strftime('%Y', birthday) <= '1989' AND city = 'Osaka',
           'Osaka senior', 'Other') AS tag
FROM staff;

Imagine the requirement "attach a special label only to Tanaka-surname staff based in Tokyo". You'll try writing a compound condition like `name LIKE '%Tanaka'` AND `city = 'Tokyo'` in a single IIF.

① From the staff table, take name, city, and `IIF(name LIKE '%Tanaka' AND city = 'Tokyo', 'Tokyo Tanaka', 'Other')` aliased as `tag` — 3 columns.

Imagine the requirement "show the top 3 by salary while also annotating each person with a rank label".

① From the staff table, take name, salary, and `IIF(salary >= 5000000, 'High', 'Normal')` aliased as `rank` — 3 columns.

② Sort by `salary` descending and limit to the first 3 rows.