Conditional Functions

CASE WHEN as a Value-Producing Expression

Chapter 12 introduced CASE WHEN as a filtering-adjacent tool inside WHERE/ORDER BY. Here, treat it as a general function-like expression that produces a value per row — most commonly used to derive a new computed column.

sql
SELECT
  customer_name,
  amount,
  CASE
    WHEN amount IS NULL   THEN 'unknown'
    WHEN amount < 200      THEN 'low'
    WHEN amount < 350      THEN 'medium'
    ELSE                        'high'
  END AS price_tier
FROM orders;

Result:

CASE evaluates its WHEN clauses top to bottom and returns the value from the first one that matches; if none match, it returns the ELSE value (or NULL if there's no ELSE).

Simple CASE Form

There's also a shorter "simple" form for equality checks against one expression:

sql
SELECT status,
       CASE status
         WHEN 'completed' THEN 'Order fulfilled'
         WHEN 'pending'   THEN 'Awaiting fulfillment'
         WHEN 'cancelled' THEN 'Order cancelled'
         ELSE 'Unknown status'
       END AS status_description
FROM orders;

This is equivalent to a searched CASE WHEN status = 'completed' THEN ... chain, just terser when every branch tests the same column for equality.

IF() / IIF() — Dialect-Specific Two-Branch Shorthand

For a simple two-outcome decision, some dialects offer a shorter function:

sql
-- MySQL / SQLite
SELECT IF(amount > 300, 'expensive', 'affordable') AS bucket FROM orders;

-- SQL Server
SELECT IIF(amount > 300, 'expensive', 'affordable') AS bucket FROM orders;

IF(condition, value_if_true, value_if_false) (MySQL/SQLite) and IIF(condition, value_if_true, value_if_false) (SQL Server) are both shorthand for a two-branch CASE WHEN condition THEN value_if_true ELSE value_if_false END. Neither is standard SQL — PostgreSQL, for example, has no IF() expression function and requires the full CASE WHEN form.

Nested/Combined Use

sql
SELECT customer_name,
       CASE
         WHEN status = 'cancelled' THEN 0
         ELSE amount
       END AS billable_amount
FROM orders;

This zeroes out cancelled orders for a billing report while leaving other amounts as-is — a pattern often used just before an aggregate like SUM(billable_amount).

Edge Cases

• CASE with no matching WHEN and no ELSE returns NULL — a very common source of "why is this column NULL?" bugs when someone forgot the ELSE.
• All branches of a CASE expression should ideally return a compatible/comparable type; mixing, say, a number in one branch and text in another can cause an implicit-conversion surprise or an outright type error depending on the engine.
• IF()/IIF() only handle exactly two outcomes; for three or more branches, a full CASE WHEN is required (or nested IFs, which quickly become unreadable).

Key Takeaways / Interview Q&A

Q: How does CASE differ when used as a value expression vs. inside a WHERE clause (Chapter 12)? A: The mechanics are identical (evaluate WHEN clauses top-down, return the first match's value); the difference is purely where it's used — as a filtering condition vs. as a computed output column.

Q: What does CASE return if no WHEN matches and there's no ELSE? A: NULL.

Q: What's the difference between IF() and IIF()? A: They do the same thing (a two-branch conditional shorthand) but are named differently by dialect: IF() in MySQL/SQLite, IIF() in SQL Server. Neither exists in PostgreSQL, which requires CASE WHEN.

Q: When would you prefer full CASE WHEN over IF()/IIF()? A: Whenever you have more than two possible outcomes, or need portability across dialects that don't support IF()/IIF() (like PostgreSQL).