CASE Expressions (Conditional Logic)

Reviewed & published by Brayan K

CASE is SQL's if/then/else. It lets a query make decisions — turning a raw score into a letter grade, a status code into a friendly label, or a number into a tier — all inside the query itself, without touching your application code.

Part of the free SQL course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.

You'll learn both the searched and simple forms, how to use CASE for custom sorting and conditional counting, and the rules that keep your branches predictable.

What You'll Learn

• Searched CASE for conditions and ranges
• Simple CASE for exact-value matching
• Why the first matching WHEN wins
• The ELSE catch-all (and NULL without it)
• CASE in ORDER BY for custom sorts
• CASE inside SUM for conditional counts

Our Sample Table: students

Four students and their exam scores — perfect raw material for turning numbers into grades and labels.

1. Searched CASE — Conditions & Ranges

The searched form puts a full boolean condition after each WHEN. SQL checks them top to bottom and returns the value of the first branch that's true. The optional ELSE is the fallback, and END closes the expression.

CASE is a series of "if / else if / else" gates. The value falls through each WHEN until one catches it — exactly like a sorting machine dropping a parcel into the first matching bin.

-- The three tables this lesson uses:
CREATE TABLE students (
    id    INTEGER PRIMARY KEY,
    name  TEXT,
    score INTEGER
);
INSERT INTO students VALUES
    (1, 'Maya',  94),
    (2, 'Leo',   81),
    (3, 'Priya', 72),
    (4, 'Sam',   58);

CREATE TABLE accounts (
    id     INTEGER PRIMARY KEY,
    name   TEXT,
    status TEXT
);
INSERT INTO accounts VALUES
    (1, 'Northwind Ltd', 'A'),
    (2, 'Acme Supplies', 'S'),
    (3, 'Vega Design',   'C'),
    (4, 'Orbit Labs',    'X');

CREATE TABLE tickets (
    id       INTEGER PRIMARY KEY,
    name     TEXT,
    priority TEXT
);
INSERT INTO tickets VALUES
    (1, 'Login broken',      'High'),
    (2, 'Typo on pricing',   'Low'),
    (3, 'Slow report',       'Medium'),
    (4, 'Feature request',   'Wishlist');

-- Searched CASE: each WHEN is a full condition
SELECT
    name,
    score,
    CASE
        WHEN score >= 90 THEN 'A'
        WHEN score >= 80 THEN 'B'
        WHEN score >= 70 THEN 'C'
        ELSE 'F'
    END AS grade
FROM students;

-- ✅ Expected result:
-- name | score | grade
-- Maya | 94 | A
-- Leo | 81 | B
-- Priya | 72 | C
-- Sam | 58 | F

2. Simple CASE — Exact Values

When you're matching one expression against a set of exact values, the simple form is tidier. You write the expression once after CASE, then list WHEN value THEN result. It can't do ranges — for those, use searched CASE.

-- Uses the students, accounts and tickets tables from the first example.
-- Simple CASE: compare ONE expression to fixed values
SELECT
    name,
    status,
    CASE status
        WHEN 'A' THEN 'Active'
        WHEN 'S' THEN 'Suspended'
        WHEN 'C' THEN 'Cancelled'
        ELSE 'Unknown'
    END AS status_label
FROM accounts;

-- Best when you're matching exact values, not ranges.

-- ✅ Expected result:
-- name | status | status_label
-- Northwind Ltd | A | Active
-- Acme Supplies | S | Suspended
-- Vega Design | C | Cancelled
-- Orbit Labs | X | Unknown

Your Turn: Pass / Fail

Fill in the keyword pair that introduces a branch and its result.

-- Uses the students, accounts and tickets tables from the first example.
-- 🎯 YOUR TURN — fill in the blanks, then "Try it Yourself"
-- Goal: label each student "Pass" (score >= 60) or "Fail".

SELECT
    name,
    score,
    CASE
        ___ score >= 60             -- 👉 replace ___ with WHEN
        ___ 'Pass'                  -- 👉 replace ___ with THEN
        ELSE 'Fail'
    END AS result
FROM students;

-- ✅ Expected: a 'result' column of Pass / Fail.

-- ✅ Expected result:
-- name | score | result
-- Maya | 94 | Pass
-- Leo | 81 | Pass
-- Priya | 72 | Pass
-- Sam | 58 | Fail

3. CASE Beyond SELECT

Because CASE is an expression, it works anywhere a value is expected. Two of the most useful spots: ORDER BY for a custom sort order, and inside SUM/COUNT for conditional totals — the classic "count how many passed" pattern.

-- Uses the students, accounts and tickets tables from the first example.
-- CASE inside ORDER BY = a custom sort order
SELECT name, priority
FROM tickets
ORDER BY
    CASE priority
        WHEN 'High'   THEN 1
        WHEN 'Medium' THEN 2
        WHEN 'Low'    THEN 3
        ELSE 4
    END;

-- ✅ Sorts High → Medium → Low, NOT alphabetically
-- (which would wrongly put High, Low, Medium).

-- ✅ Expected result:
-- name | priority
-- Login broken | High
-- Slow report | Medium
-- Typo on pricing | Low
-- Feature request | Wishlist

-- Uses the students, accounts and tickets tables from the first example.
-- CASE inside an aggregate = conditional counting
SELECT
    SUM(CASE WHEN score >= 70 THEN 1 ELSE 0 END) AS passed,
    SUM(CASE WHEN score <  70 THEN 1 ELSE 0 END) AS failed
FROM students;

-- ✅ A single-row summary:
-- passed | failed
--   3    |   1
-- This "pivot" pattern turns rows into labelled totals.

-- ✅ Expected result:
-- passed | failed
-- 3 | 1

🧩 Reorder Challenge

Unscramble these CASE lines so they label a "band": 'Hot' for score ≥ 80, 'Warm' for ≥ 50, otherwise 'Cold'.

-- Uses the students, accounts and tickets tables from the first example.
-- SCRAMBLED — arrange into a SELECT that adds a "band"
-- column: 'Hot' if score >= 80, 'Warm' if >= 50, else 'Cold'.

WHEN score >= 50 THEN 'Warm'
CASE WHEN score >= 80 THEN 'Hot'
END AS band
ELSE 'Cold'

Why: branch order is everything. The >= 80 test must come before >= 50, because a score of 90 also satisfies >= 50 — and the first true branch wins. ELSE catches everything below 50, and END closes the expression. Reversing the two WHENs would label every passing score 'Warm'.

🧠 Quick Recall

Predict each result, then reveal. Use the students table.

B — 82 fails the >= 90 branch but passes >= 80, which fires first and wins.

NULL — the missing ELSE means there's no fallback, so the expression yields NULL. Add an ELSE to avoid surprises.

C — 95 satisfies >= 70, and since that branch now comes first, it wins before >= 90 is ever reached. Order your conditions most-specific first.

Common Errors (and the fix)

• Wrong branch order: putting a loose condition (>= 70) before a tight one (>= 90) means the loose one always wins. List the most specific conditions first.
• Forgetting END: every CASE must close with END (and usually an alias). Omitting it is a syntax error.
• No ELSE, unexpected NULLs: without ELSE, unmatched rows become NULL. Add a default branch unless NULL is genuinely intended.
• Mixed return types: one branch returning a number and another a string can error or coerce unexpectedly. Keep all THEN/ELSE results the same type.

Mini-Challenge: Spend Tiers

Use a searched CASE to label customers Gold / Silver / Bronze by total_spent. Expected shape is in the comments.

-- Uses the students, accounts and tickets tables from the first example.
-- 🎯 MINI-CHALLENGE — uses only this lesson's ideas
-- Build a "tier" column from total_spent:
--   >= 1000  -> 'Gold'
--   >= 500   -> 'Silver'
--   otherwise-> 'Bronze'
-- Show name, total_spent, and the tier.
--
-- ✅ Expected: each customer labelled Gold / Silver / Bronze.

-- your query here

🎉 Lesson Complete

• ✅ Searched CASE WHEN cond THEN ... handles conditions and ranges
• ✅ Simple CASE col WHEN value THEN ... matches exact values
• ✅ The first matching branch wins — order your conditions carefully
• ✅ Always close with END; add ELSE to avoid stray NULLs
• ✅ CASE works in ORDER BY (custom sort) and inside SUM (conditional counts)
• ✅ Next: tame missing data with NULL-handling functions

Practice quiz

Continue this course

• Previous: IN, BETWEEN & Logical Operators
• Next: NULL Handling (COALESCE, IS NULL) — Work with NULLs safely — COALESCE, IS NULL, and the = NULL trap
• Quick reference: SQL cheat sheet › CASE & NULL Handling

Frequently asked questions

What's the difference between simple and searched CASE?

Simple CASE compares one expression to a list of values (CASE status WHEN 'A' THEN ...). Searched CASE evaluates a full boolean condition in each WHEN (CASE WHEN score >= 90 THEN ...), so it can handle ranges and complex logic. Searched CASE is the more flexible and common form.

Does CASE stop at the first match?

Yes. CASE evaluates WHEN branches top to bottom and returns the result of the first one that's true, ignoring the rest. Order matters — put your most specific or highest-priority conditions first.

What happens if no WHEN matches and there's no ELSE?

CASE returns NULL. If you don't want NULLs leaking into your result, always provide an ELSE branch as a catch-all default.

Can I use CASE in places other than SELECT?

Absolutely. CASE is an expression, so it works in ORDER BY (custom sort orders), WHERE, GROUP BY, and even inside aggregate functions like SUM(CASE WHEN ... THEN 1 ELSE 0 END) for conditional counting.

Do all branches need to return the same type?

Yes — every THEN/ELSE result should be type-compatible because a column has one type. Mixing a number and a string in different branches will either error or force an implicit conversion, so keep the return types consistent.

Related lessons

• SELECT