Conditional Functions Using IF and CASE in MySQL – Detailed Notes

Course: MySQL Training Course

Topic: Conditional Functions Using IF and CASE

Course Link: MySQL Training Course

Demo Link: Register for Course Demo

1. Introduction to Conditional Functions in MySQL

Conditional functions in MySQL allow queries to return different results depending on whether specified conditions are satisfied. They help apply decision-making logic directly inside SQL statements without requiring a separate programming language.

The two important conditional constructs covered in this topic are IF() and CASE. The IF() function is useful when a query needs to choose between two possible results. CASE is more flexible and can evaluate multiple conditions or compare one expression against several possible values.

Conditional logic is frequently used in business applications, sales reports, employee management systems, inventory databases, online stores, and financial reporting.

For example, an online store may classify an order as "High Value" when its amount exceeds 10,000 and "Regular Value" otherwise.

SELECT
    12000 AS order_amount,
    IF(12000 > 10000, 'High Value', 'Regular Value') AS order_category;

Expected output:

2. Learning Objectives

After completing this topic, you will be able to:

• Understand conditional logic in MySQL.
• Explain the purpose and syntax of the IF() function.
• Use IF() to return results based on a condition.
• Understand the searched CASE and simple CASE expressions.
• Apply multiple conditions using WHEN and THEN.
• Use ELSE to handle unmatched conditions.
• Apply conditional logic in SELECT, WHERE, ORDER BY, and aggregate queries.
• Classify records according to business rules.
• Handle NULL values carefully in conditional expressions.
• Choose between IF() and CASE based on the requirements of a query.

3. What Is Conditional Logic?

Conditional logic means making a decision based on whether a condition is true, false, or unknown. In MySQL, conditional expressions can evaluate data and return a result that matches the required rule.

Consider a student database. If a student's score is 40 or above, the student passes. Otherwise, the student fails.

SELECT
    75 AS marks,
    CASE
        WHEN 75 >= 40 THEN 'Pass'
        ELSE 'Fail'
    END AS result;

Output:

Conditional expressions do not necessarily change the original table data. When used in a SELECT query, they usually calculate a result for display. Updating stored values requires an appropriate statement such as UPDATE.

4. Understanding the IF() Function

The IF() function evaluates a condition and returns one value when the condition is true and another value when it is false or NULL.

4.1 Syntax of IF()

IF(condition, value_if_true, value_if_false)

Explanation:

• condition: The expression MySQL evaluates.
• value_if_true: The result returned when the condition is true.
• value_if_false: The result returned when the condition is false or NULL.

4.2 Basic IF() Example

SELECT IF(10 > 5, 'Yes', 'No') AS answer;

Output:

The condition 10 > 5 is true, so MySQL returns the second argument, 'Yes'.

4.3 IF() When a Condition Is False

SELECT IF(10 < 5, 'Yes', 'No') AS answer;

Output:

Since 10 is not less than 5, the function returns the third argument.

4.4 IF() with Numeric Results

IF() can return numeric values instead of text. This is useful for calculating bonuses, discounts, eligibility indicators, and other numeric results.

SELECT
    8500 AS salary,
    IF(8500 >= 8000, 1000, 0) AS bonus;

The bonus is 1000 because the salary meets the specified condition.

4.5 IF() with Text Comparisons

IF() can also evaluate conditions involving strings.

SELECT
    IF('Active' = 'Active', 'Account Enabled', 'Account Disabled')
    AS account_status;

The output is Account Enabled because the two compared strings match under the applicable comparison rules.

4.6 IF() with Column Values

Conditional functions become especially useful when applied to columns in a table.

SELECT
    employee_name,
    salary,
    IF(salary >= 50000, 'High Salary', 'Standard Salary') AS salary_category
FROM employees;

This query classifies each employee based on their salary. It assumes the employees table contains the columns employee_name and salary.

5. Understanding the CASE Expression

The CASE expression evaluates conditions and returns the result associated with the first matching WHEN clause. It can handle multiple possible outcomes in a single SQL expression.

CASE is useful when a query must classify records into several categories, such as salary bands, order priorities, product ratings, student grades, or delivery statuses.

5.1 Syntax of Searched CASE

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN condition3 THEN result3
    ELSE default_result
END

Explanation:

• CASE: Starts the conditional expression.
• WHEN: Specifies a condition to evaluate.
• THEN: Specifies the value returned when that condition is true.
• ELSE: Provides a default result if no WHEN condition matches.
• END: Closes the CASE expression.

5.2 Basic CASE Example

SELECT
    CASE
        WHEN 85 >= 90 THEN 'Excellent'
        WHEN 85 >= 75 THEN 'Very Good'
        WHEN 85 >= 60 THEN 'Good'
        ELSE 'Needs Improvement'
    END AS performance;

Output:

The first condition is false. The second condition is true, so MySQL returns Very Good and does not need to select a later result.

6. Types of CASE Expressions

MySQL supports two common CASE expression forms: searched CASE and simple CASE.

6.1 Searched CASE

Searched CASE evaluates one or more conditions. Each WHEN clause can contain a different comparison or logical expression.

SELECT
    CASE
        WHEN total_amount >= 10000 THEN 'Premium'
        WHEN total_amount >= 5000 THEN 'Standard'
        ELSE 'Basic'
    END AS customer_segment
FROM orders;

This form is useful when each category depends on a different condition.

6.2 Simple CASE

Simple CASE compares a single expression with multiple possible values.

SELECT
    CASE payment_status
        WHEN 'Paid' THEN 'Payment Complete'
        WHEN 'Pending' THEN 'Awaiting Payment'
        WHEN 'Failed' THEN 'Payment Unsuccessful'
        ELSE 'Unknown Status'
    END AS status_description
FROM orders;

This form is useful when one column or expression needs to be matched against several specific values.

6.3 Difference Between Searched CASE and Simple CASE

7. Creating a Sample Database Table

To practice IF() and CASE, create a table containing employee information. The examples in the following sections use this table.

CREATE DATABASE conditional_demo;
USE conditional_demo;

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

INSERT INTO employees
(employee_id, employee_name, department, salary, performance_score, employment_status)
VALUES
(1, 'Aarav', 'IT', 65000, 92, 'Active'),
(2, 'Priya', 'HR', 48000, 78, 'Active'),
(3, 'Rahul', 'Sales', 55000, 65, 'Active'),
(4, 'Neha', 'IT', 75000, 88, 'Inactive'),
(5, 'Karan', 'Finance', 35000, 45, 'Active'),
(6, 'Meera', 'Sales', 90000, 96, 'Active');

Verify the inserted records:

SELECT * FROM employees;

8. Practical Examples Using IF()

8.1 Classifying Employees by Salary

SELECT
    employee_name,
    salary,
    IF(salary >= 50000, 'High Salary', 'Standard Salary') AS salary_category
FROM employees;

Employees earning 50,000 or more are classified as High Salary. All other employees are classified as Standard Salary.

8.2 Checking Employment Status

SELECT
    employee_name,
    employment_status,
    IF(employment_status = 'Active', 'Currently Employed', 'Not Active')
    AS status_description
FROM employees;

This query translates stored status values into more descriptive labels.

8.3 Identifying High Performers

SELECT
    employee_name,
    performance_score,
    IF(performance_score >= 80, 'High Performer', 'Regular Performer')
    AS performance_category
FROM employees;

8.4 Calculating a Conditional Bonus

Suppose employees with a salary of at least 60,000 receive a bonus of 5,000. Other employees receive no bonus.

SELECT
    employee_name,
    salary,
    IF(salary >= 60000, 5000, 0) AS bonus
FROM employees;

8.5 Applying a Conditional Discount

Consider an order amount of 12,000. Customers spending at least 10,000 receive a 10% discount.

SELECT
    12000 AS order_amount,
    IF(12000 >= 10000, 12000 * 0.10, 0) AS discount;

The result is a discount of 1,200. This example demonstrates the calculation only; a real order system should apply the discount rules and rounding policies defined by the business.

9. Practical Examples Using CASE

9.1 Classifying Salaries into Multiple Categories

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

The salary ranges are evaluated from top to bottom. For example, a salary of 90,000 matches the first condition and is classified as Very High.

9.2 Assigning Performance Grades

SELECT
    employee_name,
    performance_score,
    CASE
        WHEN performance_score >= 90 THEN 'A'
        WHEN performance_score >= 80 THEN 'B'
        WHEN performance_score >= 70 THEN 'C'
        WHEN performance_score >= 60 THEN 'D'
        ELSE 'Needs Improvement'
    END AS performance_grade
FROM employees;

This query converts numeric performance scores into meaningful grade labels.

9.3 Classifying Employees by Department

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

This is a simple CASE expression because the department column is compared with specific values.

9.4 Assigning Bonus Percentages

SELECT
    employee_name,
    salary,
    performance_score,
    CASE
        WHEN performance_score >= 90 THEN salary * 0.15
        WHEN performance_score >= 80 THEN salary * 0.10
        WHEN performance_score >= 70 THEN salary * 0.05
        ELSE 0
    END AS performance_bonus
FROM employees;

This example assigns a bonus based on performance score. The percentage rules are illustrative and can be changed to match an organization's actual policy.

9.5 Handling Unknown Status Values

SELECT
    employee_name,
    CASE employment_status
        WHEN 'Active' THEN 'Available'
        WHEN 'Inactive' THEN 'Unavailable'
        ELSE 'Status Requires Review'
    END AS status_label
FROM employees;

The ELSE clause ensures that values not explicitly listed receive a default label.

10. Using IF() and CASE in SELECT Statements

The SELECT statement is one of the most common places to use conditional expressions. It allows calculated labels and values to appear alongside the original table columns.

10.1 Displaying a Conditional Label

SELECT
    employee_name,
    salary,
    IF(salary >= 50000, 'Eligible', 'Not Eligible') AS bonus_eligibility
FROM employees;

10.2 Returning Multiple Categories with CASE

SELECT
    employee_name,
    salary,
    CASE
        WHEN salary >= 80000 THEN 'Tier 1'
        WHEN salary >= 50000 THEN 'Tier 2'
        ELSE 'Tier 3'
    END AS employee_tier
FROM employees;

Conditional expressions can be combined with other calculations in the same SELECT list, making them valuable for reports and dashboards.

11. Using Conditional Expressions in WHERE Clauses

Conditional logic can also be used in filtering expressions. However, a CASE expression in the WHERE clause is not always necessary; ordinary logical conditions are often clearer.

11.1 Filter Records Using a Direct Condition

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

This is generally the simplest way to retrieve employees earning at least 50,000.

11.2 Use CASE to Evaluate a Conditional Rule

SELECT employee_name, salary
FROM employees
WHERE
    CASE
        WHEN department = 'IT' THEN salary >= 60000
        WHEN department = 'HR' THEN salary >= 45000
        ELSE salary >= 50000
    END;

This example applies different salary thresholds depending on the department. A CASE expression can return a condition result for filtering, but complex filtering should be written clearly and tested carefully.

12. Using Conditional Expressions with ORDER BY

CASE can be used to define a custom sorting order instead of relying on the normal alphabetical or numerical order of a column.

12.1 Custom Department Sorting

SELECT employee_name, department, salary
FROM employees
ORDER BY
    CASE department
        WHEN 'IT' THEN 1
        WHEN 'HR' THEN 2
        WHEN 'Finance' THEN 3
        WHEN 'Sales' THEN 4
        ELSE 5
    END,
    employee_name;

This sorts employees by the specified department order and then alphabetically by employee name.

12.2 Sorting by Employment Status

SELECT employee_name, employment_status
FROM employees
ORDER BY
    CASE employment_status
        WHEN 'Active' THEN 1
        WHEN 'Inactive' THEN 2
        ELSE 3
    END;

13. Conditional Aggregation Using CASE

Conditional aggregation allows a query to calculate totals or counts for records that meet particular conditions. It is commonly used in business reporting.

13.1 Count Active Employees

SELECT
    COUNT(*) AS total_employees,
    SUM(CASE WHEN employment_status = 'Active' THEN 1 ELSE 0 END)
    AS active_employees
FROM employees;

The SUM() expression adds 1 for each active employee and 0 for every other record.

13.2 Count Employees by Salary Category

SELECT
    SUM(CASE WHEN salary >= 80000 THEN 1 ELSE 0 END) AS very_high_salary,
    SUM(CASE WHEN salary >= 50000 AND salary < 80000 THEN 1 ELSE 0 END)
    AS medium_salary,
    SUM(CASE WHEN salary < 50000 THEN 1 ELSE 0 END) AS lower_salary
FROM employees;

Each expression counts employees in a separate salary range. The category boundaries should be designed so they do not overlap unintentionally.

13.3 Calculate Total Salary by Department

SELECT
    department,
    SUM(salary) AS total_salary,
    SUM(CASE WHEN employment_status = 'Active' THEN salary ELSE 0 END)
    AS active_employee_salary
FROM employees
GROUP BY department;

This query returns the overall salary total and the salary total for active employees in each department.

13.4 Count High Performers by Department

SELECT
    department,
    COUNT(*) AS employee_count,
    SUM(CASE WHEN performance_score >= 80 THEN 1 ELSE 0 END)
    AS high_performer_count
FROM employees
GROUP BY department;

This report helps compare department sizes and the number of high performers.

14. Understanding NULL in Conditional Functions

NULL represents a missing or unknown value. Comparisons involving NULL generally evaluate to UNKNOWN rather than TRUE or FALSE. In an IF() function, a NULL condition follows the false-result branch.

14.1 IF() with NULL

SELECT IF(NULL, 'Yes', 'No') AS result;

The result is No because the condition is NULL, not true.

14.2 CASE with NULL Conditions

SELECT
    CASE
        WHEN NULL THEN 'Matched'
        ELSE 'Not Matched'
    END AS result;

The WHEN condition does not evaluate to true, so the ELSE result is returned.

14.3 Checking for NULL Values Correctly

To check whether a column contains NULL, use IS NULL or IS NOT NULL, rather than using the equals operator.

SELECT
    employee_name,
    CASE
        WHEN salary IS NULL THEN 'Salary Missing'
        WHEN salary >= 50000 THEN 'Salary Meets Threshold'
        ELSE 'Salary Below Threshold'
    END AS salary_check
FROM employees;

This query explicitly handles missing salaries before evaluating the numeric threshold.

15. Difference Between IF() and CASE

Important: IF() is a function, while CASE is a conditional expression. MySQL also has procedural IF and CASE statements for stored programs. Those statements use different syntax and are not the same as the IF() function or CASE expression used in ordinary SELECT queries.

16. Common Mistakes and How to Avoid Them

17. Best Practices for Conditional Functions

• Use IF() for simple conditions with two outcomes.
• Use CASE when there are multiple categories or business rules.
• Write meaningful aliases for calculated columns.
• Order CASE conditions carefully because the first matching WHEN determines the result.
• Include an ELSE clause when unmatched values need a defined result.
• Use IS NULL and IS NOT NULL to handle missing values.
• Keep result types consistent to avoid confusing implicit conversions.
• Test boundary values such as scores of 39, 40, and 41 when implementing pass/fail rules.
• Use ordinary WHERE conditions when they are simpler than a CASE expression.
• Use conditional aggregation to build counts and totals for reports.
• Keep business rules documented so future developers can maintain them.
• Test conditional expressions with real data, including NULL and unexpected values.

18. Real-World Applications of IF() and CASE

19. Practice Exercises

Exercise 1: Pass or Fail

Write an IF() expression that returns Pass when marks are 40 or above and Fail otherwise.

SELECT IF(65 >= 40, 'Pass', 'Fail') AS result;

Exercise 2: Salary Classification

Use IF() to classify salaries of 50,000 or more as High Salary and all others as Standard Salary.

SELECT
    employee_name,
    salary,
    IF(salary >= 50000, 'High Salary', 'Standard Salary') AS salary_category
FROM employees;

Exercise 3: Performance Grades

Use CASE to assign grades based on performance scores: A for 90 or above, B for 80 or above, C for 70 or above, and Needs Improvement otherwise.

SELECT
    employee_name,
    performance_score,
    CASE
        WHEN performance_score >= 90 THEN 'A'
        WHEN performance_score >= 80 THEN 'B'
        WHEN performance_score >= 70 THEN 'C'
        ELSE 'Needs Improvement'
    END AS grade
FROM employees;

Exercise 4: Department Labels

Use simple CASE to convert department codes or names into descriptive labels.

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

Exercise 5: Conditional Bonus

Calculate a 10% bonus for employees whose performance score is at least 80. Return zero for all other employees.

SELECT
    employee_name,
    salary,
    IF(performance_score >= 80, salary * 0.10, 0) AS bonus
FROM employees;

Exercise 6: Count Active Employees

Use conditional aggregation to count active employees.

SELECT
    SUM(CASE WHEN employment_status = 'Active' THEN 1 ELSE 0 END)
    AS active_employees
FROM employees;

20. Mini Project: Employee Performance and Salary Report

Project Objective: Build a report using IF() and CASE to classify employees, calculate bonuses, and summarize performance by department.

Project Requirements

1. Display each employee's name, department, salary, and performance score.
2. Use IF() to classify employees as High Salary or Standard Salary.
3. Use CASE to assign performance grades.
4. Calculate a performance bonus based on score ranges.
5. Translate employment status into descriptive labels.
6. Count active employees in each department.
7. Calculate the total salary paid to active employees in each department.
8. Sort departments by their active employee count.

Example Project Query

SELECT
    employee_name,
    department,
    salary,
    performance_score,
    IF(salary >= 50000, 'High Salary', 'Standard Salary') AS salary_category,
    CASE
        WHEN performance_score >= 90 THEN 'A'
        WHEN performance_score >= 80 THEN 'B'
        WHEN performance_score >= 70 THEN 'C'
        WHEN performance_score >= 60 THEN 'D'
        ELSE 'Needs Improvement'
    END AS performance_grade,
    CASE
        WHEN performance_score >= 90 THEN salary * 0.15
        WHEN performance_score >= 80 THEN salary * 0.10
        WHEN performance_score >= 70 THEN salary * 0.05
        ELSE 0
    END AS bonus,
    CASE employment_status
        WHEN 'Active' THEN 'Currently Employed'
        WHEN 'Inactive' THEN 'Not Active'
        ELSE 'Status Requires Review'
    END AS status_description
FROM employees
ORDER BY department, employee_name;

Department Summary Query

SELECT
    department,
    COUNT(*) AS total_employees,
    SUM(CASE WHEN employment_status = 'Active' THEN 1 ELSE 0 END)
    AS active_employees,
    SUM(CASE WHEN performance_score >= 80 THEN 1 ELSE 0 END)
    AS high_performers,
    SUM(CASE WHEN employment_status = 'Active' THEN salary ELSE 0 END)
    AS active_salary_total
FROM employees
GROUP BY department
ORDER BY active_employees DESC;

This project demonstrates how conditional expressions can support employee reports and management dashboards. The bonus percentages and salary thresholds are examples; real systems should use approved business rules.

21. Interview Questions and Answers

Q1. What is the IF() function in MySQL?

IF() evaluates a condition and returns one value if the condition is true and another value if it is false or NULL.

Q2. What is the syntax of IF()?

IF(condition, value_if_true, value_if_false)

Q3. What is CASE in MySQL?

CASE is a conditional expression that returns a result associated with the first matching WHEN condition.

Q4. What is the difference between IF() and CASE?

IF() is concise for choosing between two results. CASE is generally clearer when several conditions or categories must be evaluated.

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

Simple CASE compares one expression with specified WHEN values. Searched CASE evaluates complete conditions, including range checks and logical expressions.

Q6. Why is ELSE important in CASE?

ELSE defines the default result when none of the WHEN conditions matches. Without ELSE, CASE returns NULL when there is no match.

Q7. Can IF() be used with numbers?

Yes. IF() can return numeric values, such as a bonus amount or discount, based on a condition.

Q8. Can CASE be used in a SELECT statement?

Yes. CASE is commonly used in SELECT to calculate labels, grades, categories, and other derived values.

Q9. How do you handle NULL values in a CASE expression?

Use conditions such as WHEN column_name IS NULL. A simple CASE comparison with a NULL value does not match NULL in the same way as a normal equality comparison.

Q10. Can CASE be used with aggregate functions?

Yes. CASE can be combined with SUM() or COUNT() patterns to calculate conditional totals and counts.

Q11. Does CASE update values stored in a table?

No. CASE in a SELECT expression calculates a result for the query. To modify stored data, use an appropriate UPDATE statement.

Q12. What happens if multiple CASE conditions are true?

The first matching WHEN branch determines the result. Later branches are not selected for that CASE expression.

Q13. Can conditional expressions be used in ORDER BY?

Yes. They can define custom sorting rules, such as placing active employees before inactive employees.

Q14. What is conditional aggregation?

Conditional aggregation combines an expression such as CASE with an aggregate function to calculate values only for rows that meet a condition.

Q15. Is the IF() function the same as the IF statement?

No. IF() is a function used in expressions. The IF statement uses IF...THEN...ELSEIF...ELSE...END IF syntax inside stored programs.

22. Quick Revision Notes

• IF() selects between two possible results.
• IF() syntax: IF(condition, value_if_true, value_if_false).
• CASE supports multiple conditions and possible results.
• Searched CASE evaluates full conditions after WHEN.
• Simple CASE compares one expression with multiple WHEN values.
• WHEN defines a condition or comparison value.
• THEN specifies the result for a matching condition.
• ELSE supplies a default result when no condition matches.
• END closes the CASE expression.
• IF() treats a NULL condition as not true and returns its false-result argument.
• Use IS NULL to test for NULL values.
• CASE can be used in SELECT, ORDER BY, WHERE, and conditional aggregation.
• Use IF() for simple decisions and CASE for multiple categories.

23. Best Practices Checklist

• Choose IF() or CASE based on the number and complexity of conditions.
• Keep conditional expressions readable and use clear aliases.
• Order CASE conditions carefully.
• Include an ELSE clause when a default result is needed.
• Check for NULL values explicitly.
• Test category boundaries and unexpected input values.
• Use consistent result types when possible.
• Prefer simple WHERE conditions when they express the same filtering logic clearly.
• Use conditional aggregation for reports requiring multiple counts or totals.
• Document the business rules behind salary bands, grades, discounts, and bonuses.

24. Conclusion

Conditional functions and expressions are important features of MySQL because they allow SQL queries to apply decision-making logic directly to data. IF() is a convenient option for simple true-or-false decisions, while CASE is ideal for multiple conditions, classifications, and custom business rules.

By practicing these constructs in SELECT queries, filters, sorting operations, and aggregate reports, you can build more flexible database queries for real-world applications. Always test your conditions, handle NULL values correctly, and keep business rules clear and consistent.

To continue learning SQL and database concepts, explore the MySQL Training Course. You can also register for a course demo.