Lesson 37 of 61

5 mins read

SQL CASE: Conditional Expressions & Data Transposition

In Plain English

The `CASE` expression is SQL's built-in `if-then-else` decision structure. It evaluates a sequence of conditional clauses and returns the result of the first matching `WHEN` branch. It can be used anywhere expressions are valid (SELECT, WHERE, ORDER BY, GROUP BY).

The Mental Model

"`CASE` is like an automated grading machine: input a numerical score (95), test against tier thresholds (>= 90: "A", >= 80: "B"), and output a categorical letter grade."

Deep Dive: How It Works

Searched CASE: `CASE WHEN condition1 THEN val1 WHEN condition2 THEN val2 ELSE default END`. Highly flexible, supports complex boolean logic.

Simple CASE: `CASE status WHEN 'P' THEN 'Pending' WHEN 'C' THEN 'Completed' ELSE 'Unknown' END`. Matches equality against a single target variable.

Pivot / Transposition Pattern: Combining `SUM(CASE WHEN month = 'Jan' THEN sales ELSE 0 END)` turns row data into columnar spreadsheet layouts.

Syntax Blueprint

SELECT
  name,
  score,
  CASE
    WHEN score >= 90 THEN 'Excellent'
    WHEN score >= 75 THEN 'Good'
    ELSE 'Needs Improvement'
  END AS performance_rating
FROM students;

CASE evaluates conditions in top-down order and returns the first matching branch.

Core Rules to Remember

Always Include ELSE: Without ELSE, unhandled conditions evaluate to NULL by default.

Usable in ORDER BY: Custom sort order: `ORDER BY CASE WHEN priority = 'High' THEN 1 WHEN priority = 'Med' THEN 2 ELSE 3 END`.

Expression Anywhere: Valid in SELECT projections, WHERE filters, GROUP BY buckets, and UPDATE statements.

Common Beginner Traps & How to Fix Them

Mistake:Forgetting the `END` keyword at the conclusion of a CASE block.

Why it happens: Syntax error: every CASE statement must terminate with `END`.

How to fix: Pair every `CASE` with a closing `END`.

Pro Tip: Use `CASE` inside `GROUP BY` to dynamically bucket continuous metrics into age or price brackets (e.g. Under 18, 18-35, 36+).

Live Interactive Example

Hit Run Code to see it live

Categorization and Custom Sorting with CASE Expressions

Python 3.12

Output Console

Click "Run Code" to view the rendered output.

How it works: CASE classified revenue tiers in SELECT and enforced custom priority hierarchy in ORDER BY.

Your Turn: Micro Challenge

No pressure! Edit the starter code below and test your solution with instant feedback.

Micro Exercise

Categorize Numbers with CASE

Select `val`, `CASE WHEN val >= 50 THEN 'Pass' ELSE 'Fail' END AS status` from `scores`.

Sandbox Output

Click "Run & Check" to test your solution.

Finished reading and practicing?

Mark this lesson as completed to update your course progress.