CASE WHEN — Conditional Logic in SQL

Posted May 23, 2026

By Rahul Chandna

9 min read

title: “CASE WHEN (Conditional Logic)” part: 2 topic_number: 2 slug: “case-when” difficulty: “Intermediate” prerequisites: “select-from-where” —

CASE WHEN — Conditional Logic in SQL

What Is It?

CASE WHEN is SQL’s if-then-else statement. It lets you create conditional logic directly in your queries — returning different values based on conditions.

Real-world analogy: Like a decision tree — “If this condition is true, do X; otherwise, if that condition is true, do Y; else do Z.”

When you’d use it:

Categorize data (e.g., salary ranges into “Low”, “Medium”, “High”)
Create custom labels or flags
Conditional calculations
Data transformation and formatting

Syntax Breakdown

Simple CASE (compare one value)

        
      
CASE expression
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ...
    ELSE default_result
END

Searched CASE (multiple conditions)

        
      
CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE default_result
END

Key points:

WHEN specifies the condition
THEN specifies the result if condition is true
ELSE provides a default (optional but recommended)
END closes the CASE statement
Evaluated top-to-bottom (first match wins)

Basic Examples

Categorize Salaries

        
      
SELECT 
    first_name,
    last_name,
    salary,
    CASE
        WHEN salary >= 90000 THEN 'High'
        WHEN salary >= 70000 THEN 'Medium'
        ELSE 'Low'
    END AS salary_category
FROM employees
ORDER BY salary DESC
LIMIT 8;

Expected output:

first_name	last_name	salary	salary_category
Sam	Clark	95000.00	High
Frank	Miller	93000.00	High
Henry	Moore	92000.00	High
Paul	Garcia	87000.00	Medium
Bob	Smith	82000.00	Medium
Alice	Johnson	75000.00	Medium
Ivy	Anderson	74000.00	Medium
George	Jones	71000.00	Medium

How it works: Checks conditions from top to bottom. First match wins.

Simple CASE (Exact Matches)

        
      
SELECT 
    department_id,
    department_name,
    CASE department_name
        WHEN 'Engineering' THEN 'Tech'
        WHEN 'Data Science' THEN 'Tech'
        WHEN 'IT' THEN 'Tech'
        WHEN 'Sales' THEN 'Revenue'
        WHEN 'Marketing' THEN 'Revenue'
        ELSE 'Operations'
    END AS division
FROM departments
ORDER BY division, department_name;

Expected output:

department_id	department_name	division
5	Finance	Operations
4	Human Resources	Operations
7	Legal	Operations
2	Marketing	Revenue
3	Sales	Revenue
8	Data Science	Tech
1	Engineering	Tech

Departments grouped into divisions.

Going Deeper

Conditional Calculations

Give bonuses based on salary: 10% for high earners, 15% for others:

        
      
SELECT 
    first_name,
    last_name,
    salary,
    CASE
        WHEN salary >= 80000 THEN salary * 0.10
        ELSE salary * 0.15
    END AS bonus
FROM employees
ORDER BY salary DESC
LIMIT 5;

Expected output:

first_name	last_name	salary	bonus
Sam	Clark	95000.00	9500.00
Frank	Miller	93000.00	9300.00
Henry	Moore	92000.00	9200.00
Paul	Garcia	87000.00	8700.00
Bob	Smith	82000.00	8200.00

Higher earners get smaller percentage, but larger absolute bonus.

Multi-Condition Logic

        
      
SELECT 
    first_name,
    last_name,
    salary,
    hire_date,
    CASE
        WHEN salary >= 90000 AND hire_date < '2020-01-01' THEN 'Senior High Earner'
        WHEN salary >= 90000 THEN 'High Earner'
        WHEN hire_date < '2020-01-01' THEN 'Veteran'
        ELSE 'Standard'
    END AS employee_type
FROM employees
ORDER BY salary DESC, hire_date
LIMIT 10;

Expected output:

first_name	last_name	salary	hire_date	employee_type
Frank	Miller	93000.00	2018-09-01	Senior High Earner
Henry	Moore	92000.00	2019-04-18	Senior High Earner
Sam	Clark	95000.00	2021-08-05	High Earner
Paul	Garcia	87000.00	2020-10-22	Standard
Bob	Smith	82000.00	2020-05-10	Standard
…	…	…	…	…

Complex categorization based on multiple factors.

CASE in WHERE Clause

        
      
-- Find employees: high earners in Engineering, or anyone in Sales
SELECT 
    e.first_name,
    e.last_name,
    e.salary,
    d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE 
    CASE d.department_name
        WHEN 'Engineering' THEN e.salary >= 80000
        WHEN 'Sales' THEN TRUE
        ELSE FALSE
    END
ORDER BY d.department_name, e.salary DESC;

Expected output:

first_name	last_name	salary	department_name
Sam	Clark	95000.00	Engineering
Paul	Garcia	87000.00	Engineering
Bob	Smith	82000.00	Sales
Carol	Williams	78000.00	Sales
Noah	Harris	64000.00	Sales

Different criteria for different departments.

Pause and Predict: What happens if no WHEN conditions match and there’s no ELSE clause?

Answer

`NULL` If no conditions match and no ELSE is specified, CASE returns NULL. That's why ELSE clauses are recommended!

Watch Out — Common Mistakes

Mistake #1: Forgetting END

        
      
--  SYNTAX ERROR
SELECT 
    salary,
    CASE
        WHEN salary >= 80000 THEN 'High'
        ELSE 'Low'
    -- Missing END!
FROM employees;

Error: SQL syntax error near ‘FROM’

Fix: Always close with END:

        
      
-- • CORRECT
SELECT 
    salary,
    CASE
        WHEN salary >= 80000 THEN 'High'
        ELSE 'Low'
    END AS salary_level
FROM employees;

Mistake #2: Wrong Order of Conditions

        
      
--  LOGIC ERROR
CASE
    WHEN salary >= 50000 THEN 'High'   -- This matches EVERYTHING >= 50k!
    WHEN salary >= 80000 THEN 'Very High'  -- This NEVER runs
    ELSE 'Low'
END

Problem: First condition catches all salaries over 50k, so the second condition never executes.

Fix: Order from most specific to least specific:

        
      
-- • CORRECT
CASE
    WHEN salary >= 80000 THEN 'Very High'
    WHEN salary >= 50000 THEN 'High'
    ELSE 'Low'
END

Mistake #3: Mixing Data Types

        
      
--  INCONSISTENT TYPES
CASE
    WHEN salary >= 80000 THEN 'High'
    WHEN salary >= 50000 THEN 75000    -- Number instead of string!
    ELSE 'Low'
END

Problem: MySQL will try to convert everything to the same type, leading to unexpected results.

Fix: Keep all THEN results the same data type:

        
      
-- • CORRECT (all strings)
CASE
    WHEN salary >= 80000 THEN 'High'
    WHEN salary >= 50000 THEN 'Medium'
    ELSE 'Low'
END

Edge Case Spotlight

NULL Handling in Conditions

        
      
SELECT 
    'Test' AS label,
    CASE
        WHEN NULL = NULL THEN 'Equal'  -- Never true!
        WHEN NULL IS NULL THEN 'Is Null'  -- This works
        ELSE 'Not Null'
    END AS result;

Expected output:

label	result
Test	Is Null

Key: Use IS NULL, not = NULL in conditions.

Nested CASE Statements

        
      
SELECT 
    first_name,
    salary,
    hire_date,
    CASE
        WHEN salary >= 80000 THEN
            CASE
                WHEN hire_date < '2020-01-01' THEN 'Senior Level'
                ELSE 'New High Earner'
            END
        ELSE 'Standard'
    END AS classification
FROM employees
LIMIT 5;

Expected output:

first_name	salary	hire_date	classification
Alice	75000.00	2019-03-15	Standard
Bob	82000.00	2020-05-10	New High Earner
Carol	78000.00	2020-08-20	Standard
David	66000.00	2021-11-30	Standard
Eve	69000.00	2022-02-14	Standard

Nested CASE for complex decision trees (but can get hard to read).

Try This

Exercise 1 (Guided)

Categorize employees by tenure: ‘Veteran’ (hired before 2019), ‘Experienced’ (2019-2021), ‘Recent’ (2022 or later). Show first_name, last_name, hire_date, and tenure_category. Sort by hire_date.

Hint

Use CASE WHEN with date comparisons: hire_date < '2019-01-01', etc.

Exercise 2 (Independent)

Create a “performance bonus” calculator:

Salary >= 90k: 5% bonus
Salary 70k-89k: 8% bonus
Salary < 70k: 10% bonus

Show first_name, last_name, salary, bonus_percentage (‘5%’, ‘8%’, ‘10%’), and bonus_amount (calculated).

Hint

Use CASE for both bonus_percentage (text) and bonus_amount (calculation with salary * 0.05, etc.).

Exercise 3 (Challenge)

Create a department “health” report. For each department, calculate:

employee_count
avg_salary
health_status: ‘Excellent’ if avg_salary > 75k AND employee_count >= 3, ‘Good’ if avg_salary > 70k, ‘Needs Review’ otherwise

Show department_name, employee_count, avg_salary, and health_status. Sort by health_status, then avg_salary DESC.

Hint

JOIN employees and departments, GROUP BY department, use CASE with AND conditions on aggregates.

Answer Key

Exercise 1 Answer

```sql SELECT first_name, last_name, hire_date, CASE WHEN hire_date < '2019-01-01' THEN 'Veteran' WHEN hire_date < '2022-01-01' THEN 'Experienced' ELSE 'Recent' END AS tenure_category FROM employees ORDER BY hire_date; ``` **Expected output:** | first_name | last_name | hire_date | tenure_category | |------------|-----------|-----------|-----------------| | George | Jones | 2017-07-12 | Veteran | | Ivy | Anderson | 2018-02-20 | Veteran | | Frank | Miller | 2018-09-01 | Veteran | | Alice | Johnson | 2019-03-15 | Experienced | | ... | ... | ... | ... | Employees categorized by hire date.

Exercise 2 Answer

```sql SELECT first_name, last_name, salary, CASE WHEN salary >= 90000 THEN '5%' WHEN salary >= 70000 THEN '8%' ELSE '10%' END AS bonus_percentage, CASE WHEN salary >= 90000 THEN salary * 0.05 WHEN salary >= 70000 THEN salary * 0.08 ELSE salary * 0.10 END AS bonus_amount FROM employees ORDER BY salary DESC; ``` **Expected output:** | first_name | last_name | salary | bonus_percentage | bonus_amount | |------------|-----------|---------|------------------|--------------| | Sam | Clark | 95000.00 | 5% | 4750.00 | | Frank | Miller | 93000.00 | 5% | 4650.00 | | Henry | Moore | 92000.00 | 5% | 4600.00 | | Paul | Garcia | 87000.00 | 8% | 6960.00 | | Bob | Smith | 82000.00 | 8% | 6560.00 | | ... | ... | ... | ... | ... | Lower salaries get higher percentage bonuses.

Exercise 3 Answer

```sql SELECT d.department_name, COUNT(e.employee_id) AS employee_count, ROUND(AVG(e.salary), 2) AS avg_salary, CASE WHEN AVG(e.salary) > 75000 AND COUNT(e.employee_id) >= 3 THEN 'Excellent' WHEN AVG(e.salary) > 70000 THEN 'Good' ELSE 'Needs Review' END AS health_status FROM departments d LEFT JOIN employees e ON d.department_id = e.department_id GROUP BY d.department_id, d.department_name ORDER BY CASE health_status WHEN 'Excellent' THEN 1 WHEN 'Good' THEN 2 ELSE 3 END, avg_salary DESC; ``` **Expected output:** | department_name | employee_count | avg_salary | health_status | |-----------------|----------------|------------|---------------| | Engineering | 3 | 85666.67 | Excellent | | Marketing | 2 | 87500.00 | Good | | Finance | 2 | 79000.00 | Good | | Sales | 3 | 69666.67 | Needs Review | | ... | ... | ... | ... | Department health assessment based on size and salaries. **Note:** The ORDER BY uses CASE to sort by health status category, then by salary within each category.

Quick Recap

• CASE WHEN provides if-then-else logic in SQL
• Simple CASE compares one expression to values
• Searched CASE evaluates multiple conditions
• Conditions evaluated top-to-bottom (first match wins)
• ELSE provides default (returns NULL if omitted)
• Must close with END
• Keep THEN results the same data type
• Use IS NULL for NULL checks, not = NULL

Up Next

Next topic: Self Joins → part2_03_self_joins.md

Type ‘next’ when ready to continue!

Graduate Training, MySQL

Graduate Training

This post is licensed under CC BY 4.0 by the author.

CASE WHEN — Conditional Logic in SQL

What Is It?

Syntax Breakdown

Simple CASE (compare one value)

Searched CASE (multiple conditions)

Basic Examples

Categorize Salaries

Simple CASE (Exact Matches)

Going Deeper

Conditional Calculations

Multi-Condition Logic

CASE in WHERE Clause

Watch Out — Common Mistakes

Mistake #1: Forgetting END

Mistake #2: Wrong Order of Conditions

Mistake #3: Mixing Data Types

Edge Case Spotlight

NULL Handling in Conditions

Nested CASE Statements

Try This

Exercise 1 (Guided)

Exercise 2 (Independent)

Exercise 3 (Challenge)

Answer Key

Quick Recap

Up Next

Trending Tags