IF ELSE and CASE Conditional Statements in MySQL Workbench

• In MySQL, the IF, ELSE, and CASE statements are used for conditional branching within queries or procedures. They allow you to execute different blocks of code based on certain conditions.

• Suppose we have a 'students' table that contains id, name, age, and marks. The schema of this table is as follows.

    -- Create the students table

    CREATE TABLE students (

        id INT AUTO_INCREMENT PRIMARY KEY,

        name VARCHAR(255),

        age INT,

        marks INT

    );

    -- Insert data into the students table

    INSERT INTO students (name, age, marks) VALUES

    ('John Doe', 20, 85),

    ('Jane Smith', 21, 92),

    ('Michael Johnson', 19, 33),

    ('Emily Brown', 22, 110),

    ('David Lee', 20, 70),

    ('Sophia Williams', 21, 20),

    ('William Davis', 19, 62),

    ('Olivia Jones', 22, 44),

    ('Daniel Martinez', 20, 52),

    ('Ava Taylor', 21, -30);

• Now, we will check the condition: if the student's marks are greater than 32, they pass; otherwise, they fail.

    SELECT *, IF(marks >=33, "PASS", "FAIL") as RESULT from students

• Now, we will check the condition: if the student's marks are greater than 32, they pass; otherwise, they fail. We will also display records of students whose age has surpassed 20.

    SELECT *, IF(marks >=33, "PASS", "FAIL") as RESULT from students WHERE age > 20

• Now, we are checking the student's marks under multiple conditions using a case statement.

    SELECT *,

    CASE

        WHEN marks >= 75 THEN "MERIT"

        WHEN marks >= 60 THEN "1st Div"

        WHEN marks >= 45 THEN "2nd Div"

        WHEN marks >= 33 THEN "3rd Div"

        WHEN marks < 33 THEN "FAILED"

    END as GRADE

    FROM students

• Now, we are checking the student's marks under multiple conditions using a case statement with else as default value.

    SELECT *,

    CASE

        WHEN marks >= 75 and marks <= 100 THEN "MERIT"

        WHEN marks >= 60 and marks < 75 THEN "1st Div"

        WHEN marks >= 45 and marks < 60 THEN "2nd Div"

        WHEN marks >= 33 and marks < 45 THEN "3rd Div"

        WHEN marks >= 0 and marks < 33 THEN "FAILED"

        ELSE "% is not valid"

    END as GRADE

    FROM students