Understanding and Using CASE Expressions in SQL

The article begins with a humorous preface before introducing the CASE expression, a conditional construct in SQL that works like a programming "if‑else" statement and has been part of the SQL‑92 standard.

Two syntaxes are described: the simple CASE that compares a column to literal values, and the searched CASE that evaluates arbitrary Boolean expressions. Both require a WHEN clause, an optional ELSE clause, and a mandatory END.

Basic syntax examples are provided: -- Simple CASE CASE column WHEN THEN WHEN THEN ... ELSE END -- Searched CASE CASE WHEN THEN WHEN THEN ... ELSE END

Practical scenarios demonstrate the power of CASE:

Row‑to‑column pivot : Transform three credit‑type rows per user into a single row with separate columns using CASE inside aggregate functions, or using self‑joins for better performance on large datasets.

Statistical conversion : Map province names to geographic regions (East, West, South, North, Central) with CASE, then aggregate population per region.

Level categorisation : Classify summed province populations into four levels using nested CASE expressions.

Conditional SELECT : Compute male and female population totals per province with CASE inside SUM.

Conditional UPDATE : Apply salary adjustments in a single UPDATE statement by nesting CASE to avoid overlapping conditions.

CHECK constraints : Show how to enforce a rule (female salary ≤ 50000) using CASE inside a CHECK, noting that MySQL does not enforce CHECK constraints.

Each scenario includes complete SQL code blocks wrapped in tags, preserving the original formatting for clarity.

The article concludes with key take‑aways: CASE expressions are fundamental for declarative SQL programming, can appear in SELECT, GROUP BY, WHERE, ORDER BY, and must have consistent return types, an ELSE clause, and a closing END.

References: "SQL Fundamentals" and "SQL Advanced Tutorial".