MySQL IF ELSE with Concepts and Examples

Are you looking to harness the power of conditional logic in your MySQL queries? Look no further! In this article, we’ll explore the concepts of IF ELSE statements in MySQL, learn how to write them and dive into examples you can apply in your work. Let’s get started!

MySQL IF Function

The MySQL IF function is a simple way to implement conditional logic in your queries. It allows you to evaluate a condition and return a value when it is true and another value when it is false. The syntax is as follows:

IF(condition, value_if_true, value_if_false)

For example, let’s consider a students table with columns id, name, and score. If you want to retrieve a student’s name and determine if they passed or failed based on a passing score of 60, you could use the IF function as follows:

SELECT name, IF(score >= 60, 'Passed', 'Failed') AS result
FROM students;

IF-THEN-ELSE

Let’s look at the IF-THEN-ELSE structure, which provides more control over your conditional logic.

Syntax

The syntax for an IF-THEN-ELSE statement is:

IF condition THEN
   statements;
ELSE
   statements;
END IF;

Examples

Suppose you have a users table and want to update the status column based on the registration_date:

DELIMITER //

CREATE PROCEDURE update_user_status()
BEGIN
  DECLARE reg_date DATE;
  DECLARE cur CURSOR FOR SELECT registration_date FROM users;
  
  OPEN cur;
  FETCH cur INTO reg_date;
  
  WHILE NOT done DO
    IF reg_date < DATE(NOW()) - INTERVAL 1 YEAR THEN
      UPDATE users SET status = 'inactive' WHERE registration_date = reg_date;
    ELSE
      UPDATE users SET status = 'active' WHERE registration_date = reg_date;
    END IF;
    FETCH cur INTO reg_date;
  END WHILE;
  
  CLOSE cur;
END //

DELIMITER ;

This stored procedure checks each user’s registration date and updates their status accordingly.

IF-THEN-ELSEIF

For scenarios with multiple conditions, you can use the IF-THEN-ELSEIF structure.

Syntax

The syntax for an IF-THEN-ELSEIF statement is:

IF condition THEN
   statements;
ELSEIF condition THEN
   statements;
...
ELSE
   statements;
END IF;

Examples

Imagine you want to classify products in a products table based on their price:

DELIMITER //

CREATE PROCEDURE classify_products()
BEGIN
  DECLARE cur_price DECIMAL(10,2);
  DECLARE cur CURSOR FOR SELECT price FROM products;
  
  OPEN cur;
  FETCH cur INTO cur_price;
  
  WHILE NOT done DO
    IF cur_price < 10 THEN
      UPDATE products SET category = 'low' WHERE price = cur_price;
    ELSEIF cur_price >= 10 AND cur_price < 50 THEN
      UPDATE products SET category = 'medium' WHERE price = cur_price;
    ELSE
      UPDATE products SET category = 'high' WHERE price = cur_price;
    END IF;
    FETCH cur INTO cur_price;
  END WHILE;
  
  CLOSE cur;
END //

DELIMITER ;

This stored procedure classifies products into low, medium, and high categories based on their price.

CASE Statement

The CASE statement is another way to implement conditional logic in MySQL. It is more flexible than the IF function and can handle multiple conditions.

Syntax

The syntax for a CASE statement is:

CASE
   WHEN condition THEN result
   ...
   ELSE result
END

Examples

Let’s classify products based on their price and stock in a products table:

UPDATE products
SET category = (CASE
   WHEN price < 10 AND stock >= 100 THEN 'low'
   WHEN price >= 10 AND price < 50 AND stock >= 100 THEN 'medium'
   ELSE 'high'
END);

This query updates the category column for each product based on the specified conditions.

Stored Procedures and IF-THEN-ELSE

Stored procedures provide a way to encapsulate a series of SQL statements, making it easier to reuse code and improve maintainability. You can use IF-THEN-ELSE and IF-THEN-ELSEIF structures within stored procedures to implement conditional logic.

IF-THEN-ELSE in Stored Procedures

Consider a stored procedure that calculates a user’s discount based on their membership status:

DELIMITER //

CREATE PROCEDURE calculate_discount(IN user_id INT, OUT discount DECIMAL(5,2))
BEGIN
  DECLARE membership VARCHAR(20);

  SELECT membership_status INTO membership FROM users WHERE id = user_id;
  
  IF membership = 'Gold' THEN
    SET discount = 0.20;
  ELSE
    SET discount = 0.10;
  END IF;
END //

DELIMITER ;

This stored procedure takes a user ID as input and returns the corresponding discount based on their membership status.

IF-THEN-ELSEIF in Stored Procedures

Now let’s create a stored procedure that calculates a user’s discount using multiple membership levels:

DELIMITER //

CREATE PROCEDURE calculate_discount_v2(IN user_id INT, OUT discount DECIMAL(5,2))
BEGIN
  DECLARE membership VARCHAR(20);

  SELECT membership_status INTO membership FROM users WHERE id = user_id;
  
  IF membership = 'Gold' THEN
    SET discount = 0.20;
  ELSEIF membership = 'Silver' THEN
    SET discount = 0.15;
  ELSE
    SET discount = 0.10;
  END IF;
END //

DELIMITER ;

This stored procedure calculates the discount based on three different membership levels – Gold, Silver, and other.

Best Practices

1. Use descriptive names for variables, tables, and columns to improve readability.
2. Keep your conditional logic simple to avoid complex, nested structures.
3. Consider using stored procedures for complex logic that requires multiple conditional statements.
4. Test your queries and stored procedures thoroughly to ensure they work as expected.

Conclusion

This article covers the fundamentals of MySQL IF ELSE statements, including the IF function, IF-THEN-ELSE, IF-THEN-ELSEIF, and CASE statements. We also explored how to use these structures within stored procedures. By using conditional logic in your queries, you can create more flexible and powerful applications that respond dynamically to your data.