Conditional Expressions

Q: Q: What's the difference between CASE WHEN and IF? Which should I use?

A: IF can only handle binary choices (true/false) and is suitable for simple scenarios; CASE WHEN can handle multiple condition branches and is suitable for complex logic. In MySQL, IF is more concise, but CASE WHEN is the SQL standard with better cross-database compatibility. For complex conditions, use CASE; for simple checks, use IF or COALESCE.

Q: Q: Can CASE expressions be used in WHERE clauses?

A: Yes, but it's not recommended. Conditions should typically be written directly in WHERE rather than wrapped in CASE. CASE is better suited for generating new columns in SELECT, custom sorting in ORDER BY, and conditional aggregation in GROUP BY.

Q: Q: What is the execution order of CASE WHEN?

A: CASE evaluates WHEN clauses in written order — once a WHEN condition is true, it returns the corresponding THEN value and subsequent WHENs are not evaluated. Place the most common or highest-priority conditions first.

Q: Q: Which is better, COALESCE or IFNULL?

A: COALESCE is a SQL standard function supporting multiple arguments (e.g., COALESCE(a, b, c, 0)) and works across databases; IFNULL is MySQL-specific and only supports two arguments. COALESCE is recommended for better compatibility and flexibility.

Imagine you're ordering at a restaurant and the waiter asks: "Would you like spicy or non-spicy? Mild, medium, or extra hot?" — this is conditional logic: giving different answers based on different situations. The CASE WHEN in SQL works the same way: returning different values based on different conditions, making your query results more flexible and meaningful.

1. Core Concepts

Simple CASE Syntax

Simple CASE compares an expression against multiple values, similar to a switch statement in programming languages.

SQL

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

SQL

-- Return Chinese descriptions based on order status
SELECT order_id, status,
    CASE status
        WHEN 'completed' THEN 'Completed'
        WHEN 'shipped'   THEN 'Shipped'
        WHEN 'pending'   THEN 'Pending'
        WHEN 'cancelled' THEN 'Cancelled'
        ELSE 'Unknown Status'
    END AS status_description
FROM orders;

Searched CASE Syntax

Searched CASE uses conditional expressions, offering more flexibility for complex logic.

SQL

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

SQL

-- Classify salary levels
SELECT first_name, last_name, salary,
    CASE
        WHEN salary >= 15000 THEN 'High'
        WHEN salary >= 10000 THEN 'Medium'
        WHEN salary >= 7000  THEN 'Standard'
        ELSE 'Entry Level'
    END AS salary_level
FROM employees;

💡 Simple CASE vs Searched CASE: Simple CASE can only do equality comparisons; searched CASE can use >, <, LIKE, IS NULL, and any other conditions. Searched CASE is recommended for most scenarios as it's more flexible.

CASE in SELECT

Using CASE in the SELECT column list allows conditional evaluation for each row, generating new computed columns.

SQL

SELECT 
    CONCAT(first_name, last_name) AS name,
    salary,
    CASE
        WHEN salary >= 12000 THEN 'Senior'
        WHEN salary >= 8000  THEN 'Mid-Level'
        ELSE 'Junior'
    END AS rank
FROM employees;

CASE in ORDER BY

Use CASE to define custom sorting rules for "sort by specified order."

SQL

-- Custom sort by status: Pending > Shipped > Completed > Cancelled
SELECT order_id, status, total_amount
FROM orders
ORDER BY 
    CASE status
        WHEN 'pending'   THEN 1
        WHEN 'shipped'   THEN 2
        WHEN 'completed' THEN 3
        WHEN 'cancelled' THEN 4
    END;

CASE in GROUP BY

Combined with aggregate functions, this enables conditional statistics (row-to-column pivoting), a common technique in data analysis.

SQL

-- Count orders by status
SELECT 
    SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) AS completed,
    SUM(CASE WHEN status = 'shipped'   THEN 1 ELSE 0 END) AS shipped,
    SUM(CASE WHEN status = 'pending'   THEN 1 ELSE 0 END) AS pending,
    SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled
FROM orders;

CASE in UPDATE

Use CASE in UPDATE statements for batch conditional updates, completing multiple update logic operations in a single SQL statement.

SQL

-- Adjust salary based on performance
UPDATE employees
SET salary = CASE
    WHEN salary < 8000  THEN salary * 1.15
    WHEN salary < 12000 THEN salary * 1.10
    ELSE salary * 1.05
END;

IF / IFNULL / COALESCE

MySQL provides more concise conditional functions than CASE:

Function	Syntax	Purpose
`IF`	`IF(condition, true_value, false_value)`	Simple binary choice
`IFNULL`	`IFNULL(expression, replacement)`	Return replacement when NULL
`COALESCE`	`COALESCE(value1, value2, ...)`	Return the first non-NULL value

SQL

-- IF: simple binary choice
SELECT IF(salary > 10000, 'High', 'Normal') AS level FROM employees;

-- IFNULL: handle NULL
SELECT IFNULL(department_id, 0) AS department_id FROM employees;

-- COALESCE: return first non-NULL
SELECT COALESCE(commission, bonus, 0) AS bonus FROM employees;

💡 COALESCE vs IFNULL: COALESCE is a SQL standard function supporting multiple arguments; IFNULL is MySQL-specific and only supports two arguments. COALESCE is recommended for better compatibility.

2. Basic Syntax

CASE WHEN Complete Syntax

SQL

-- Simple CASE
CASE expression
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    [ELSE default_result]
END

-- Searched CASE
CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    [ELSE default_result]
END

💡 Tip: CASE expressions return a value and can appear anywhere an expression is used (SELECT, WHERE, ORDER BY, GROUP BY, etc.). WHEN clauses are evaluated in order — once a match is found, the result is returned and subsequent WHENs are not evaluated.

💡 Tip: If ELSE is omitted and no match is found, CASE returns NULL. It's recommended to always include ELSE to handle unexpected cases.

Conditional Function Syntax

SQL

IF(condition, true_value, false_value)
IFNULL(expression, replacement)
COALESCE(value1, value2, ..., valueN)

💡 Tip: COALESCE can replace IFNULL and supports multiple fallback values — it is recommended.

Example: Add Status Labels and Priority to Orders (Difficulty ⭐)

SQL

SELECT 
    order_id,
    CONCAT(e.first_name, e.last_name) AS owner,
    o.total_amount AS amount,
    CASE status
        WHEN 'completed' THEN '✅ Completed'
        WHEN 'shipped'   THEN '🚚 Shipped'
        WHEN 'pending'   THEN '⏳ Pending'
        WHEN 'cancelled' THEN '❌ Cancelled'
        ELSE '❓ Unknown'
    END AS status_label,
    CASE
        WHEN total_amount >= 3000 THEN 'High'
        WHEN total_amount >= 1000 THEN 'Medium'
        ELSE 'Low'
    END AS priority
FROM orders o
LEFT JOIN employees e ON o.employee_id = e.employee_id
ORDER BY 
    CASE
        WHEN total_amount >= 3000 THEN 1
        WHEN total_amount >= 1000 THEN 2
        ELSE 3
    END;

▶ Try it Yourself

Output:

TEXT

order_id | owner    | amount    | status_label    | priority
---------+----------+-----------+-----------------+---------
    1005 | ZhaoMin  | 4100.00   | ✅ Completed    | High
    1003 | ZhaoMin  | 3200.00   | 🚚 Shipped      | High
    1006 | WangQiang| 2800.00   | ❌ Cancelled    | Medium
    1001 | WangQiang| 2500.00   | ✅ Completed    | Medium
    1002 | WangQiang| 1800.00   | ✅ Completed    | Medium
    1004 | ZhangWei |  950.00   | ⏳ Pending      | Low

Query approach:

The first CASE converts English status to labeled descriptions
The second CASE classifies priority by amount
The CASE in ORDER BY sorts by priority

Example: Count Employees by Salary Level per Department (Difficulty ⭐⭐)

SQL

SELECT 
    d.department_name AS department,
    COUNT(*) AS total_count,
    SUM(CASE WHEN e.salary >= 12000 THEN 1 ELSE 0 END) AS high_salary,
    SUM(CASE WHEN e.salary >= 8000 AND e.salary < 12000 THEN 1 ELSE 0 END) AS medium_salary,
    SUM(CASE WHEN e.salary < 8000 THEN 1 ELSE 0 END) AS standard_salary,
    AVG(e.salary) AS avg_salary
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
GROUP BY d.department_name
ORDER BY avg_salary DESC;

▶ Try it Yourself

Output:

TEXT

department | total_count | high_salary | medium_salary | standard_salary | avg_salary
-----------+-------------+-------------+---------------+-----------------+-----------
Technology |           2 |           1 |             1 |               0 | 10750.00
Sales      |           2 |           1 |             1 |               0 |  9500.00
Marketing  |           1 |           0 |             0 |               1 |  7500.00
NULL       |           1 |           0 |             1 |               0 |  9000.00

Query approach:

GROUP BY groups by department
Each SUM(CASE ...) counts employees meeting the condition, achieving row-to-column pivoting
Simultaneously calculates average salary as a reference

3. Common Application Scenarios

Scenario 1: Dynamic Labels — Tag Employees Based on Multi-Dimensional Conditions

SQL

SELECT 
    CONCAT(e.first_name, e.last_name) AS name,
    d.department_name AS department,
    e.salary,
    CASE
        WHEN e.salary >= 12000 AND d.department_name = 'Technology' THEN 'Core Technical Staff'
        WHEN e.salary >= 12000 THEN 'Senior Employee'
        WHEN e.salary >= 8000 AND e.hire_date < '2023-06-01' THEN 'Experienced Mid-Level'
        WHEN e.salary >= 8000 THEN 'Mid-Level Employee'
        ELSE 'Junior Employee'
    END AS employee_tag
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
ORDER BY e.salary DESC;

Output:

TEXT

name      | department | salary    | employee_tag
----------+------------+-----------+----------------------
ZhangWei  | Technology | 12000.00  | Core Technical Staff
ZhaoMin   | Sales      | 11000.00  | Senior Employee
LiNa      | Technology |  9500.00  | Experienced Mid-Level
ChenJing  | NULL       |  9000.00  | Mid-Level Employee
WangQiang | Sales      |  8000.00  | Experienced Mid-Level
LiuYang   | Marketing  |  7500.00  | Junior Employee

💡 Approach: Searched CASE's WHEN clauses are evaluated in order — once a match is found, it returns immediately, so the order of conditions matters — more specific conditions should come first.

Scenario 2: UPDATE + CASE for Batch Conditional Updates

SQL

-- Adjust salary based on tenure and department
UPDATE employees
SET salary = CASE
    WHEN department_id = 1 AND hire_date < '2023-01-01' THEN salary * 1.15
    WHEN department_id = 1 THEN salary * 1.10
    WHEN department_id = 2 THEN salary * 1.08
    WHEN department_id IS NULL THEN salary * 1.05
    ELSE salary * 1.03
END;

💡 Approach: A single UPDATE completes multiple update logic operations, avoiding writing multiple UPDATE statements. CASE evaluates in order, so the "Technology department and early hire" condition comes before "Technology department."

❓ FAQ

Q: What's the difference between CASE WHEN and IF? Which should I use? A: IF can only handle binary choices (true/false) and is suitable for simple scenarios; CASE WHEN can handle multiple condition branches and is suitable for complex logic. In MySQL, IF is more concise, but CASE WHEN is the SQL standard with better cross-database compatibility. For complex conditions, use CASE; for simple checks, use IF or COALESCE.

Q: Can CASE expressions be used in WHERE clauses? A: Yes, but it's not recommended. Conditions should typically be written directly in WHERE rather than wrapped in CASE. CASE is better suited for generating new columns in SELECT, custom sorting in ORDER BY, and conditional aggregation in GROUP BY.

Q: What is the execution order of CASE WHEN? A: CASE evaluates WHEN clauses in written order — once a WHEN condition is true, it returns the corresponding THEN value and subsequent WHENs are not evaluated. Place the most common or highest-priority conditions first.

Q: Which is better, COALESCE or IFNULL? A: COALESCE is a SQL standard function supporting multiple arguments (e.g., COALESCE(a, b, c, 0)) and works across databases; IFNULL is MySQL-specific and only supports two arguments. COALESCE is recommended for better compatibility and flexibility.

📖 Summary

CASE WHEN is SQL's conditional expression and can appear in SELECT, WHERE, ORDER BY, GROUP BY, and more
Simple CASE: equality comparison, suitable for "one field matching multiple values"
Searched CASE: arbitrary conditions, suitable for complex logic, recommended as the default choice
CASE in GROUP BY: combined with SUM/COUNT for conditional statistics (row-to-column pivoting), a core data analysis technique
CASE in UPDATE: complete multiple update operations in a single UPDATE
IF/IFNULL/COALESCE: more concise conditional functions; COALESCE has the best compatibility

📝 Exercises

Exercise 1 (⭐): Query all orders, adding an "amount tier" column: amount >= 3000 is "Large Order", 1000-2999 is "Medium Order", < 1000 is "Small Order". Sort by amount tier.

Exercise 2 (⭐⭐): Aggregate order statistics per employee using CASE WHEN with aggregate functions to generate a report with: employee name, total orders, completed orders, pending orders, and total sales.

Exercise 3 (⭐⭐): Use UPDATE + CASE to raise salary by 10% for "Sales" department employees, 8% for "Technology" department, and 5% for other departments.

Next Lesson

👉 17-views-indexes - Views and Indexes: Learn how to create and use views (virtual tables), as well as index concepts and optimization strategies!

Web-Tutorial Tech Team

A team of developers maintaining programming tutorials. Each tutorial is written and reviewed by developers with expertise in that field. We work to keep our content accurate and reliable — if you spot an issue, please let us know.

About Us

Conditional Expressions

Conditional Expressions

1. Core Concepts

Simple CASE Syntax

Searched CASE Syntax

CASE in SELECT

CASE in ORDER BY

CASE in GROUP BY

CASE in UPDATE

IF / IFNULL / COALESCE

2. Basic Syntax

CASE WHEN Complete Syntax

Conditional Function Syntax

Example: Add Status Labels and Priority to Orders (Difficulty ⭐)

Example: Count Employees by Salary Level per Department (Difficulty ⭐⭐)

3. Common Application Scenarios

Scenario 1: Dynamic Labels — Tag Employees Based on Multi-Dimensional Conditions

Scenario 2: UPDATE + CASE for Batch Conditional Updates

❓ FAQ

📖 Summary

📝 Exercises

Next Lesson

🙏 帮我们做得更好