? MySQL CASE

? Quick Overview

The CASE expression adds conditional logic to SQL queries — similar to an if/else. Use it to return different values from a single SELECT (or in UPDATE/ORDER BY/aggregations) depending on evaluated conditions.

? Key Concepts

Expression-based: CASE evaluates conditions and returns a value for the row.
Sequential evaluation: Conditions are checked in order — the first true WHEN wins.
Fallback: Use ELSE to provide a default result (avoids NULL).
Versatile: Works in SELECT, UPDATE, ORDER BY, and inside aggregates.

? Syntax / Theory

? View Code Example

-- General CASE expression syntax: checks conditions and returns corresponding results
SELECT column1, column2,
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
ELSE result3
END AS alias_name
FROM table_name;

? Code Example

? View Code Example

-- Example: group employees into department teams using CASE
SELECT employee_id, first_name, last_name,
CASE
WHEN department = 'Sales' THEN 'Sales Team'
WHEN department = 'IT' THEN 'Tech Team'
ELSE 'Other Team'
END AS department_group
FROM employees;

⚙️ Live Output / Explanation

What happens when the query runs

For each row in employees the CASE expression evaluates the department value:

If department = 'Sales' → department_group = Sales Team
If department = 'IT' → department_group = Tech Team
If none match → department_group = Other Team (from ELSE)

This allows a single SELECT to add human-friendly or computed categories without changing source data.

✅ Tips & Best Practices

Always include an ELSE when appropriate to avoid unexpected NULL results.
Prefer simple conditions; complex nested CASE can become hard to read — consider helper columns or views for clarity.
Use CASE in ORDER BY to implement custom sorting (e.g., prioritise statuses).
Inside aggregates, use SUM(CASE WHEN ... THEN 1 ELSE 0 END) or similar for conditional counts.

? Try It Yourself / Practice Tasks

Use CASE within an UPDATE to set a status column based on numeric ranges (e.g., score >= 90 → 'A').
Create a query that sorts rows with a custom priority using ORDER BY CASE.
Write an aggregate that counts only rows meeting a condition using SUM(CASE ...).