MySQL IF Statement

The IF statement in MySQL is used for conditional execution of SQL statements inside stored programs like stored procedures, functions, or triggers. It allows executing different code blocks depending on whether a condition evaluates to TRUE or FALSE.

1. Syntax of MySQL IF Statement

IF condition THEN
    -- Code block to execute if condition is TRUE
ELSEIF another_condition THEN
    -- Code block to execute if another_condition is TRUE
ELSE
    -- Code block to execute if none of the above conditions are TRUE
END IF;

✔ THEN → Specifies the block of statements to execute if the condition is TRUE.
✔ ELSEIF (Optional) → Specifies additional conditions.
✔ ELSE (Optional) → Specifies the block to execute if all conditions are FALSE.
✔ END IF → Marks the end of the IF statement.

2. MySQL IF Statement Example

Let's create a stored procedure that checks a student's grade and returns the result.

Example: Check Grade Status

DELIMITER $$

CREATE PROCEDURE check_grade(IN score INT, OUT result VARCHAR(50))
BEGIN
    IF score >= 90 THEN
        SET result = 'Excellent';
    ELSEIF score >= 75 THEN
        SET result = 'Good';
    ELSEIF score >= 50 THEN
        SET result = 'Pass';
    ELSE
        SET result = 'Fail';
    END IF;
END$$

DELIMITER ;

Calling the Procedure

CALL check_grade(85, @grade);
SELECT @grade;

📌 Output:

+----------+
| @grade   |
+----------+
| Good     |
+----------+

3. Using MySQL IF in SELECT Queries

MySQL does not support IF statements inside SELECT, but you can use IF() function or CASE statement.

Example: Using IF() Function

SELECT name, score, 
    IF(score >= 50, 'Pass', 'Fail') AS status 
FROM students;

📌 Explanation:

If score is 50 or more, it returns 'Pass'; otherwise, 'Fail'.

4. Using MySQL IF Inside a Trigger

Example: Trigger to Set Default Price

This trigger automatically sets a minimum price when inserting a new product.

DELIMITER $$

CREATE TRIGGER before_insert_product
BEFORE INSERT ON products
FOR EACH ROW
BEGIN
    IF NEW.price < 10 THEN
        SET NEW.price = 10;
    END IF;
END$$

DELIMITER ;

✔ If a new product has a price less than 10, it is set to 10 before inserting.

5. Key Points

✔ Only used inside stored programs (procedures, functions, triggers).
✔ Use ELSEIF for multiple conditions.
✔ Use IF() function for inline queries.
✔ Use CASE for complex conditions inside SELECT.

Would you like examples for nested IF statements or alternative conditional logic? 🚀

Souy Soeng

MySQL IF Statement