Conditional Logic with IF, ELSEIF, and CASE in MySQL

Course: MySQL Training

Topic: Conditional Logic with IF, ELSEIF, and CASE

Course Link: MySQL Training Course

Demo Link: Register for a Free Course Demo

1. Introduction to Conditional Logic in MySQL

Conditional logic allows a MySQL program to make decisions based on specified conditions. Instead of executing the same statements in every situation, a stored program can evaluate a condition and execute different statements depending on whether the condition is true or false.

MySQL supports several conditional constructs, including IF, ELSEIF, and CASE, for controlling the execution flow of stored procedures, stored functions, triggers, and other compound SQL programs.

For example, a business application might need to:

• Determine whether an employee is eligible for a bonus.
• Assign a performance category based on an employee's score.
• Identify whether an order is pending, shipped, or delivered.
• Calculate a discount according to the total purchase amount.
• Classify a student's result as pass, fail, or distinction.
• Handle different situations when processing database records.

Conditional logic makes database programs more flexible, readable, and useful for real-world applications.

2. Learning Objectives

After completing this topic, you will be able to:

• Understand conditional statements and decision-making in MySQL.
• Differentiate between procedural IF statements and the IF() function.
• Use IF, ELSEIF, and ELSE in stored programs.
• Apply simple and searched CASE statements.
• Understand the difference between a procedural CASE statement and a CASE expression.
• Combine conditions using comparison and logical operators.
• Use conditional logic in stored procedures, functions, and triggers.
• Identify common errors and follow best practices when writing conditional SQL.

3. What Is Conditional Logic?

Conditional logic is a programming technique that evaluates one or more conditions and chooses an appropriate action based on the result.

A condition is an expression that evaluates to true, false, or unknown. In MySQL, comparisons involving NULL often produce an unknown result, which must be handled carefully.

3.1 Example of a Condition

SELECT 100 > 50;

Output:

1

MySQL returns 1 for true and 0 for false in this Boolean-style expression.

Another example:

SELECT 25 > 50;

Output:

0

3.2 Common Comparison Operators

4. Understanding IF, ELSEIF, ELSE, and CASE

MySQL provides different ways to implement conditional decisions. The best choice depends on whether you are controlling the execution of statements inside a stored program or calculating a value inside a SQL query.

Important distinction: The procedural IF ... THEN statement is different from the SQL IF() function. Similarly, the CASE statement controls program flow, while a CASE expression returns a value.

5. The IF Statement in MySQL

The IF statement evaluates a condition. If the condition is true, MySQL executes the statements inside the THEN block. If the condition is false or unknown, it can execute an optional ELSE block.

5.1 Syntax

IF condition THEN
    statements;
ELSE
    alternative_statements;
END IF;

The ELSE section is optional. If it is omitted and the condition is false or unknown, MySQL skips the THEN block and continues after END IF.

5.2 Example: Checking Whether a Number Is Positive

DELIMITER //

CREATE PROCEDURE CheckPositiveNumber(IN p_number INT)
BEGIN
    IF p_number > 0 THEN
        SELECT 'The number is positive' AS message;
    ELSE
        SELECT 'The number is zero or negative' AS message;
    END IF;
END //

DELIMITER ;

Execute the procedure:

CALL CheckPositiveNumber(10);
CALL CheckPositiveNumber(-5);
CALL CheckPositiveNumber(0);

Expected results:

In this example, the procedure accepts an integer input parameter and evaluates whether it is greater than zero. The appropriate message is returned based on the condition.

5.3 Example: Checking Employee Bonus Eligibility

Suppose an organization provides a bonus to employees whose annual salary is at least 600000.

DELIMITER //

CREATE PROCEDURE CheckBonusEligibility(IN p_salary DECIMAL(10,2))
BEGIN
    IF p_salary >= 600000 THEN
        SELECT 'Eligible for bonus' AS eligibility;
    ELSE
        SELECT 'Not eligible for bonus' AS eligibility;
    END IF;
END //

DELIMITER ;

Test the procedure:

CALL CheckBonusEligibility(750000);
CALL CheckBonusEligibility(450000);

The first call returns Eligible for bonus, while the second returns Not eligible for bonus.

5.4 IF with Logical Operators

Multiple conditions can be combined with AND, OR, and NOT.

DELIMITER //

CREATE PROCEDURE CheckDiscountEligibility(
    IN p_amount DECIMAL(10,2),
    IN p_member_status VARCHAR(20)
)
BEGIN
    IF p_amount >= 5000
       AND p_member_status = 'Gold' THEN
        SELECT 'Premium discount applicable' AS message;
    ELSE
        SELECT 'Standard pricing applies' AS message;
    END IF;
END //

DELIMITER ;

Here, both conditions must be true for the premium discount message to appear.

6. The ELSEIF Statement in MySQL

The ELSEIF clause allows a stored program to check multiple conditions in sequence. It is useful when an application must choose among three or more possible outcomes.

6.1 Syntax

IF condition_1 THEN
    statements_1;
ELSEIF condition_2 THEN
    statements_2;
ELSEIF condition_3 THEN
    statements_3;
ELSE
    default_statements;
END IF;

MySQL evaluates the conditions from top to bottom. The statements belonging to the first condition that evaluates to true are executed. The remaining branches are skipped. If no condition is true, the optional ELSE branch runs.

6.2 Example: Grading a Student

Suppose the grading rules are:

• Marks of 90 or above: Grade A.
• Marks of 75 to 89: Grade B.
• Marks of 60 to 74: Grade C.
• Marks of 40 to 59: Grade D.
• Marks below 40: Fail.

DELIMITER //

CREATE PROCEDURE GetStudentGrade(IN p_marks INT)
BEGIN
    IF p_marks >= 90 THEN
        SELECT 'Grade A' AS result;
    ELSEIF p_marks >= 75 THEN
        SELECT 'Grade B' AS result;
    ELSEIF p_marks >= 60 THEN
        SELECT 'Grade C' AS result;
    ELSEIF p_marks >= 40 THEN
        SELECT 'Grade D' AS result;
    ELSE
        SELECT 'Fail' AS result;
    END IF;
END //

DELIMITER ;

Test different values:

CALL GetStudentGrade(95);
CALL GetStudentGrade(82);
CALL GetStudentGrade(68);
CALL GetStudentGrade(45);
CALL GetStudentGrade(30);

Why does the order matter? The first condition is p_marks >= 90. If the value is 95, it matches immediately and the remaining conditions are not evaluated. The next condition only needs to check whether the marks are at least 75 because values of 90 and above have already been handled.

6.3 Example: Employee Performance Classification

DELIMITER //

CREATE PROCEDURE ClassifyPerformance(IN p_score INT)
BEGIN
    IF p_score >= 85 THEN
        SELECT 'Excellent' AS performance;
    ELSEIF p_score >= 70 THEN
        SELECT 'Good' AS performance;
    ELSEIF p_score >= 50 THEN
        SELECT 'Average' AS performance;
    ELSE
        SELECT 'Needs Improvement' AS performance;
    END IF;
END //

DELIMITER ;

Run the procedure:

CALL ClassifyPerformance(92);
CALL ClassifyPerformance(76);
CALL ClassifyPerformance(58);
CALL ClassifyPerformance(35);

This example demonstrates how several score ranges can be handled using one conditional structure.

6.4 Example: Shipping Charges

DELIMITER //

CREATE PROCEDURE CalculateShipping(IN p_order_amount DECIMAL(10,2))
BEGIN
    IF p_order_amount >= 5000 THEN
        SELECT 0.00 AS shipping_charge;
    ELSEIF p_order_amount >= 2000 THEN
        SELECT 50.00 AS shipping_charge;
    ELSEIF p_order_amount >= 500 THEN
        SELECT 100.00 AS shipping_charge;
    ELSE
        SELECT 150.00 AS shipping_charge;
    END IF;
END //

DELIMITER ;

The procedure returns a shipping charge based on the order amount. In a production application, the thresholds and fees should reflect the organization's actual shipping policy.

7. Understanding the CASE Statement

The CASE statement provides another way to implement conditional logic in stored programs. It can be particularly readable when many alternative conditions need to be handled.

MySQL supports two forms of the procedural CASE statement:

1. Simple CASE: Compares one expression with a list of possible values.
2. Searched CASE: Evaluates a sequence of Boolean conditions.

8. Simple CASE Statement

A simple CASE statement evaluates one expression and compares it with the values specified in the WHEN clauses.

8.1 Syntax

CASE expression
    WHEN value_1 THEN
        statements_1;
    WHEN value_2 THEN
        statements_2;
    WHEN value_3 THEN
        statements_3;
    ELSE
        default_statements;
END CASE;

The expression is evaluated and compared with each WHEN value. The first matching branch is executed. If there is no match, the optional ELSE branch executes. If there is no matching branch and no ELSE, MySQL raises a case-not-found error in a stored-program CASE statement.

8.2 Example: Identifying a Day of the Week

Suppose an application uses numbers from 1 to 7 to represent the days of the week.

DELIMITER //

CREATE PROCEDURE GetDayName(IN p_day_number INT)
BEGIN
    CASE p_day_number
        WHEN 1 THEN
            SELECT 'Monday' AS day_name;
        WHEN 2 THEN
            SELECT 'Tuesday' AS day_name;
        WHEN 3 THEN
            SELECT 'Wednesday' AS day_name;
        WHEN 4 THEN
            SELECT 'Thursday' AS day_name;
        WHEN 5 THEN
            SELECT 'Friday' AS day_name;
        WHEN 6 THEN
            SELECT 'Saturday' AS day_name;
        WHEN 7 THEN
            SELECT 'Sunday' AS day_name;
        ELSE
            SELECT 'Invalid day number' AS day_name;
    END CASE;
END //

DELIMITER ;

Test the procedure:

CALL GetDayName(1);
CALL GetDayName(5);
CALL GetDayName(7);
CALL GetDayName(10);

8.3 Example: Order Status Processing

An order management application can use a simple CASE statement to process different order statuses.

DELIMITER //

CREATE PROCEDURE DescribeOrderStatus(IN p_status VARCHAR(20))
BEGIN
    CASE p_status
        WHEN 'Pending' THEN
            SELECT 'Order is awaiting processing' AS description;
        WHEN 'Processing' THEN
            SELECT 'Order is being prepared' AS description;
        WHEN 'Shipped' THEN
            SELECT 'Order has been dispatched' AS description;
        WHEN 'Delivered' THEN
            SELECT 'Order has been delivered' AS description;
        WHEN 'Cancelled' THEN
            SELECT 'Order has been cancelled' AS description;
        ELSE
            SELECT 'Unknown order status' AS description;
    END CASE;
END //

DELIMITER ;

Execute the procedure with different status values to test each branch.

9. Searched CASE Statement

A searched CASE statement evaluates one or more conditions rather than comparing a single expression with fixed values. It is suitable for ranges, comparisons, and combinations of logical conditions.

9.1 Syntax

CASE
    WHEN condition_1 THEN
        statements_1;
    WHEN condition_2 THEN
        statements_2;
    WHEN condition_3 THEN
        statements_3;
    ELSE
        default_statements;
END CASE;

Each WHEN clause contains a condition. MySQL executes the statements for the first condition that evaluates to true. If no condition is true, the optional ELSE branch executes. Without a matching branch or an ELSE, a stored-program CASE statement raises a case-not-found error.

9.2 Example: Classifying Salary

DELIMITER //

CREATE PROCEDURE ClassifySalary(IN p_salary DECIMAL(10,2))
BEGIN
    CASE
        WHEN p_salary >= 100000 THEN
            SELECT 'High Salary' AS category;
        WHEN p_salary >= 50000 THEN
            SELECT 'Medium Salary' AS category;
        WHEN p_salary >= 25000 THEN
            SELECT 'Entry-Level Salary' AS category;
        ELSE
            SELECT 'Below Defined Salary Range' AS category;
    END CASE;
END //

DELIMITER ;

Test the procedure:

CALL ClassifySalary(120000);
CALL ClassifySalary(65000);
CALL ClassifySalary(30000);
CALL ClassifySalary(18000);

The searched CASE structure evaluates salary ranges in sequence and executes the first matching branch.

9.3 Example: Checking Product Stock

DELIMITER //

CREATE PROCEDURE CheckProductStock(IN p_quantity INT)
BEGIN
    CASE
        WHEN p_quantity IS NULL THEN
            SELECT 'Stock quantity is unknown' AS stock_status;
        WHEN p_quantity < 0 THEN
            SELECT 'Invalid stock quantity' AS stock_status;
        WHEN p_quantity = 0 THEN
            SELECT 'Out of stock' AS stock_status;
        WHEN p_quantity <= 10 THEN
            SELECT 'Low stock' AS stock_status;
        ELSE
            SELECT 'Stock available' AS stock_status;
    END CASE;
END //

DELIMITER ;

This procedure handles NULL, invalid, zero, low, and sufficient stock quantities. Checking for NULL explicitly is important because ordinary comparisons with NULL do not evaluate to true.

10. CASE Expression in MySQL Queries

A CASE expression differs from a procedural CASE statement. The expression returns a value and can be used in SQL statements such as SELECT, UPDATE, and ORDER BY.

10.1 Searched CASE Expression

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

This query returns each employee's name and salary along with a calculated salary category. It does not create a stored procedure or execute a procedural block.

10.2 Simple CASE Expression

SELECT
    order_id,
    status,
    CASE status
        WHEN 'Pending' THEN 'Awaiting action'
        WHEN 'Shipped' THEN 'On the way'
        WHEN 'Delivered' THEN 'Completed'
        ELSE 'Other status'
    END AS status_description
FROM orders;

This query maps each order status to a more descriptive label.

10.3 Using CASE in an UPDATE Statement

A CASE expression can update several records with different values in one statement.

UPDATE employees
SET bonus =
    CASE
        WHEN salary >= 100000 THEN 15000
        WHEN salary >= 70000 THEN 10000
        WHEN salary >= 40000 THEN 5000
        ELSE 0
    END;

This statement calculates a bonus according to salary ranges for all rows in the table. Use a suitable WHERE clause if only certain employees should be updated. Before running an UPDATE in a production database, verify the target rows and test the business rules.

11. The IF() Function in MySQL

MySQL also provides an IF() function for returning one of two values based on a condition. This function is different from the procedural IF ... THEN ... ELSE ... END IF statement.

11.1 Syntax

IF(condition, value_if_true, value_if_false)

11.2 Example

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

Output:

result
Yes

11.3 Example: Pass or Fail

SELECT
    student_name,
    marks,
    IF(marks >= 40, 'Pass', 'Fail') AS result
FROM students;

This query calculates a result for every student without requiring a stored procedure.

11.4 IF() Function and NULL

When the condition evaluates to zero or NULL, the IF() function returns its third argument. Therefore, if missing marks should be treated as an unknown result instead of a failure, handle NULL explicitly.

SELECT
    marks,
    IF(marks IS NULL, 'Not Available',
       IF(marks >= 40, 'Pass', 'Fail')) AS result
FROM students;

12. Difference Between IF, ELSEIF, and CASE

13. Combining Conditional Logic with Stored Functions

A stored function can use conditional statements to calculate and return a single value. MySQL stored-function parameters are input parameters; stored functions do not use OUT or INOUT parameters. A function must declare a return type and return a value of a compatible type.

13.1 Example: Calculating a Discount Percentage

DELIMITER //

CREATE FUNCTION GetDiscountPercentage(p_amount DECIMAL(10,2))
RETURNS DECIMAL(5,2)
DETERMINISTIC
NO SQL
BEGIN
    DECLARE v_discount DECIMAL(5,2);

    IF p_amount >= 10000 THEN
        SET v_discount = 15.00;
    ELSEIF p_amount >= 5000 THEN
        SET v_discount = 10.00;
    ELSEIF p_amount >= 2000 THEN
        SET v_discount = 5.00;
    ELSE
        SET v_discount = 0.00;
    END IF;

    RETURN v_discount;
END //

DELIMITER ;

Call the function in a query:

SELECT GetDiscountPercentage(12000) AS discount_percentage;
SELECT GetDiscountPercentage(6000) AS discount_percentage;
SELECT GetDiscountPercentage(1000) AS discount_percentage;

The function returns a discount percentage based on the supplied amount. Its DETERMINISTIC declaration is appropriate only when the function's result depends solely on its input and not on changing external data or state.

13.2 Example: Returning a Grade from a Function

DELIMITER //

CREATE FUNCTION GetGrade(p_marks INT)
RETURNS VARCHAR(20)
DETERMINISTIC
NO SQL
BEGIN
    DECLARE v_grade VARCHAR(20);

    CASE
        WHEN p_marks IS NULL THEN
            SET v_grade = 'Not Available';
        WHEN p_marks >= 90 THEN
            SET v_grade = 'Grade A';
        WHEN p_marks >= 75 THEN
            SET v_grade = 'Grade B';
        WHEN p_marks >= 60 THEN
            SET v_grade = 'Grade C';
        WHEN p_marks >= 40 THEN
            SET v_grade = 'Grade D';
        ELSE
            SET v_grade = 'Fail';
    END CASE;

    RETURN v_grade;
END //

DELIMITER ;

Example usage:

SELECT GetGrade(92) AS grade;
SELECT GetGrade(72) AS grade;
SELECT GetGrade(NULL) AS grade;

Notice that the function assigns the appropriate value to a local variable and returns it using RETURN. It does not use a SELECT statement as a substitute for the required return value.

14. Conditional Logic in Stored Procedures with Table Data

Conditional statements are useful when a stored procedure retrieves data, evaluates a value, and then decides what action to take.

14.1 Example: Employee Salary Evaluation

Assume an employees table contains the following columns:

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

Insert sample records:

INSERT INTO employees (employee_id, employee_name, salary)
VALUES
(1, 'Aarav', 85000),
(2, 'Meera', 55000),
(3, 'Kabir', 35000);

Create a procedure that evaluates an employee's salary:

DELIMITER //

CREATE PROCEDURE EvaluateEmployeeSalary(IN p_employee_id INT)
BEGIN
    DECLARE v_salary DECIMAL(10,2);

    SELECT salary
    INTO v_salary
    FROM employees
    WHERE employee_id = p_employee_id;

    IF v_salary >= 80000 THEN
        SELECT 'Senior Salary Band' AS salary_band;
    ELSEIF v_salary >= 50000 THEN
        SELECT 'Middle Salary Band' AS salary_band;
    ELSE
        SELECT 'Junior Salary Band' AS salary_band;
    END IF;
END //

DELIMITER ;

Run the procedure:

CALL EvaluateEmployeeSalary(1);
CALL EvaluateEmployeeSalary(2);
CALL EvaluateEmployeeSalary(3);

Important: If the supplied employee ID does not exist, the SELECT statement may leave the local variable NULL, and the procedure can classify it incorrectly. Production code should initialize variables and explicitly handle missing rows, for example with a NOT FOUND handler or a separate existence check.

14.2 Safer Version with a NOT FOUND Handler

DELIMITER //

CREATE PROCEDURE EvaluateEmployeeSalarySafely(IN p_employee_id INT)
BEGIN
    DECLARE v_salary DECIMAL(10,2) DEFAULT NULL;
    DECLARE v_employee_found BOOLEAN DEFAULT TRUE;

    DECLARE CONTINUE HANDLER FOR NOT FOUND
        SET v_employee_found = FALSE;

    SELECT salary
    INTO v_salary
    FROM employees
    WHERE employee_id = p_employee_id;

    IF v_employee_found = FALSE THEN
        SELECT 'Employee not found' AS message;
    ELSEIF v_salary IS NULL THEN
        SELECT 'Salary is not available' AS message;
    ELSEIF v_salary >= 80000 THEN
        SELECT 'Senior Salary Band' AS salary_band;
    ELSEIF v_salary >= 50000 THEN
        SELECT 'Middle Salary Band' AS salary_band;
    ELSE
        SELECT 'Junior Salary Band' AS salary_band;
    END IF;
END //

DELIMITER ;

The handler allows the procedure to respond gracefully when the SELECT statement finds no employee. Handling NULL salary values separately also prevents missing data from being treated as a valid salary category.

15. Using Conditional Logic in Triggers

A trigger is a stored database program that executes automatically when a specified table event occurs. Conditional logic can validate or transform values during INSERT or UPDATE operations.

15.1 Example: Validate a Product Price

Assume a products table contains a price column:

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    price DECIMAL(10,2)
);

The following trigger rejects a negative price before a row is inserted:

DELIMITER //

CREATE TRIGGER BeforeProductInsert
BEFORE INSERT ON products
FOR EACH ROW
BEGIN
    IF NEW.price < 0 THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Product price cannot be negative';
    ELSEIF NEW.price IS NULL THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Product price is required';
    END IF;
END //

DELIMITER ;

In a trigger, NEW refers to the incoming row's values for an INSERT operation. The SIGNAL statement raises an error and prevents invalid data from being inserted.

Note: The table definition permits NULL in the price column in this example, so the trigger checks it explicitly. In production, a NOT NULL constraint is also appropriate when NULL prices are not allowed.

16. Handling NULL Values in Conditions

MySQL uses three-valued logic: true, false, and unknown. A comparison such as NULL = 10 does not evaluate to true or false; it evaluates to NULL (unknown).

16.1 Incorrect NULL Comparison

IF p_salary = NULL THEN
    SELECT 'Salary is missing';
END IF;

This condition does not correctly test whether the salary is NULL.

16.2 Correct NULL Comparison

IF p_salary IS NULL THEN
    SELECT 'Salary is missing';
ELSE
    SELECT 'Salary is available';
END IF;

Use IS NULL or IS NOT NULL when checking for missing values.

16.3 Handling NULL in CASE

SELECT
    employee_name,
    CASE
        WHEN salary IS NULL THEN 'Unknown'
        WHEN salary >= 50000 THEN 'Eligible'
        ELSE 'Not Eligible'
    END AS eligibility
FROM employees;

Place the NULL condition before ordinary salary comparisons when missing values need their own category.

17. Nested IF Statements

A nested IF statement is an IF statement placed inside another IF statement. It can be used when one decision depends on the result of another decision.

17.1 Example: User Eligibility

DELIMITER //

CREATE PROCEDURE CheckUserEligibility(
    IN p_age INT,
    IN p_has_id BOOLEAN
)
BEGIN
    IF p_age >= 18 THEN
        IF p_has_id = TRUE THEN
            SELECT 'Eligible' AS result;
        ELSE
            SELECT 'Valid identification is required' AS result;
        END IF;
    ELSE
        SELECT 'Age requirement not met' AS result;
    END IF;
END //

DELIMITER ;

The procedure first checks the age. Only if the age requirement is met does it evaluate whether the user has identification.

17.2 When to Avoid Excessive Nesting

• Deeply nested conditions can make code difficult to read.
• Use ELSEIF when the decisions are mutually exclusive alternatives.
• Use searched CASE when it makes multiple conditions easier to understand.
• Move complicated business rules into clearly named helper routines when that improves maintainability.

18. Common Errors and Troubleshooting

18.1 Understanding DELIMITER

The DELIMITER command is a client instruction commonly used in tools such as the MySQL command-line client. It temporarily changes the statement terminator so semicolons inside a stored-program definition are not treated as the end of the whole CREATE statement.

DELIMITER //

CREATE PROCEDURE ExampleProcedure()
BEGIN
    IF 5 > 3 THEN
        SELECT 'Condition is true';
    END IF;
END //

DELIMITER ;

The server stores the procedure definition; the delimiter command itself is not part of the stored program. Some database clients manage routine delimiters differently, so follow the syntax supported by your chosen tool.

19. Best Practices for Conditional Logic

• Write clear conditions: Use meaningful comparisons that directly express the business rule.
• Order branches carefully: In IF/ELSEIF and CASE structures, the first true branch wins.
• Include a fallback: Use ELSE when unmatched or unexpected values need a defined outcome.
• Handle NULL explicitly: Use IS NULL and IS NOT NULL instead of equality comparisons with NULL.
• Validate input values: Check for negative values, invalid ranges, and missing parameters when necessary.
• Use appropriate constructs: Use procedural IF or CASE for control flow and CASE expressions or IF() for query results.
• Avoid duplicate logic: Reuse well-designed routines where it improves consistency.
• Use descriptive aliases: Names such as salary_category and order_description make query output easier to understand.
• Test boundary values: Verify values immediately below, equal to, and above every threshold.
• Be careful with updates: Test conditional UPDATE statements on sample data and use an appropriate WHERE clause.
• Document business rules: Explain non-obvious thresholds and policies so future developers can maintain the code.
• Use constraints when appropriate: Enforce data requirements with NOT NULL, CHECK constraints, unique keys, or other database constraints where suitable.

20. Practical Project: Student Result Management

Project objective: Build a simple stored procedure that classifies a student's marks, handles invalid values, and returns an appropriate result.

20.1 Create a Sample Table

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(100) NOT NULL,
    marks INT
);

20.2 Insert Sample Records

INSERT INTO students (student_id, student_name, marks)
VALUES
(1, 'Aarav', 95),
(2, 'Meera', 78),
(3, 'Kabir', 63),
(4, 'Anaya', 42),
(5, 'Ishaan', 28),
(6, 'Riya', NULL);

20.3 Create the Result Procedure

DELIMITER //

CREATE PROCEDURE GetStudentResult(IN p_student_id INT)
BEGIN
    DECLARE v_marks INT DEFAULT NULL;
    DECLARE v_student_name VARCHAR(100);
    DECLARE v_found BOOLEAN DEFAULT TRUE;

    DECLARE CONTINUE HANDLER FOR NOT FOUND
        SET v_found = FALSE;

    SELECT student_name, marks
    INTO v_student_name, v_marks
    FROM students
    WHERE student_id = p_student_id;

    IF v_found = FALSE THEN
        SELECT 'Student not found' AS result;
    ELSEIF v_marks IS NULL THEN
        SELECT 'Marks not available' AS result;
    ELSEIF v_marks < 0 OR v_marks > 100 THEN
        SELECT 'Invalid marks' AS result;
    ELSEIF v_marks >= 90 THEN
        SELECT v_student_name AS student_name,
               'Distinction' AS result;
    ELSEIF v_marks >= 75 THEN
        SELECT v_student_name AS student_name,
               'Very Good' AS result;
    ELSEIF v_marks >= 60 THEN
        SELECT v_student_name AS student_name,
               'Good' AS result;
    ELSEIF v_marks >= 40 THEN
        SELECT v_student_name AS student_name,
               'Pass' AS result;
    ELSE
        SELECT v_student_name AS student_name,
               'Fail' AS result;
    END IF;
END //

DELIMITER ;

20.4 Execute the Procedure

CALL GetStudentResult(1);
CALL GetStudentResult(2);
CALL GetStudentResult(3);
CALL GetStudentResult(4);
CALL GetStudentResult(5);
CALL GetStudentResult(6);
CALL GetStudentResult(100);

20.5 Expected Behavior

This project demonstrates conditional branching, validation, NULL handling, and missing-record handling in one stored procedure.

21. Practice Exercises

1. Create a procedure that checks whether a number is even or odd.
2. Create a procedure that classifies a person's age as child, teenager, adult, or senior.
3. Use ELSEIF to calculate a commission based on sales amount.
4. Create a simple CASE statement that converts numeric order statuses into descriptions.
5. Create a searched CASE statement that categorizes products according to stock quantity.
6. Write a CASE expression that assigns an employee performance category in a SELECT query.
7. Use the IF() function to display whether an order qualifies for free shipping.
8. Create a stored function that returns a discount percentage based on a purchase amount.
9. Handle a NULL input explicitly in a procedure that checks salary eligibility.
10. Modify the student result project to return a different message for marks outside the 0–100 range.

22. Interview Questions and Answers

Q1. What is conditional logic in MySQL?

Conditional logic evaluates conditions and determines which statements or values should be used based on their results.

Q2. What is the purpose of the IF statement?

The IF statement executes a block of statements when a condition is true and can execute an alternative block when it is false or unknown.

Q3. What is ELSEIF used for?

ELSEIF allows a stored program to test additional conditions after earlier conditions have not been satisfied.

Q4. What happens when no IF condition is true and there is no ELSE?

The statements in the IF and ELSEIF branches are skipped, and execution continues after END IF.

Q5. What is the difference between IF and IF()?

The procedural IF statement controls program flow. The IF() function returns one of two values and can be used in SQL expressions.

Q6. What is a CASE statement?

A CASE statement chooses a branch and executes its statements based on a matching value or condition.

Q7. What are the two types of CASE statements?

The simple CASE statement compares an expression with specified values. The searched CASE statement evaluates Boolean conditions.

Q8. What is the difference between a CASE statement and a CASE expression?

A CASE statement controls the execution of statements in a stored program. A CASE expression returns a value that can be used in a SQL query or another expression.

Q9. What happens when a procedural CASE statement has no matching branch and no ELSE?

MySQL raises a case-not-found error. Adding an ELSE branch or handling all possible cases prevents this situation.

Q10. Can IF and CASE be used inside stored functions?

Yes. Stored functions can use procedural IF and CASE statements to calculate a value and return it using RETURN.

Q11. Can CASE be used in a SELECT statement?

Yes. A CASE expression can calculate values in SELECT queries, including labels and categories based on column values.

Q12. How do you check whether a variable is NULL?

Use variable IS NULL. Do not use variable = NULL for a NULL check.

Q13. Why does condition order matter?

MySQL executes the first branch whose condition evaluates to true. A broader condition placed before a more specific condition can prevent the latter from being reached.

Q14. What is a nested IF statement?

A nested IF statement is an IF statement inside another IF statement. It is useful when one decision depends on an earlier decision.

Q15. What is the role of ELSE in conditional logic?

ELSE provides a default branch when none of the preceding conditions is true. It is useful for handling remaining or unexpected cases.

Q16. What does DELIMITER do?

DELIMITER is a client command that changes the statement terminator while defining compound stored-program code. It is not an SQL statement stored inside the routine.

Q17. Can a CASE expression be used in an UPDATE statement?

Yes. A CASE expression can assign different values to rows based on conditions in an UPDATE statement.

Q18. How can conditional logic improve database applications?

It allows database programs to validate inputs, classify data, enforce business rules, calculate results, and respond differently to different situations.

23. Quick Revision Notes

• IF ... THEN executes statements when a condition is true.
• ELSEIF checks additional conditions.
• ELSE provides a fallback branch.
• END IF closes a procedural IF structure.
• A simple CASE statement compares one expression with specified values.
• A searched CASE statement evaluates Boolean conditions.
• END CASE closes a procedural CASE statement.
• A CASE expression returns a value and ends with END.
• IF() is a function that returns one of two values.
• Conditions are evaluated in order; the first true branch is selected.
• Use IS NULL to test for NULL values.
• Use DELIMITER when required by your SQL client to define compound routines.
• Test boundary values and unexpected inputs before using conditional logic in production.

24. Conclusion

Conditional logic is an essential part of MySQL programming. The IF statement is useful for straightforward decisions, ELSEIF handles multiple alternative conditions, and CASE provides simple and searched branching options. The CASE expression and IF() function are useful when a SQL query needs to return a calculated value instead of controlling procedural execution.

By combining conditional logic with stored procedures, stored functions, triggers, and SQL queries, developers can implement business rules, validate data, categorize records, and build more reliable database applications. Always consider condition order, NULL values, boundary cases, and unexpected inputs when designing conditional statements.

25. Learn More with JustAcademy

To learn MySQL fundamentals, database programming, stored procedures, stored functions, and other SQL concepts, explore the course and register for a demonstration session using the links below.

MySQL Training Course: https://www.justacademy.co/course-detail/mysql-training

Register for Course Demo: https://www.justacademy.co/register-for-course-demo