MySQL IF – Function and Statement (with Examples)

June 1st, 2022

There are two types of IFs in MySQL: the IF statement and the IF function. Both are different from each other. 

In this article, we will explain their diversities and show usage examples. Also, we will review other MySQL functions.

Contents

MySQL IF statements

The IF statement allows you to verify a condition. There are three types of MySQL IF statements: 

  • IF-THEN
  • IF-THEN-ELSE
  • IF-THEN-ELSEIF-ELSE

MySQL IF-THEN statement

You can use IF-THEN to run a collection of SQL queries based on a condition. Here is the statement syntax:

IF condition THEN 
  statements;
END IF;

If a specified condition evaluates to: 

  • true – statements between IF-THEN and END-IF will be executed
  • false – statements following the END-IF block will be applied

MySQL IF-THEN-ELSE statement

To execute statements when a condition in the IF block does not evaluate to true, you can use IF-THEN-ELSE. The syntax looks as follows: 

IF condition THEN
   statements;
ELSE
   else-statements;
END IF;

If a specified condition is: 

  • true – statements between IF-THEN and ELSE will be executed
  • false – statements following the ELSE and END-IF will be executed

MySQL IF-THEN-ELSEIF-ELSE statement

Using IF-THEN-ELSEIF-ELSE, you can run statements based on several conditions. The syntax is given below:

IF condition THEN
   statements;
ELSEIF elseif-condition THEN
   elseif-statements;
...
ELSE
   else-statements;
END IF;

Let’s review each block. If a condition in IF-THEN is: 

  • true – statements will be executed
  • false – elseif-condition will be evaluated 

If elseif-condition in ELSEIF-THEN is:

  • true – elseif-statements will be executed 
  • false – the next elseif-condition will be evaluated 

And if there is no true condition both in the IF-THEN and in the ELSEIF-THEN blocks, else-statements will be applied.

Examples of MySQL IF statements

In this section, we are going to review examples of the IF statements usage.

In the statement below, we declare the following condition using IF-THEN: if the sum of all empty entries in the STATUS column is more than 0, then in the output you will see the message “We have rows with NULL!!!”

DELIMITER $$
CREATE PROCEDURE GetEmtyStatus(
    OUT Message  VARCHAR(100))
BEGIN
    DECLARE sum_null DECIMAL(10,2) DEFAULT 0;
    SELECT COUNT(*) INTO sum_null FROM TASKS WHERE status IS NULL;
    IF sum_null > 0 THEN
        SET Message = 'We have rows with NULL!!!';
    END IF;
END$$
DELIMITER ;

In this IF-THEN-ELSE statement, we specify two conditions:

  • If the sum of all completed records in the STATUS column is 0, there will be the message “We have rows with NULL!!!”
  • If the sum of all completed records in the STATUS column isn’t 0, you will see “We do not have rows with NULL!!!” in the output
DELIMITER $$
CREATE PROCEDURE GetEmptyStatusNotNull(
    OUT Message  VARCHAR(100))
BEGIN
    DECLARE sum_null DECIMAL(10,2) DEFAULT 0;
    SELECT COUNT(*) INTO sum_null FROM TASKS WHERE STATUS IS NOT NULL;
    IF sum_null = 0 THEN
        SET Message = 'We have rows with NULL!!!';
                    ELSE 
        SET Message = 'We do not have rows with NULL!!!' ;
    END IF;
END$$
DELIMITER ;

Using the IF-THEN-ELSEIF-ELSE statement, we set multiple conditions:

  • If the sum of all empty values in the STATUS column is less than 3, the message “We have rows with NULL!!!” will be displayed
  • If the sum of all empty values in the STATUS column is more than 3, you will see “We have NULL, but result is valid!!!”
  • If the value does not satisfy two previous conditions, there will the message “We do not have rows with NULL!!!” in the output
DELIMITER $$
CREATE PROCEDURE CheckStatus(
    OUT Message  VARCHAR(100))
BEGIN
    DECLARE sum_null DECIMAL(10,2) DEFAULT 0;
    SELECT COUNT(*) INTO sum_null FROM TASKS WHERE STATUS IS  NULL;
    IF sum_null < 3 THEN
        SET Message = 'We have rows with NULL!!!';
    ELSEIF sum_null>3 THEN
        SET Message = 'We have NULL, but result is valid!!!';  
    ELSE 
        SET Message = 'We do not have rows with NULL!!!' ;
    END IF;
END$$
DELIMITER ;

MySQL functions

Introduction

In this section, we are going to explore the following MYSQL functions: IF, NULLIF, and IFNULL.

What is MySQL function?

Function in MySQL comprises a set of SQL statements with certain parameters that perform a task or operation and return a value as a result.

For example, the MySQL Version() function returns the current version of the MySQL database.

IF function: definition, syntax, and usage

Pay attention that the IF function is not the same as the IF statement.

The IF function returns a value for a specified condition and has the following syntax:

IF(expr,if_true_expr,if_false_expr)

The function returns if_true_expr if expr evaluates to true (it is not NULL). Otherwise, it returns if_false_expr. Based on how it is used, the IF function returns either a numeric value or a string.

Examples

Let’s review some examples of the IF function.

A simple example of MySQL IF function

The IF function accepts three expressions, and if the first one is true, not NULL, and not zero, it returns the second expression. Otherwise, the function returns the third expression.

In this example, as 1 is less than 2, the function will return NO:

SELECT IF(1>2, 'YES', 'NO');

In the statement below, the IF function will return:

  • 3 – if the condition is true
  • 5 – if the condition is false
SELECT IF(300<500, 3, 5);

Also, you can MySQL IF function with STRCMP to compare two strings. The following statement will return:

  • YES – if test and test1 are the same
  • NO – if test and test1 are different
SELECT IF(STRCMP("test","test1"), "YES", "NO");

MySQL IF function with aggregate functions

Aggregate functions calculate multiple values and provide a single value as a result. Using the IF function with aggregate functions can be beneficial to retrieve a specific output. 

In this section, we’ll review some aggregate functions that can be used with the IF function.

MySQL SUM IF

For example, if you want to know how many job tasks you have completed or still planned this month, you can use the function with the SUM aggregate function, as in the following query:

SELECT 
    SUM(IF(status = 'Completed', 1, 0)) AS Completed
FROM
    tasks;

In the query above, if the task status is completed or planned, the IF function returns 1 or 0. The SUM function determines the total number of planned and completed tasks depending on the returned value of the IF function.

MySQL COUNT IF

It is possible to combine the IF function with MySQL COUNT function. We choose the task’s status in the Tasks table using the following query:

SELECT DISTINCT
    status
FROM
    orders
ORDER BY status;

SELECT DISTINCT
    status
FROM
   tasks
ORDER BY status;

By combining the IF function with COUNT, we can find out how many tasks are in each status at any given time. As NULL values are not counted by the COUNT function, the IF function returns NULL if the status is not in the selected status, and 1 otherwise. Take a look at the following query:

SELECT 
    COUNT(IF(status = 'Completed', 1, NULL)) Completed,
    COUNT(IF(status = 'Reopened', 1, NULL)) Reopened,
    COUNT(IF(status = 'In Progress', 1, NULL)) 'In Progress',
    COUNT(IF(status = 'In Review', 1, NULL)) 'In Review',
    COUNT(IF(status = 'Planned', 1, NULL)) 'Planned'
FROM
    tasks;

You can also get a similar result without the IF function by using the GROUP BY clause and the COUNT function, as shown below:

SELECT status, COUNT(STATUS)
FROM tasks
GROUP BY status;

NULLIF function: definition, syntax, and usage

MySQL NULLIF is used to compare two expressions:

NULLIF(expr1, expr2)

The function returns NULL if both expressions are equal, otherwise, it returns the first expression. Let’s try it in practice:

SELECT NULLIF(25, 25);

As you can see, both expressions are equal, that is why the function returned NULL.

IFNULL function: definition, syntax, and usage

MySQL IFNULL is used to check for any NULL values in specified expressions:

IFNULL(expr_1, expr_2)

The function returns expr_1 if the expression is NOT NULL. Otherwise, it returns expr_2 if the expression is NULL. So, for example, if you run:

SELECT IFNULL(NULL, 500);

You will get such an output:

The function returned 500 because the expression in NULL.

Display N/A instead of NULL using MySQL IF function

Let’s create a demo MySQL table Company. Many employees in the Employees table have no data in the city column, and as a result, when we select employees, the city column shows NULL values. Take a look at the following query:

SELECT 
    employeeNumber, employeeName, age, city
FROM
    employees;

To improve the output, we can use the IF function to return N/A if the city is NULL, as shown by the following query:

SELECT
    employeeNumber,
    employeeName,
    IFNULL(city, 'N/A'),
    age
FROM
    employees;

Conclusion

In this article, we have found out the differences between MySQL IF statement and IF function and reviewed other MySQL functions. Also, we have shown a small part of the usage variations for both of them. Actually, there are a lot of MySQL statements, queries, and functions. That’s why, database administrators must have a reliable and easy-to-use tool to facilitate their routine work. For example, dbForge Studio for MySQL can become your indispensable assistant for database development, management, and administration. The tool has rich features among which you can find code completion and code formatting, and many more. For more information about dbForge Studio for MySQL, feel free to watch these video tutorials.  

Comments are closed.


Your subscription could not be saved. Please try again.
Confirm your email by clicking the link in your inbox!

Subscribe to our blog

to get the latest posts delivered to your inbox