SQL CASE Statement Interview Questions

Q1: Simple CASE vs searched CASE

A simple CASE compares one expression against a list of exact values. A searched CASE evaluates a full boolean condition per branch — which is strictly more powerful, since a simple CASE can be rewritten as a searched CASE, but not the other way around.

-- Simple CASE: exact value matches only
CASE status
  WHEN 'a' THEN 'Active'
  WHEN 'c' THEN 'Cancelled'
  ELSE 'Unknown'
END

-- Searched CASE: any boolean condition per branch
CASE
  WHEN total > 1000 THEN 'High value'
  WHEN total > 100 THEN 'Mid value'
  ELSE 'Low value'
END

Ranges, comparisons across multiple columns, and compound conditions all require a searched CASE — simple CASE only ever tests for equality against one expression.

Q2: What happens with no ELSE and no match?

Query:

SELECT id,
  CASE WHEN status = 'active' THEN 'Yes' END AS is_active
FROM customers;
-- one row has status = 'inactive'

No error, no empty string — an unmatched row with no ELSE branch simply evaluates to NULL. This is a common silent bug source in reports: a row that should be flagged "No" instead shows up as NULL, which behaves differently in later filters and aggregates (see NULL Handling in SQL).

Q3: Pivot rows into columns with CASE

Question: given an orders table with a status column, produce one row per customer with separate columns for pending and completed order counts.

SELECT customer_id,
  COUNT(CASE WHEN status = 'pending' THEN 1 END) AS pending_count,
  COUNT(CASE WHEN status = 'completed' THEN 1 END) AS completed_count
FROM orders
GROUP BY customer_id;

This works because COUNT() ignores NULL. Each CASE returns 1 only for its own status and implicitly NULL for every other row (see Q2), so each COUNT() only counts the rows matching its own condition — the pattern is a full pivot without a dedicated PIVOT keyword. For more aggregation patterns, see Advanced GROUP BY Techniques You Should Know, or build one interactively with the CASE Statement Builder.

Q4: CASE inside ORDER BY

Question: sort orders so that 'urgent' status always appears first, regardless of alphabetical order.

SELECT id, status
FROM orders
ORDER BY
  CASE WHEN status = 'urgent' THEN 0 ELSE 1 END,
  id;

CASE computes a custom sort key per row — every 'urgent' row gets a sort key of 0 and sorts before everything else (sort key 1), with id as the tiebreaker within each group. This pattern generalizes to any custom, business-defined priority order that doesn't match alphabetical or numeric order.

Q5: CASE inside WHERE

CASE can appear in WHERE too, though it's less common — usually because the same logic can be expressed more directly with AND/OR. It's most useful in WHERE when the condition genuinely depends on a value computed per row that would otherwise need to be repeated across several OR branches:

SELECT * FROM orders
WHERE
  CASE
    WHEN region = 'US' THEN total > 100
    ELSE total > 50
  END;

Q6: CASE vs COALESCE

COALESCE(a, b, c) returns the first non-NULL value among its arguments — it's really shorthand for a specific, common CASE pattern: check each value for NULL in order, return the first one that isn't.

-- These two are equivalent
COALESCE(phone, backup_phone, 'N/A')

CASE
  WHEN phone IS NOT NULL THEN phone
  WHEN backup_phone IS NOT NULL THEN backup_phone
  ELSE 'N/A'
END

Use COALESCE when the logic really is just "first non-NULL value" — it's shorter and clearer intent. Reach for CASE the moment the condition is anything other than a NULL check.