CASE WHEN

Part of the Beyond the Basics section of Coddy's SQL journey — lesson 1 of 27.

A CASE expression is an if/else if/else for SQL. It lives inside SELECT (or ORDER BY, or WHERE) and produces a value per row.

SELECT name,
       CASE
           WHEN score >= 90 THEN 'A'
           WHEN score >= 75 THEN 'B'
           WHEN score >= 60 THEN 'C'
           ELSE 'F'
       END AS grade
FROM students

Each WHEN is checked top-down; the first match wins. The ELSE is optional. Without it, unmatched rows get NULL.

Always end with END and give the column a name with AS.

Challenge

Easy

Available tables and columns:
employees: id, name, department, salary

For each employee return their name, department, and a tier column built like this:

'partner' when salary >= 150000 (any department)
'senior' when department = 'eng' and salary >= 100000
'mid' when salary >= 60000
'entry' otherwise

Order by salary descending. Note that the order of the WHEN branches matters: the first match wins.

Cheat sheet

CASE acts as if/else inside SELECT, producing a value per row:

SELECT name,
       CASE
           WHEN score >= 90 THEN 'A'
           WHEN score >= 75 THEN 'B'
           ELSE 'F'
       END AS grade
FROM students

Conditions are checked top-down; first match wins
ELSE is optional — without it, unmatched rows return NULL
Always close with END; use AS to name the column

Try it yourself

SELECT name, department,
       -- tier with multiple WHEN branches that mix salary and department
FROM employees

Test yourself

This lesson includes a short quiz. Start the lesson to answer it and track your progress.

All lessons in Beyond the Basics

1Conditional Logic

CASE WHENCASE in ORDER BY COALESCE for NULLs NULLIF and IIF Recap - Attendance

4Set Operations

UNION vs UNION ALL INTERSECT EXCEPT Recap - Membership

2String Functions

LENGTH, UPPER, LOWER SUBSTR INSTR REPLACE and TRIM Concatenating with ||Recap - Invoices

5Recursive CTEs

A Number Sequence Walking a Hierarchy Recap - Org Chart

3Conditional Aggregation

COUNT with CASE SUM with CASE Pivoting Rows to Columns Recap - Traffic Sources

6Analytics Recipes

Top-N per Group Percentage of Total Running Total Keeping One Row per Key Recap - Top Customers