MySQL IF Statement

The MySQL IF statement allows you to execute a set of SQL statements based on a certain condition or value of an expression. To form an expression in MySQL, you can combine literals, variables, operators, and even functions. An expression can return three value TRUE, FALSE or NULL.

MySQL IF statement syntax

The following illustrates the syntax of the IF statement:

IF if_expression THEN commands [ELSEIF elseif_expression THEN commands] [ELSE commands] END IF;

1 2 3 4

IF if_expression THEN commands    [ELSEIF elseif_expression THEN commands]    [ELSE commands]    END IF;

If the if_expression evaluates to TRUE the commands in the IF branch will execute. If it evaluates to FALSE, MySQL will check the elseif_expression and execute the commands in ELSEIF branch if the elseif_expression evaluates to TRUE.
The IF statement may have multiple ELSEIF branches to check multiple expressions.  If no expression evaluates to TRUE, the commands in the ELSE branch will execute.

MySQL IF statement examples

Let’s take a look at an example of how to use MySQL IF statements.

DELIMITER $$ CREATE PROCEDURE GetCustomerLevel( in p_customerNumber int(11), out p_customerLevel varchar(10)) BEGIN DECLARE creditlim double; SELECT creditlimit INTO creditlim FROM customers WHERE customerNumber = p_customerNumber; IF creditlim > 50000 THEN SET p_customerLevel = 'PLATINUM'; ELSEIF (creditlim <= 50000 AND creditlim >= 10000) THEN SET p_customerLevel = 'GOLD'; ELSEIF creditlim < 10000 THEN SET p_customerLevel = 'SILVER'; END IF; END$$

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

DELIMITER $$   CREATE PROCEDURE GetCustomerLevel(     in  p_customerNumber int(11),     out p_customerLevel  varchar(10)) BEGIN     DECLARE creditlim double;       SELECT creditlimit INTO creditlim     FROM customers     WHERE customerNumber = p_customerNumber;       IF creditlim > 50000 THEN     SET p_customerLevel = 'PLATINUM';     ELSEIF (creditlim <= 50000 AND creditlim >= 10000) THEN         SET p_customerLevel = 'GOLD';     ELSEIF creditlim < 10000 THEN         SET p_customerLevel = 'SILVER';     END IF;   END$$

We pass customer number to the stored procedure to get customer level based on credit limit. We use IF ELSEIF and ELSE statement to check customer credit limit against multiple values.
The following is the flow chart  that demonstrates the logic of determining customer level.

In this tutorial, you have learned how to use MySQL IF statement to execute a block of SQL code based on conditions.