Applying Conditional Logic Using CASE Expressions in MySQL – Detailed Notes

Course: MySQL Training

Topic: Applying Conditional Logic Using CASE Expressions

Course Link: MySQL Training Course

Demo Link: Register for a Free Course Demo

1. Introduction to CASE Expressions in MySQL

The CASE expression in MySQL is used to apply conditional logic within SQL queries. It evaluates conditions and returns a value corresponding to the first condition that evaluates to true. It is similar to an IF-ELSE or IF-ELSE IF structure in programming languages.

CASE expressions are useful when a query needs to categorize records, assign labels, calculate conditional values, classify performance, apply discounts, or display different results based on specific rules.

For example, an organization may want to classify employees as 'High Salary', 'Medium Salary', or 'Low Salary' based on their salary. Instead of writing separate queries for each category, a CASE expression can handle all the conditions in one query.

Example Scenario

Suppose an employees table contains employee names and salaries. The following query categorizes employees based on their salary:

SELECT
    employee_name,
    salary,
    CASE
        WHEN salary >= 80000 THEN 'High Salary'
        WHEN salary >= 40000 THEN 'Medium Salary'
        ELSE 'Low Salary'
    END AS salary_category
FROM employees;

In this query, MySQL checks the conditions in order and returns the category associated with the first matching condition.

2. Learning Objectives

After completing this topic, learners should be able to:

• Understand the purpose and syntax of CASE expressions in MySQL.
• Differentiate between simple CASE and searched CASE expressions.
• Use WHEN, THEN, ELSE, and END correctly.
• Apply conditional logic to SELECT query results.
• Create categories using numerical and text-based conditions.
• Use CASE expressions with aggregate functions and GROUP BY.
• Apply conditional sorting using ORDER BY and CASE.
• Perform conditional calculations and generate custom output values.
• Handle NULL values and missing ELSE conditions appropriately.
• Identify common CASE expression errors and follow SQL best practices.

3. What Is a CASE Expression?

A CASE expression is a SQL expression that evaluates one or more conditions and returns a value based on the matching condition. It can be used in several parts of a query, including SELECT, ORDER BY, GROUP BY, and aggregate calculations.

3.1 Important Components

3.2 General Syntax

SELECT
    column_name,
    CASE
        WHEN condition_1 THEN result_1
        WHEN condition_2 THEN result_2
        WHEN condition_3 THEN result_3
        ELSE default_result
    END AS result_column
FROM table_name;

Explanation:

• SELECT specifies the columns to display.
• CASE begins the conditional expression.
• Each WHEN defines a condition.
• THEN specifies the value to return when the corresponding condition is true.
• ELSE provides a fallback value if no condition matches.
• END closes the expression.
• AS assigns a meaningful name to the calculated result.

4. Types of CASE Expressions in MySQL

MySQL supports two main forms of CASE expressions:

1. Simple CASE expression: Compares one expression against multiple possible values.
2. Searched CASE expression: Evaluates multiple independent conditions, including comparisons and logical expressions.

5. Simple CASE Expression

A simple CASE expression compares a single expression with different values. It is suitable when a column or expression must be matched against specific values.

5.1 Syntax

CASE expression
    WHEN value_1 THEN result_1
    WHEN value_2 THEN result_2
    WHEN value_3 THEN result_3
    ELSE default_result
END

5.2 Example: Categorizing Employee Departments

Assume an employees table contains a department_code column. The codes need to be displayed as readable department names.

SELECT
    employee_name,
    department_code,
    CASE department_code
        WHEN 'IT' THEN 'Information Technology'
        WHEN 'HR' THEN 'Human Resources'
        WHEN 'FN' THEN 'Finance'
        ELSE 'Other Department'
    END AS department_name
FROM employees;

How it works:

• If department_code is 'IT', MySQL returns 'Information Technology'.
• If department_code is 'HR', MySQL returns 'Human Resources'.
• If department_code is 'FN', MySQL returns 'Finance'.
• If none of these values match, MySQL returns 'Other Department'.

5.3 Example Output

5.4 When to Use Simple CASE

• Mapping status codes to readable descriptions.
• Converting department codes into department names.
• Displaying product types based on fixed identifiers.
• Converting numeric codes into text labels.
• Mapping payment methods such as 'CASH', 'CARD', and 'UPI' to display names.

6. Searched CASE Expression

A searched CASE expression evaluates separate Boolean conditions. It is useful when conditions involve comparison operators, ranges, logical operators, or multiple columns.

6.1 Syntax

CASE
    WHEN condition_1 THEN result_1
    WHEN condition_2 THEN result_2
    WHEN condition_3 THEN result_3
    ELSE default_result
END

6.2 Example: Categorizing Student Marks

SELECT
    student_name,
    marks,
    CASE
        WHEN marks >= 90 THEN 'Grade A'
        WHEN marks >= 75 THEN 'Grade B'
        WHEN marks >= 60 THEN 'Grade C'
        WHEN marks >= 40 THEN 'Grade D'
        ELSE 'Fail'
    END AS grade
FROM students;

Explanation: MySQL evaluates each condition from top to bottom. If marks are 92, the first condition matches and the result is 'Grade A'. If marks are 78, the first condition fails but the second matches, so the result is 'Grade B'.

6.3 Example Output

6.4 When to Use Searched CASE

• Classifying salaries into ranges.
• Assigning grades based on marks.
• Applying discounts based on purchase amounts.
• Checking multiple conditions using AND and OR.
• Classifying orders based on payment and delivery status.

7. Understanding WHEN and THEN

The WHEN and THEN keywords form the core of a CASE expression. WHEN defines a condition, and THEN provides the result for that condition.

Example

SELECT
    product_name,
    price,
    CASE
        WHEN price > 10000 THEN 'Premium'
        WHEN price > 5000 THEN 'Standard'
        WHEN price >= 1000 THEN 'Budget'
        ELSE 'Low Cost'
    END AS price_category
FROM products;

For a product priced at 12,000, the first condition matches. For a product priced at 7,000, the first condition fails and the second condition matches.

Important: Conditions are checked in order. Put more specific or higher-priority conditions before broader conditions to avoid unintended classifications.

8. Using ELSE as a Default Result

The ELSE clause specifies what MySQL should return when none of the WHEN conditions matches. Although ELSE is optional, using it can make the query's behavior clearer.

Example with ELSE

SELECT
    order_id,
    status,
    CASE
        WHEN status = 'Pending' THEN 'Awaiting Processing'
        WHEN status = 'Shipped' THEN 'On the Way'
        WHEN status = 'Delivered' THEN 'Completed'
        ELSE 'Unknown Status'
    END AS status_description
FROM orders;

Example Without ELSE

SELECT
    order_id,
    status,
    CASE
        WHEN status = 'Pending' THEN 'Awaiting Processing'
        WHEN status = 'Shipped' THEN 'On the Way'
    END AS status_description
FROM orders;

If the status is neither 'Pending' nor 'Shipped', the CASE expression returns NULL because no condition matches and ELSE is omitted.

Best Practice

Use ELSE when unmatched values need a meaningful fallback, such as 'Other', 'Unknown', or 'Not Classified'. Choose a fallback that accurately describes the data rather than hiding data-quality problems.

9. Using CASE with Comparison Operators

Searched CASE expressions can use comparison operators to evaluate numeric and text-based conditions.

Example: Inventory Classification

SELECT
    product_name,
    stock_quantity,
    CASE
        WHEN stock_quantity = 0 THEN 'Out of Stock'
        WHEN stock_quantity < 10 THEN 'Low Stock'
        WHEN stock_quantity <= 50 THEN 'Available'
        ELSE 'High Stock'
    END AS stock_status
FROM products;

This query assigns a stock status based on the available quantity. The zero-stock condition is checked first, ensuring that zero is classified as 'Out of Stock' rather than 'Low Stock'.

10. Using CASE with AND, OR, and NOT

CASE expressions can combine conditions with logical operators. This allows a result to depend on more than one rule.

10.1 Using AND

AND requires both conditions to be true.

SELECT
    employee_name,
    salary,
    experience_years,
    CASE
        WHEN salary >= 60000 AND experience_years >= 5
            THEN 'Senior Eligible'
        ELSE 'Standard'
    END AS employee_category
FROM employees;

10.2 Using OR

OR requires at least one condition to be true.

SELECT
    product_name,
    price,
    CASE
        WHEN price > 50000 OR category = 'Luxury'
            THEN 'Premium Product'
        ELSE 'Regular Product'
    END AS product_type
FROM products;

10.3 Using NOT

NOT reverses a logical condition.

SELECT
    order_id,
    status,
    CASE
        WHEN NOT (status = 'Cancelled')
            THEN 'Active or Completed'
        ELSE 'Cancelled Order'
    END AS order_category
FROM orders;

Tip: Use parentheses around complex logical expressions to make the intended evaluation clear.

11. Using CASE with BETWEEN and IN

CASE conditions can use BETWEEN to check a range and IN to match one value from a list.

11.1 Using BETWEEN

SELECT
    employee_name,
    salary,
    CASE
        WHEN salary BETWEEN 0 AND 29999 THEN 'Entry Level'
        WHEN salary BETWEEN 30000 AND 69999 THEN 'Mid Level'
        ELSE 'Senior Salary Range'
    END AS salary_level
FROM employees;

BETWEEN includes both endpoints. The example assumes salary values are non-negative and the listed ranges are suitable for the business rules.

11.2 Using IN

SELECT
    order_id,
    payment_method,
    CASE
        WHEN payment_method IN ('Credit Card', 'Debit Card')
            THEN 'Card Payment'
        WHEN payment_method IN ('UPI', 'Net Banking')
            THEN 'Digital Payment'
        ELSE 'Other Payment'
    END AS payment_category
FROM orders;

IN makes it easier to test several possible values without writing multiple OR conditions.

12. Using CASE in SELECT Statements

The SELECT clause is one of the most common places to use CASE. It lets you return original columns along with dynamically calculated labels or values.

Example: Order Priority

SELECT
    order_id,
    order_date,
    total_amount,
    CASE
        WHEN total_amount >= 50000 THEN 'High Priority'
        WHEN total_amount >= 20000 THEN 'Medium Priority'
        ELSE 'Normal Priority'
    END AS order_priority
FROM orders;

The original order information remains unchanged. The CASE expression adds a calculated order_priority column to the result set.

13. Using CASE with Calculated Values

CASE can return numbers as well as text. This is useful for discounts, commissions, charges, bonuses, and other conditional calculations.

Example: Calculating Discounts

SELECT
    product_name,
    price,
    CASE
        WHEN price >= 20000 THEN price * 0.15
        WHEN price >= 10000 THEN price * 0.10
        WHEN price >= 5000 THEN price * 0.05
        ELSE 0
    END AS discount_amount
FROM products;

In this example, products priced at 20,000 or more receive a 15% discount, products priced from 10,000 to less than 20,000 receive a 10% discount, and products priced from 5,000 to less than 10,000 receive a 5% discount.

Example: Calculating Final Price

SELECT
    product_name,
    price,
    CASE
        WHEN price >= 20000 THEN price * 0.85
        WHEN price >= 10000 THEN price * 0.90
        WHEN price >= 5000 THEN price * 0.95
        ELSE price
    END AS final_price
FROM products;

These expressions calculate values in the query result. They do not permanently update the stored price in the products table.

14. Using CASE with Aggregate Functions

CASE expressions can be combined with aggregate functions such as SUM(), COUNT(), AVG(), MIN(), and MAX(). One important technique is conditional aggregation, where a query calculates totals or counts for specific categories.

14.1 Conditional Counting with SUM and CASE

SELECT
    SUM(CASE WHEN status = 'Completed' THEN 1 ELSE 0 END)
        AS completed_orders,
    SUM(CASE WHEN status = 'Pending' THEN 1 ELSE 0 END)
        AS pending_orders,
    SUM(CASE WHEN status = 'Cancelled' THEN 1 ELSE 0 END)
        AS cancelled_orders
FROM orders;

Each CASE expression returns 1 when the status matches and 0 otherwise. SUM() adds those values to calculate the number of orders in each category.

14.2 Conditional Revenue Calculation

SELECT
    SUM(
        CASE
            WHEN status = 'Completed' THEN total_amount
            ELSE 0
        END
    ) AS completed_revenue
FROM orders;

This query calculates the total amount associated with completed orders only.

14.3 Conditional Average

SELECT
    AVG(
        CASE
            WHEN department = 'IT' THEN salary
            ELSE NULL
        END
    ) AS average_it_salary
FROM employees;

AVG() ignores NULL values, so this expression calculates the average salary for employees in the IT department. Alternatively, a filtered WHERE clause can be used when only the IT department is needed.

15. Using CASE with GROUP BY

CASE can transform individual records into categories that can then be grouped for summary reports. This is useful for salary bands, age groups, sales ranges, and performance levels.

Example: Grouping Employees by Salary Band

SELECT
    CASE
        WHEN salary < 30000 THEN 'Below 30000'
        WHEN salary < 60000 THEN '30000 to 59999'
        ELSE '60000 and Above'
    END AS salary_band,
    COUNT(*) AS employee_count
FROM employees
GROUP BY
    CASE
        WHEN salary < 30000 THEN 'Below 30000'
        WHEN salary < 60000 THEN '30000 to 59999'
        ELSE '60000 and Above'
    END;

This query groups employees by the salary band produced by the CASE expression and counts the employees in each group.

Important Note

When a calculated expression is used in GROUP BY, keep the grouping expression consistent with the category displayed in SELECT. In MySQL, aliases may be used in GROUP BY in many cases, but repeating the expression can make the logic explicit.

16. Using CASE with ORDER BY

CASE can control the sorting order of query results. This is particularly useful when the required order does not follow the natural alphabetical or numerical order.

Example: Custom Status Sorting

SELECT
    order_id,
    status,
    order_date
FROM orders
ORDER BY
    CASE
        WHEN status = 'Pending' THEN 1
        WHEN status = 'Processing' THEN 2
        WHEN status = 'Shipped' THEN 3
        WHEN status = 'Delivered' THEN 4
        WHEN status = 'Cancelled' THEN 5
        ELSE 6
    END,
    order_date DESC;

The query sorts orders by the specified business priority and then sorts orders within each priority by order_date in descending order.

When Custom Sorting Is Useful

• Displaying pending tasks before completed tasks.
• Prioritizing urgent support tickets.
• Showing high-priority orders before low-priority orders.
• Arranging workflow statuses in a defined sequence.
• Sorting products according to business importance.

17. Using CASE with NULL Values

NULL represents a missing or unknown value. Comparisons such as column_name = NULL do not correctly test for NULL. Use IS NULL or IS NOT NULL in CASE conditions.

Example: Checking Missing Phone Numbers

SELECT
    customer_name,
    phone,
    CASE
        WHEN phone IS NULL THEN 'Phone Not Available'
        ELSE 'Phone Available'
    END AS phone_status
FROM customers;

Example: Classifying Missing Salaries

SELECT
    employee_name,
    salary,
    CASE
        WHEN salary IS NULL THEN 'Salary Not Entered'
        WHEN salary >= 60000 THEN 'High Salary'
        WHEN salary >= 30000 THEN 'Medium Salary'
        ELSE 'Low Salary'
    END AS salary_status
FROM employees;

Checking for NULL first makes the intended behavior explicit. If salary is NULL, the query returns 'Salary Not Entered' rather than assigning a misleading salary category.

18. Using CASE with String Conditions

CASE expressions can evaluate text values to categorize customers, products, payment methods, or account statuses.

Example: Customer Type

SELECT
    customer_name,
    customer_type,
    CASE
        WHEN customer_type = 'Premium' THEN 'Priority Support'
        WHEN customer_type = 'Regular' THEN 'Standard Support'
        WHEN customer_type = 'Trial' THEN 'Trial Support'
        ELSE 'Support Type Not Assigned'
    END AS support_level
FROM customers;

Text comparisons may depend on the collation of the column or expression, which determines case sensitivity and other comparison behavior. If matching must be case-sensitive, choose an appropriate collation or use a deliberate comparison strategy.

19. Nested CASE Expressions

A nested CASE expression is a CASE expression placed inside another CASE expression. It can represent multi-level decision logic, but it should be used only when the added complexity is justified.

Example: Classifying Employee Performance

SELECT
    employee_name,
    department,
    performance_score,
    CASE
        WHEN performance_score >= 80 THEN
            CASE
                WHEN department = 'Sales' THEN 'Sales Top Performer'
                WHEN department = 'IT' THEN 'Technical Top Performer'
                ELSE 'High Performer'
            END
        WHEN performance_score >= 50 THEN 'Average Performer'
        ELSE 'Needs Improvement'
    END AS performance_category
FROM employees;

Explanation: Employees with scores of 80 or higher are further classified according to department. Employees scoring from 50 to less than 80 are classified as average performers, while lower scores are classified as needing improvement.

Best Practices for Nested CASE

• Indent each nested CASE block clearly.
• Use meaningful aliases for calculated columns.
• Avoid unnecessary nesting when a single searched CASE can handle the conditions.
• Test each decision path with representative data.

20. CASE Expressions with Date Conditions

CASE can classify dates according to business rules, such as whether an order is overdue, due soon, or completed. Date comparisons should use appropriate date and time values.

Example: Delivery Status

SELECT
    order_id,
    expected_delivery_date,
    actual_delivery_date,
    CASE
        WHEN actual_delivery_date IS NOT NULL THEN 'Delivered'
        WHEN expected_delivery_date < CURRENT_DATE() THEN 'Overdue'
        WHEN expected_delivery_date = CURRENT_DATE() THEN 'Due Today'
        ELSE 'Upcoming'
    END AS delivery_status
FROM orders;

The query first checks whether an actual delivery date exists. If the order has not been delivered, it compares the expected date with the current date to determine its status.

Note: CURRENT_DATE() uses the current date according to the MySQL session environment. For timestamp-based deadlines, consider whether time-of-day and time-zone behavior must also be taken into account.

21. CASE Expressions Compared with IF()

MySQL provides both CASE expressions and the IF() function. Both can return different values based on conditions, but CASE is generally more convenient for multiple branches.

CASE Example

SELECT
    employee_name,
    CASE
        WHEN salary >= 60000 THEN 'High'
        WHEN salary >= 30000 THEN 'Medium'
        ELSE 'Low'
    END AS salary_level
FROM employees;

IF() Example

SELECT
    employee_name,
    IF(salary >= 60000, 'High', 'Not High') AS salary_level
FROM employees;

Use CASE when there are several conditions or when the decision rules need to be easy to read and maintain.

22. CASE Expressions Compared with WHERE

CASE and WHERE have different purposes. CASE returns a value based on conditions, while WHERE filters rows from the result set.

Using CASE to Classify

SELECT
    employee_name,
    salary,
    CASE
        WHEN salary >= 50000 THEN 'High'
        ELSE 'Standard'
    END AS salary_category
FROM employees;

Using WHERE to Filter

SELECT
    employee_name,
    salary
FROM employees
WHERE salary >= 50000;

The first query returns all employees and classifies them. The second query returns only employees whose salary is at least 50,000.

23. Sample Database Setup for Practice

Use the following example table to practice CASE expressions. Run the CREATE TABLE and INSERT statements in a MySQL database before running the examples that follow.

23.1 Create the Employees Table

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100),
    department VARCHAR(50),
    salary DECIMAL(10,2),
    experience_years INT
);

23.2 Insert Sample Records

INSERT INTO employees
(employee_id, employee_name, department, salary, experience_years)
VALUES
(1, 'Rahul', 'IT', 85000, 7),
(2, 'Priya', 'HR', 45000, 4),
(3, 'Amit', 'Finance', 32000, 2),
(4, 'Neha', 'IT', 62000, 5),
(5, 'Karan', 'Sales', 28000, 1),
(6, 'Simran', 'Finance', 72000, 6),
(7, 'Dev', 'HR', 25000, 1),
(8, 'Ananya', 'Sales', 55000, 3);

23.3 Verify the Records

SELECT * FROM employees;

This query displays all records in the employees table. You can now apply CASE expressions to classify salaries, departments, and employee experience.

24. Practical Examples Using the Sample Table

Example 1: Classifying Salary Levels

SELECT
    employee_name,
    salary,
    CASE
        WHEN salary >= 70000 THEN 'High'
        WHEN salary >= 40000 THEN 'Medium'
        ELSE 'Low'
    END AS salary_level
FROM employees;

Example 2: Classifying Experience

SELECT
    employee_name,
    experience_years,
    CASE
        WHEN experience_years >= 6 THEN 'Experienced'
        WHEN experience_years >= 3 THEN 'Intermediate'
        ELSE 'Beginner'
    END AS experience_level
FROM employees;

Example 3: Department Labels

SELECT
    employee_name,
    department,
    CASE department
        WHEN 'IT' THEN 'Technology'
        WHEN 'HR' THEN 'Human Resources'
        WHEN 'Finance' THEN 'Financial Operations'
        WHEN 'Sales' THEN 'Sales and Business Development'
        ELSE 'Other'
    END AS department_description
FROM employees;

Example 4: Salary-Based Bonus

SELECT
    employee_name,
    salary,
    CASE
        WHEN salary >= 70000 THEN salary * 0.15
        WHEN salary >= 40000 THEN salary * 0.10
        ELSE salary * 0.05
    END AS estimated_bonus
FROM employees;

The estimated_bonus is calculated in the query result only. It does not update any permanent employee record.

Example 5: Counting Employees by Salary Level

SELECT
    CASE
        WHEN salary >= 70000 THEN 'High'
        WHEN salary >= 40000 THEN 'Medium'
        ELSE 'Low'
    END AS salary_level,
    COUNT(*) AS employee_count
FROM employees
GROUP BY
    CASE
        WHEN salary >= 70000 THEN 'High'
        WHEN salary >= 40000 THEN 'Medium'
        ELSE 'Low'
    END;

25. Common Errors When Using CASE

26. Troubleshooting CASE Expressions

Problem 1: All Rows Display the Same Category

Possible cause: An early condition is too broad and matches nearly every row.

Incorrect example:

CASE
    WHEN salary > 0 THEN 'Employed'
    WHEN salary > 50000 THEN 'High Salary'
    ELSE 'Other'
END

Every positive salary matches the first condition, so the second condition is never reached.

Corrected example:

CASE
    WHEN salary > 50000 THEN 'High Salary'
    WHEN salary > 0 THEN 'Employed'
    ELSE 'Other'
END

Problem 2: Unexpected NULL Results

Possible cause: No condition matches and no ELSE clause is defined.

Solution: Add a suitable ELSE result, or revise the conditions to cover the expected values.

Problem 3: Incorrect Results for Missing Values

Possible cause: A condition does not account for NULL values.

Solution: Add an explicit condition using IS NULL when missing data requires special treatment.

Problem 4: Incorrect Category Boundaries

Possible cause: Conditions use inconsistent operators, leaving gaps or creating overlapping ranges.

Solution: Write down the intended minimum and maximum values for each category, then test boundary values such as 29,999, 30,000, and 30,001.

Problem 5: Unexpected Sorting

Possible cause: The CASE expression in ORDER BY assigns priorities in an unintended order.

Solution: Verify the numeric priority assigned to each category and add a secondary sorting column if needed.

27. Performance and Best Practices

• Order conditions carefully: Put high-priority or more specific conditions before broader conditions.
• Use meaningful aliases: Names such as salary_level, order_priority, and payment_category make results easier to understand.
• Always review ELSE behavior: Decide how unmatched values should be handled.
• Handle NULL explicitly: Use IS NULL and IS NOT NULL when missing data matters.
• Keep conditions readable: Use indentation and line breaks for multi-branch expressions.
• Avoid excessive nesting: Deeply nested CASE expressions can be difficult to maintain.
• Test boundaries: Check values immediately below, at, and above each threshold.
• Keep output types consistent: Return compatible data types across THEN and ELSE branches.
• Use WHERE for row filtering: Do not use CASE when the real requirement is to exclude rows.
• Use indexes appropriately: Applying CASE to a column in a filtering or sorting expression may prevent straightforward use of an index for that expression. Check the execution plan for performance-critical queries.
• Avoid unnecessary repeated logic: If a complicated classification is reused across many reports, consider a view or another maintainable design.
• Validate business rules: Confirm that categories and calculations reflect the actual requirements.

28. Real-World Applications of CASE Expressions

29. Practice Exercises

Complete the following exercises using the employees table or create additional tables where required.

1. Write a query to classify employees with salaries of 70,000 or above as 'High Salary', salaries from 40,000 to less than 70,000 as 'Medium Salary', and all other salaries as 'Low Salary'.
2. Write a query to classify employees with six or more years of experience as 'Senior', three to five years as 'Intermediate', and fewer than three years as 'Junior'.
3. Use a simple CASE expression to display full department names for IT, HR, Finance, and Sales.
4. Write a query that assigns a 15% bonus to employees earning at least 70,000, a 10% bonus to employees earning at least 40,000, and a 5% bonus to other employees.
5. Use CASE with SUM() to count employees whose salary is at least 60,000.
6. Use CASE with ORDER BY to display IT employees first, followed by Finance, HR, and Sales.
7. Write a query that classifies salary values as 'Not Entered' when salary is NULL.
8. Create a custom CASE expression that classifies employees as 'Eligible' when salary is at least 50,000 and experience is at least three years.
9. Write a query that counts employees in each salary category.
10. Use CASE to return 'Long Service' for employees with at least eight years of experience and 'Regular Service' for all others.

30. Mini Project: Employee Classification and Reporting

Project Objective

Create a SQL report that categorizes employees according to salary, experience, department, and estimated bonus. Use CASE expressions to produce readable categories and aggregate summaries.

Requirements

• Display employee ID, employee name, department, salary, and experience.
• Classify salary as High, Medium, or Low.
• Classify experience as Senior, Intermediate, or Junior.
• Calculate an estimated bonus using salary-based rules.
• Use custom sorting to display departments in a specified order.
• Generate a summary showing the number of employees in each salary category.

Sample Solution: Employee Detail Report

SELECT
    employee_id,
    employee_name,
    department,
    salary,
    experience_years,
    CASE
        WHEN salary >= 70000 THEN 'High Salary'
        WHEN salary >= 40000 THEN 'Medium Salary'
        ELSE 'Low Salary'
    END AS salary_category,
    CASE
        WHEN experience_years >= 6 THEN 'Senior'
        WHEN experience_years >= 3 THEN 'Intermediate'
        ELSE 'Junior'
    END AS experience_category,
    CASE
        WHEN salary >= 70000 THEN salary * 0.15
        WHEN salary >= 40000 THEN salary * 0.10
        ELSE salary * 0.05
    END AS estimated_bonus
FROM employees
ORDER BY
    CASE department
        WHEN 'IT' THEN 1
        WHEN 'Finance' THEN 2
        WHEN 'HR' THEN 3
        WHEN 'Sales' THEN 4
        ELSE 5
    END,
    employee_name;

Sample Solution: Salary Category Summary

SELECT
    CASE
        WHEN salary >= 70000 THEN 'High Salary'
        WHEN salary >= 40000 THEN 'Medium Salary'
        ELSE 'Low Salary'
    END AS salary_category,
    COUNT(*) AS employee_count,
    AVG(salary) AS average_salary
FROM employees
GROUP BY
    CASE
        WHEN salary >= 70000 THEN 'High Salary'
        WHEN salary >= 40000 THEN 'Medium Salary'
        ELSE 'Low Salary'
    END;

Expected Learning Outcomes

• Apply CASE expressions to real-world employee records.
• Generate calculated columns without modifying stored records.
• Use conditional logic for summaries and custom sorting.
• Combine CASE with aggregate functions and grouping.
• Build reports that are easier for business users to understand.

31. MySQL CASE Interview Questions and Answers

Q1. What is a CASE expression in MySQL?

Answer: A CASE expression evaluates conditions and returns a result based on the first matching condition. It is used to implement conditional logic inside SQL queries.

Q2. What are the main types of CASE expressions?

Answer: MySQL supports simple CASE, which compares an expression with specific values, and searched CASE, which evaluates independent conditions.

Q3. What is the purpose of WHEN?

Answer: WHEN specifies a value or condition to evaluate in the CASE expression.

Q4. What does THEN do?

Answer: THEN specifies the result returned when the associated WHEN condition matches.

Q5. Is ELSE mandatory?

Answer: No. ELSE is optional. If no condition matches and ELSE is omitted, the CASE expression returns NULL.

Q6. What is the purpose of END?

Answer: END marks the end of a CASE expression.

Q7. Can CASE be used in a SELECT statement?

Answer: Yes. It is commonly used in SELECT to generate calculated columns, labels, and conditional values.

Q8. Can CASE be used with aggregate functions?

Answer: Yes. CASE can be combined with SUM(), COUNT(), and AVG() to perform conditional aggregation and calculate category-specific totals or averages.

Q9. Can CASE be used in ORDER BY?

Answer: Yes. It can assign custom sorting priorities to statuses, categories, and other values.

Q10. What happens when no CASE condition matches?

Answer: MySQL returns the ELSE result if one is specified. Otherwise, it returns NULL.

Q11. What is the difference between simple CASE and searched CASE?

Answer: Simple CASE compares one expression with possible values. Searched CASE evaluates independent conditions such as salary >= 50000 or status = 'Pending'.

Q12. How do you check NULL in a CASE expression?

Answer: Use IS NULL or IS NOT NULL, such as WHEN salary IS NULL THEN 'Not Entered'.

Q13. Can CASE return numbers instead of strings?

Answer: Yes. CASE can return numbers, strings, dates, or other compatible values depending on the expressions used in its branches.

Q14. Can CASE expressions be nested?

Answer: Yes. A CASE expression can appear inside another CASE expression to represent multi-level decision logic.

Q15. What is the difference between CASE and WHERE?

Answer: CASE calculates a result value, while WHERE filters rows from the query result.

Q16. Why is condition order important?

Answer: CASE returns the result associated with the first matching condition. If a broad condition appears first, more specific conditions later may never be reached.

Q17. Can CASE be used with GROUP BY?

Answer: Yes. CASE can create categories that are grouped and summarized with aggregate functions.

Q18. Can CASE calculate a discount?

Answer: Yes. A CASE expression can select different discount rates or discount amounts based on product prices or customer categories.

Q19. Does CASE permanently change table data?

Answer: No. A CASE expression in a SELECT query calculates output values only. Permanent changes require an appropriate data-modification statement, such as UPDATE.

Q20. What are the main best practices for CASE expressions?

Answer: Use clear conditions, order branches carefully, include an appropriate ELSE clause, handle NULL values explicitly, test range boundaries, and keep nested logic readable.

32. Quick Revision Notes

• CASE applies conditional logic in MySQL queries.
• Simple CASE compares one expression against multiple values.
• Searched CASE evaluates independent Boolean conditions.
• WHEN defines a condition, and THEN defines its result.
• ELSE provides a fallback result and is optional.
• END closes the CASE expression.
• Use aliases to name calculated columns.
• CASE can return text, numbers, and other compatible values.
• CASE works with SELECT, ORDER BY, GROUP BY, and aggregate functions.
• Use IS NULL and IS NOT NULL to check missing values.
• Condition order matters because the first matching branch is selected.
• Use WHERE to filter rows rather than merely classify them.
• Test boundary values and make sure every category follows the intended rules.

33. Final Checklist

• Understand the difference between simple and searched CASE.
• Write correct CASE, WHEN, THEN, ELSE, and END syntax.
• Classify records based on numerical ranges and text values.
• Use logical operators within searched CASE conditions.
• Handle NULL values correctly.
• Calculate conditional discounts, bonuses, and totals.
• Combine CASE with aggregate functions and GROUP BY.
• Sort results using custom CASE-based priorities.
• Identify and correct common CASE expression errors.
• Complete the exercises and employee classification mini project.

34. Conclusion

CASE expressions are an essential MySQL feature for implementing conditional logic directly in SQL queries. They help transform raw data into meaningful categories, calculate conditional values, classify records, create business-friendly reports, and control sorting order.

By understanding simple CASE and searched CASE expressions, and by combining CASE with aggregate functions, GROUP BY, ORDER BY, and NULL checks, learners can build flexible and readable database queries. Correct condition ordering, clear fallback values, consistent data types, and careful testing are key to producing accurate results.

To continue learning SQL concepts and practical database skills, explore the MySQL Training Course at JustAcademy. You can also register for a course demo to learn more about the training.