MySQL IF Statement

By /
Mysql If Statement

In this tutorial, we will learn about the IF Statement in MySQL which is very similar to the if statements in any other programing language such as C, C++, Java, Python etc. We will also learn different forms of the IF statement and their examples. So, let’s get started!

What is the MySQL IF Statement?

Note that the IF Statement is different from the IF() function available in MySQL. For more information about the MySQL IF() Function, read this tutorial.

Back to our topic now – MySQL IF Statement.

In MySQL, there are three forms of the IF statement – IF-THEN, IF-THEN-ELSE and IF-THEN-ELSEIF ladder.

The IF statement is used in the stored programs such as stored functions or stored procedures.

As mentioned earlier, it works similarly to the if statements in other programming languages. When the given search condition becomes true, the THEN block executes. Otherwise, the else block will be executed.

In the case of the IF-THEN-ELSEIF Ladder, the next condition will be checked if the previous condition becomes false. If neither condition evaluates to true, the ELSE block will be executed.

Let’s see the syntax of the IF statement.

Syntax of the IF Statement

IF search_condition THEN statement_list
    [ELSEIF search_condition THEN statement_list] ...
    [ELSE statement_list]
END IFCode language: SQL (Structured Query Language) (sql)

The following things you must consider while using the IF statement.

  1. The IF statement must end with the END IF statement.
  2. The statement_lists must consist of one or more SQL statements. Empty statement_list is not permitted.
  3. The IF…END IF block inside the stored programs must end with the semicolon.

MySQL IF Statement Examples

Now we will take some examples to understand the different forms of the IF statement.

Let’s start with the simple IF-THEN statement.

Before that, take a look at the table that we have created for the demo purpose. The following snapshots show the description and the sample data inside the table.

Emps Table Description
Emps Table Description
Emps Table Data
Emps Table Data

IF-THEN Statement Example

The syntax of the IF-ELSE statement is as follows-

IF condition THEN 
   statements;
END IF;Code language: SQL (Structured Query Language) (sql)

Here, if the condition evaluates to true, the statement after THEN will execute. If the condition becomes false, control will go to the next statement of the END IF.

Let’s take an example here.

We will create a stored procedure which will take an employee id as an argument and return true if the employee lives in the city “London”. Else, it will return false.

DELIMITER //
CREATE PROCEDURE isLondon(empID INT)
BEGIN
DECLARE eCity VARCHAR(50);
SELECT city INTO eCity FROM emps
WHERE id = empID;
IF eCity = "london" THEN
SELECT "true" AS isLondon;
END IF;
END //
DELIMITER ;
Code language: SQL (Structured Query Language) (sql)

The procedure is created successfully.

In the table data, you can see that the employee with id 5 lives in the city “London”. So, we must get the result true for the employee id 5. For any other employee id, we should not get anything.

CALL isLondon(1);
CALL isLondon(2);
CALL isLondon(5);Code language: SQL (Structured Query Language) (sql)
IF THEN Result
IF THEN Result

As you can see, we have got the expected output for the employee id 5.

IF-THEN-ELSE Statement Example

The syntax of the IF-THEN-ELSE is similar to the IF-THEN.

IF condition THEN
   statements;
ELSE
   else-statements;
END IF;Code language: SQL (Structured Query Language) (sql)

Here, if the condition becomes false, the else-statement will get executed.

Let’s take an example. We will create a stored procedure which will accept an employee id as an argument. We will display “true” if the employee lives in the city of London. If not, then we will display “False” and the city name of that employee.

DELIMITER //
CREATE PROCEDURE displayCity(
IN empID INT)
BEGIN
DECLARE eCity VARCHAR(50);
SELECT city INTO eCity FROM emps
WHERE id = empID;
IF ecity = "london" THEN
SELECT "true" AS isLondon;
ELSE 
SELECT CONCAT("False"," ",eCity);
END IF;
END//
DELIMITER ;Code language: SQL (Structured Query Language) (sql)

Here, only for the employee id 5, we should get the result “true”. For any other employee id, we should get the respective city names. Let’s test it by calling the procedure.

CALL displayCity(1);
CALL displayCity(5);Code language: SQL (Structured Query Language) (sql)
IF THEN ELSE Result
IF THEN ELSE Result

As you can see, we get the city name of the employee.

IF-THEN-ELSEIF Statement Example

When you want to get the result out of multiple conditions, you use the IF-THEN-ELSEIF-ELSE statement.

The syntax of it is as follows-

IF condition THEN
   statements;
ELSEIF elseif-condition THEN
   elseif-statements;
...
ELSE
   else-statements;
END IF;Code language: SQL (Structured Query Language) (sql)

Here, if the first condition becomes false, the next condition will be checked. All next conditions will be checked as long as previously specified conditions become false. As soon as the current condition becomes true, the THEN block will execute and control goes to the end of the IF statement. If no condition evaluates to true, the ELSE block will execute.

Let’s take an example now.

In the table, we have employees from five cities. We will assign ranks to cities as follows- London:1, LA:2, LV:3, NY:4, Boston:5.

Now, we will create a stored procedure which will accept the employee id as an argument. Then, we will show the detail of that employee with the rank of the city.

DELIMITER //
CREATE PROCEDURE cityRanks(
empID INT)
BEGIN
DECLARE eCity VARCHAR(50);
DECLARE ranking INT(1);
SELECT city INTO eCity FROM emps
WHERE id = empID;
IF eCity = "London" THEN 
SET ranking = 1;
ELSEIF eCity = "LA" THEN 
SET ranking = 2;
ELSEIF eCity = "LV" THEN 
SET ranking = 3;
ELSEIF eCity = "NY" THEN 
SET ranking = 4;
ELSEIF eCity = "Boston" THEN 
SET ranking = 5;
ELSE SET ranking = -1;
END IF;
SELECT *, ranking FROM emps
WHERE id = empID;
END //
DELIMITER ;Code language: SQL (Structured Query Language) (sql)

Let’s test it now by providing the employee ids to the procedure. For employees id 1, 2, and 3, we should get ranks 2,5 and 4 respectively because the employees live in LA, Boston and NY. Let’s see an output.

CALL cityRanks(1);
CALL cityRanks(2);
CALL cityRanks(3);Code language: SQL (Structured Query Language) (sql)
IF ELSEIF Ladder
IF ELSEIF Ladder

As you can see, we have received the expected output.

Summary

In this tutorial, we have learned –

  • What is the IF statement.
  • How the IF statement is different from the IF() function.
  • Different forms of the IF statement.
  • Syntax of each form of the IF statement.
  • Example of each form of the IF statement.

References

MySQL official documentation on the IF Statement.