How to Use the IF Statement in MySQL

08/06/2021

Contents

In this article, you will learn how to use the IF statement in MySQL.

Using the IF statement in MySQL

The IF statement in MySQL is used to execute a set of SQL statements based on a specified condition. It can be used in a variety of scenarios, such as data validation, conditional data retrieval, and data manipulation.

Syntax

The basic syntax of the IF statement in MySQL is as follows:

IF(condition, statement1, statement2);

Here, “condition” is the condition that you want to test, “statement1” is the SQL statement that will be executed if the condition is true, and “statement2” is the SQL statement that will be executed if the condition is false.

Examples

Using IF to validate data

Suppose we have a table called “students” that contains information about students, including their name and age. We want to validate the age of each student and set it to zero if it is negative. We can use the IF statement to accomplish this as follows:

UPDATE students
SET age = IF(age < 0, 0, age);

In the above example, we use the IF statement to check if the "age" column is less than 0. If it is, we set the age to 0. Otherwise, we leave the age as it is.

Using IF to retrieve conditional data

Suppose we have a table called "sales" that contains information about sales made by salespeople, including their name and the amount of sales. We want to retrieve the names of salespeople who have made sales greater than $10,000. We can use the IF statement to accomplish this as follows:

SELECT name
FROM sales
WHERE IF(amount > 10000, 1, 0) = 1;

In the above example, we use the IF statement to check if the "amount" column is greater than 10,000. If it is, we return a value of 1. Otherwise, we return a value of 0. We then use the WHERE clause to filter the results and return the names of salespeople whose sales are greater than $10,000.

Using IF in stored procedures

Stored procedures in MySQL are a collection of SQL statements that can be executed as a single unit. You can use the IF statement in stored procedures to execute different SQL statements based on a condition. Let's take an example:

DELIMITER //
CREATE PROCEDURE get_student(IN student_id INT)
BEGIN
   DECLARE student_name VARCHAR(50);
   DECLARE student_age INT;
   SELECT name, age INTO student_name, student_age FROM students WHERE id = student_id;
   IF student_age > 18 THEN
      SELECT CONCAT(student_name, ' is an adult') AS info;
   ELSE
      SELECT CONCAT(student_name, ' is a minor') AS info;
   END IF;
END //
DELIMITER ;

In the above example, we create a stored procedure called "get_student" that takes a student ID as input. We then use the IF statement to check if the age of the student is greater than 18. If it is, we return a message saying that the student is an adult. Otherwise, we return a message saying that the student is a minor.

#MySQL