MySQL IF Statement and MySQL IF() Function

Yusuf SEZER

5 years ago

The article contains information about MYSQL IF Statement, ELSEIF Statement and ELSE STATEMENT used to direct the command flow by condition in the MySQL VTY system.

MySQL IF() Function

We can use two if statements: MySQL if function and if statement.

MySQL if function is used in SQL commands. Similar to the ternary operator statement in programming languages.

IF(Condition, True, False)

1	IF(Condition, True, False)

If the condition written to the first parameter of the MySQL IF function is true, it runs the TRUE parameter, and if it is false, it runs the FALSE parameter.

Using MySQL IF function;

SELECT IF(5 > 6, "5 is bigger", "6 is bigger") AS result;

1	SELECT IF(5 > 6, "5 is bigger", "6 is bigger") AS result;

The IF function is also used in table queries.

SELECT *, IF( product_price> 30, "Expensive", "Inexpensive") AS result FROM Products;

1	SELECT *, IF( product_price> 30, "Expensive", "Inexpensive") AS result FROM Products;

The query will return “Expensive” as a result if the price in the product_price column is greater than 30, and “Inexpensive” if the product_price column is is smaller than 30.

SQL functions can also be used within the MySQL IF function.

SELECT *, IF( LENGTH(product_name) > 10, "LONG", "SHORT") AS result FROM Products;

1	SELECT *, IF( LENGTH(product_name) > 10, "LONG", "SHORT") AS result FROM Products;

The query will return LONG if the product name length in the product_name column is greater than 10 characters, and SHORT if product_name column is smaller than 10 characters.

MySQL IF-ELSEIF-ELSE Statement

This allows using if, elseif and else statements to check the values in MySQL Stored Procedures.

MySQL If Usage;

IF Condition THEN
  -- Commands
END IF;

IF Condition THEN

-- Commands

END IF;

MySQL ElseIf Usage;

IF Condition THEN
  -- Commands
ELSEIF Condition2 THEN
  -- Commands2
END IF;

IF Condition THEN

-- Commands

ELSEIF Condition2 THEN

-- Commands2

END IF;

If Condition in IF is wrong AND Condition2 in ELSEIF is true, commands in Command2 are executed.

MySQL Else Usage;

IF Condition THEN
  -- Commands
ELSE
  -- Commands2
END IF;

IF Condition THEN

-- Commands

ELSE

-- Commands2

END IF;

IF Condition1 THEN
  -- Commands1
ELSEIF Condition2 THEN
  -- Commands2
...
ELSE
  -- Commands3
END IF;

IF Condition1 THEN

-- Commands1

ELSEIF Condition2 THEN

-- Commands2

...

ELSE

-- Commands3

END IF;

If all IF and ELSEIF conditions are wrong, commands3 in the ELSE section are executed.

Below is an example of using if, elseif and else in stored procedures.

DELIMITER //
CREATE PROCEDURE NameSurname(IN mynumber INT, OUT status VARCHAR(50))
BEGIN

IF mynumber = 1 THEN
  SET status = 'Yusuf';
ELSEIF mynumber = 2 THEN
  SET status = 'SEZER';
ELSEIF mynumber = 3 THEN
  SET status = 'Yusuf SEZER';
ELSE
  SET status = 'Yusuf Sefa SEZER';
END IF;

END//
DELIMITER ;

DELIMITER //

CREATE PROCEDURE NameSurname(IN mynumber INT, OUT status VARCHAR(50))

BEGIN

IF mynumber = 1 THEN

SET status = 'Yusuf';

ELSEIF mynumber = 2 THEN

SET status = 'SEZER';

ELSEIF mynumber = 3 THEN

SET status = 'Yusuf SEZER';

ELSE

SET status = 'Yusuf Sefa SEZER';

END IF;

END//

DELIMITER ;

In the example, the NameSurname stored procedure returns different status values according to the MyNumber parameter.

If the MyNumber parameter value is 1, the status parameter returns Yusuf, 2 if SEZER, 3 if Yusuf SEZER, if none, Yusuf Sefa SEZER.

Let’s run the stored procedure.

SET @result= 0;
CALL NameSurname(99, @result);
SELECT @result;

SET @result= 0;

CALL NameSurname(99, @result);

SELECT @result;

When the stored procedure is executed, it will return “Yusuf Sefa SEZER”.