Functions ⑥ — CASE in Practice — ORDER BY / UPDATE / NULL

Data Used in This Article — staff and customer

The CASE learned so far is a general-purpose expression you can place not only in the SELECT column list but also inside `ORDER BY` and in an `UPDATE` SET clause. This article walks through those two applications and the pitfall of handling NULL with CASE in order.

The subject is the staff table (10 staff) in the first half and the customer table (8 customers / contains NULL) in the second. The first half sorts city into an arbitrary order; the second half uses a CASE that displays NULL email as "Not registered", and finally runs an UPDATE CASE that rewrites country values into region categories.

Before the exercises, let's confirm the column definitions and a data sample of the two tables used here — staff and customer.

① Use PRAGMA table_info(staff); and PRAGMA table_info(customer); to check both tables' column definitions.

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

Using CASE in ORDER BY — Assigning an Arbitrary Order

The right side of ORDER BY can hold not only a column name but also a CASE expression. With this you can assign an arbitrary order that string alphabetical order or numeric size cannot produce. Use it for business priorities like "Tokyo → Osaka → Kyoto → others", status orders like "Open → In progress → Done", or a rank ordering.

Write it as ORDER BY CASE WHEN cond THEN number ... ELSE number END — CASE returns a number for sorting and you sort by that number. ASC / DESC can be attached just like a column. Rows with the same number are ordered by the next ORDER BY column (comma-separated), so combining sort key + secondary key also determines the order within a category.

-- 1) Sort city in the order 'Osaka → Kyoto → Tokyo → others'
SELECT name, city, salary FROM staff
ORDER BY
  CASE city
    WHEN 'Osaka' THEN 1
    WHEN 'Kyoto' THEN 2
    WHEN 'Tokyo' THEN 3
    ELSE 9
  END,
  salary DESC;  -- within the same city, highest salary first

-- 2) ORDER BY CASE + DESC can also reverse the order
--   if CASE assigns 1, 2, 3, then DESC orders them 3, 2, 1

Imagine the requirement "display staff in order of office size (Tokyo → Osaka → Kyoto → others)". (The explanation appears once you run it correctly.)

① From the staff table, take the 2 columns name, city.

② With ORDER BY CASE, assign "Tokyo is 1, Osaka is 2, Kyoto is 3, everything else is 9" and sort by that number. Make it a multi-level sort where rows within the same city are ordered by name ascending.

The CASE + NULL Pitfall — Use `IS NULL`, Not `= NULL`

Just like in WHERE, `= NULL` / `<> NULL` cannot be used inside CASE either. A comparison with NULL always results in NULL (unknown), and when a CASE WHEN clause returns NULL that branch is not taken, so the result differs from what you intended.

To test for NULL, use `column IS NULL` / `column IS NOT NULL`. This is the same rule as the WHERE NULL check learned earlier, and it is common to everywhere a condition can be written — CASE / IIF / WHERE / ON. The simple form (CASE column WHEN ... THEN) cannot test for NULL, so when you need to handle NULL always use the searched form (CASE WHEN column IS NULL THEN ...).

-- Wrong: WHEN NULL cannot pick out NULL rows ('Unknown' is never shown)
SELECT name, age,
  CASE age
    WHEN NULL THEN 'Unknown'
    ELSE age
  END AS age_display
FROM customer;

-- Right: searched form + IS NULL
SELECT name, age,
  CASE
    WHEN age IS NULL THEN 'Unknown'
    ELSE age
  END AS age_display
FROM customer;

-- Note: COALESCE(age, 'Unknown') gives the same result
--   for a simple 2-value NULL replacement, COALESCE is shorter

Imagine the requirement "on a customer list screen, show NULL in the email column as 'Not registered' instead of a blank".

① From the customer table, take name and email.

② Using the searched CASE form, add a 3rd column aliased email_display that is 'Not registered' for rows where email is NULL, and the original email value otherwise.

Using CASE in UPDATE — Rewrite Values Based on a Condition

So far CASE has generated a new column on the read side, but writing CASE in an UPDATE SET clause lets you rewrite the actual column values in the table based on a condition.

Write it as UPDATE table SET column = CASE WHEN cond THEN value1 WHEN cond THEN value2 ... ELSE column END. Writing ELSE column to say "rows matching no WHEN keep their original value" is the safe way; forget it and ELSE is treated as NULL, overwriting the non-target rows with NULL.

Because UPDATE is a destructive operation that changes the actual table, this article handles it in the final exercise. Before running it, the standard production practice is to try the same condition with SELECT name, column, CASE ..., visually verify the result, and rewrite it as UPDATE only if it looks right.

-- First verify with SELECT (intending to make NULL age 0)
SELECT name, age,
  CASE
    WHEN age IS NULL THEN 0
    ELSE age
  END AS new_age
FROM customer;

-- If it looks right, rewrite as UPDATE
UPDATE customer SET age = CASE
  WHEN age IS NULL THEN 0
  ELSE age
END;

Imagine the requirement "bulk-convert the customer table's country column from country names to region categories (Asia / Western / Europe / Unknown)". This is the article's final exercise, so you perform a destructive operation that changes the actual table.

① Bulk UPDATE the customer table's country column with CASE. The mapping is:

- Japan → Asia

- US or UK → Western

- Italy → Europe

- NULL → Unknown

- Anything else → keep the original value (so re-running gives the same result)

② After the UPDATE, run SELECT id, name, country FROM customer ORDER BY id; and confirm each customer's country has changed to a region name.