IF Statement in MySQL

This statement executes a block of SQL code based on a specified condition.

It has three forms:

Simple IF-THEN statement, 

IF-THEN-ELSE statement, and 

IF-THEN-ELSEIF- ELSE statement.

 

MySQL simple IF-THEN statement

Execute a set of SQL statements based on a specified condition.

Syntax:-

IF condition THEN 

   statements;

END IF;

 

In this syntax:

• First, specify a condition to execute the code between the IF-THEN keyword.
• If this condition is TRUE, the statements between IF-THEN and END IF will execute. Otherwise, the control is passed to the next statement after the END IF.

 Example: create a customer table

 

Create  GetCustomerLevel() stored procedure.

DELIMITER $$

 

CREATE PROCEDURE GetCustomerLevel( IN  pCustomerNumber INT, 

OUT pCustomerLevel  VARCHAR(20))

BEGIN

    DECLARE credit DECIMAL(10,2) DEFAULT 0;

 

    SELECT creditLimit INTO credit FROM customers WHERE customerNumber = pCustomerNumber;

 

    IF credit > 50000 THEN

        SET pCustomerLevel = 'PLATINUM';

    END IF;

END$$

 

DELIMITER ;

 

Explanation:-

The stored procedure GetCustomerLevel() accepts two parameters:  pCustomerNumber 

and  pCustomerLevel.

select creditLimit of the customer specified by the pCustomerNumber from the customers table and store it in the local variable credit.

Then, set value for the OUT parameter pCustomerLevel to PLATINUM if the credit limit of the customer is greater than 50,000.

 

call the GetCustomerLevel() stored procedure for customer 141 and show the value of the OUT parameter pCustomerLevel:

CALL GetCustomerLevel(141, @level);

SELECT @level;

 

Output:- 

customer 141 has a credit limit greater than 50,000, its level is set to PLATINUM as

expected.

If want to show all customers that have a credit limit greater than 50,000 then use :

SELECT customerNumber, creditLimit FROM customers WHERE creditLimit > 50000

ORDER BY creditLimit DESC;

MySQL IF-THEN-ELSE statement

In this case execute other statements when the IF condition is false, then else-statements between the ELSE and END IF execute.

Syntax:- 

IF condition THEN

   statements;

ELSE

   else-statements;

END IF;

Example:- 

Create GetCustomerLevel() stored procedure:

DELIMITER $$

 

CREATE PROCEDURE GetCustomerLevel( IN  pCustomerNumber INT, 

    OUT pCustomerLevel  VARCHAR(20))

BEGIN

    DECLARE credit DECIMAL DEFAULT 0;

 

    SELECT creditLimit INTO credit FROM customers WHERE customerNumber = pCustomerNumber;

 

    IF credit > 50000 THEN

        SET pCustomerLevel = 'PLATINUM';

    ELSE

        SET pCustomerLevel = 'NOT PLATINUM';

    END IF;

END$$

 

DELIMITER ;

Call the stored procedure for customer number 447  and show the value of the OUT parameter 

pCustomerLevel:

CALL GetCustomerLevel(447, @level);

SELECT @level;

 

Output:-  

The credit limit of the customer 447 is less than 50,000, therefore, the statement in the ELSE branch executes and sets the value of the OUT parameter pCustomerLevel to NOT PLATINUM.

Explanation:-

The stored procedure GetCustomerLevel() accepts two parameters:  pCustomerNumber  

and  pCustomerLevel.

select creditLimit of the customer specified by the pCustomerNumber from the customers table and store it in the local variable credit.

Then, set value for the OUT parameter pCustomerLevel to PLATINUM if the credit limit of the customer is greater than 50,000. Otherwise include the ELSE branch. If the credit is not greater than 50,000, then set the customer level to NOT PLATINUM in the block between ELSE and END IF.

Use query to finds customers that have credit limit less than or equal 50,000:

SELECT customerNumber, creditLimit FROM customers WHERE creditLimit <= 50000

ORDER BY creditLimit DESC;

MySQL IF-THEN-ELSEIF-ELSE statement

If execute multiple conditions, use the following IF-THEN-ELSEIF-ELSE statement:

Syntax:- 

IF condition THEN

   statements;

ELSEIF elseif-condition THEN

   elseif-statements;

...

ELSE

   else-statements;

END IF;

In this syntax, if the condition evaluates to TRUE , the statements in the IF-THEN branch executes; otherwise, the next elseif-condition is evaluated.

If the elseif-condition evaluates to TRUE, the elseif-statement executes; otherwise, the next elseif-condition is evaluated.

The IF-THEN-ELSEIF-ELSE statement can have multiple ELSEIF branches.

If no condition in the IF and ELSE IF evaluates to TRUE, the else-statements in the ELSE branch will execute.

Example:- 

create the GetCustomerLevel() stored procedure 

DELIMITER $$

CREATE PROCEDURE GetCustomerLevel( IN  pCustomerNumber INT, 

    OUT pCustomerLevel  VARCHAR(20))

BEGIN

    DECLARE credit DECIMAL DEFAULT 0;

    SELECT creditLimit INTO credit FROM customers WHERE customerNumber = pCustomerNumber;

    IF credit > 50000 THEN

        SET pCustomerLevel = 'PLATINUM';

    ELSEIF credit <= 50000 AND credit > 10000 THEN

        SET pCustomerLevel = 'GOLD';

    ELSE

        SET pCustomerLevel = 'SILVER';

    END IF;

END $$

DELIMITER ;

Call the stored procedure GetCustomerLevel() and show the level of the customer 447:

CALL GetCustomerLevel(447, @level); 

SELECT @level;

Output:- 

Explanation: 

In this stored procedure:

• If the credit is greater than 50,000, the level of the customer is PLATINUM.
• If the credit is less than or equal 50,000 and greater than 10,000, then the level of customer is GOLD.
• Otherwise, the level of the customer is SILVER.

These statements test the stored procedure with the customer that has a credit limit of 10000 or less, which output as SILVER.

===========================================================