The “IFs” in MySQL

MySQL has two types of IF:

• IF statement
• IF function

In this tutorial, I will explain the difference and show you examples of MySQL IF.

What is the MySQL IF function?

The IF function returns a value if the condition is true. If the condition is false, it returns another value. The syntax for using the IF function is:

Alternatively, you may also write like this:

This is how it works:

An example of the IF function in MySQL

In the first example of the IF function, I used no table data.

Instead, simple values are used as a condition and returned values to show how IF function works.

The query:

SELECT IF(10>5, "True", "False") AS Result;

The output:

As the first expression, I used if 10 is greater than 5 then return expression 2 (True), otherwise expression 3 (False).

As 10 is greater than 5 (True), it returned the second expression.

Using table data with IF function

For this query, I am using the table data of the employees’ table.

We will get the name of employees, salary, and the IF function is used to tell the level of salary as “High” or “Low” based on the salary of the employee.

Have a look:

SELECT employee_name, employee_salary,

IF(employee_salary>=4000,"High","Low") AS Level

FROM tst_employees;

The result:

Using nested IF example

The following example shows using a nested IF function. Again, I used the employee’s table salary column as shown below:

SELECT employee_name, employee_salary,

IF(employee_salary <= 3000, 'Low', IF(employee_salary <= 4000, 'Handsome', 'High')) AS "Salary Status"

FROM tst_employees;

The result-set:

What is an IF statement?

The IF statement allows executing one or more given statements if the condition is true.

You may use more than one condition by using the ELSEIF statement.

If none of the conditions are true then the ELSE part statements (if given) are executed.

The general way of using the IF statement:

IF condition THEN statement(s)

    [ELSEIF condition THEN statement(s)] ...

    [ELSE statement(s)]

END IF

So, if you have a little programming background, this should look familiar and easy to understand.

The keyword IF is followed by a condition. For example:

After the condition, “THEN” keyword is used and there you will provide one or more statements to execute if that condition is true.

Optionally, you may use ELSEIF to give more conditions if the first condition fails.

If none of the conditions is true then the ELSE part statements are executed.

An example of IF statement

The following MySQL function shows the simple usage of IF..THEN..ELSEIF statement.

The function takes an input salary value. This is checked in the IF statement and returns the level of salary as the output.

See how IF, ELSEIF is used:

DELIMITER //

CREATE FUNCTION Set_level ( emp_sal Float )
RETURNS varchar(20)

BEGIN

   DECLARE e_sal_level VARCHAR(40);
   IF emp_sal <= 3500 THEN

      SET e_sal_level = 'Low Salary';

   ELSEIF emp_sal > 3500 AND monthly_value <= 5000 THEN

      SET e_sal_level = 'Avg Salary';
   ELSE
      SET e_sal_level = 'High Salary';
   END IF;

   RETURN income_level;
END;

DELIMITER ;