MySQL NULLIF

 MySQL NULLIF

Summary: in this tutorial, you will learn about the MySQL NULLIF function and how to use it to prevent the division by zero error in a query.

Introduction to MySQL NULLIF function

The NULLIF the function is one of the control flow functions that accepts 2 arguments. The NULLIF function returns NULL if the first argument is equal to the second argument, otherwise it returns the first argument.

The syntax of the NULLIF the function is as follows:

NULLIF(expression_1,expression_2);

The NULLIF function returns NULL if expression_1 = expression_2 is true , otherwise, it returns expression_1

Notice that the NULLIF the function is similar to the following expression that uses the CASE expression:

CASE WHEN expression_1 = expression_2
   THEN NULL
ELSE
   expression_1
END;

Note that you should not confuse the NULLIF function a similar function called IFNULL function.

MySQL NULLIF examples

Let’s take a look at some examples of using the NULLIF function to understand how it works.

SELECT NULLIF(1,1); -- return NULL

SELECT NULLIF(1,2); -- return 1

SELECT NULLIF('MySQL NULLIF','MySQL NULLIF'); -- return NULL

SELECT NULLIF('MySQL NULLIF','MySQL IFNULL'); -- return MySQL NULLIF

SELECT NULLIF(1,NULL); -- return 1 because 1 <=> NULL

SELECT NULLIF(NULL,1); -- return NULL the first argument

How the statements work.

• NULIF(1,1) returns NULL because 1 is equal to 1.
• NULLIF(1,2) returns 1, which is the first argument, because 1 is not equal to 2.
• NULLIF('MySQL NULLIF','MySQL NULLIF') returns NULL because two arguments are the same string
• NULLIF('MySQL NULLIF','MySQL NULLIF') returns MySQL NULLIF because two strings are not equal.
• NULLIF(1,NULL) returns 1 because 1 is not equal to NULL
• NULLIF(NULL,1) return the first argument i.e., NULL , because NULL is not equal to 1.

Using NULLIF function to prevent division by zero error

We often use the NULLIF function to prevent the division by zero error in a query. If the MySQL server has ERROR_FOR_DIVISION_BY_ZERO mode enabled, it will issue an error when a division by zero occurred.

See the following statement:

SELECT 1/0; -- cause error

In this case, you can use the NULLIF function to prevent the division by zero as follows:

SELECT 1/NULLIF(0,0); -- return NULL

Because zero is equal to zero, the expression NULLIF(0,0) returns NULL. As the result, the statement returns NULL.

Let’s take a look at the orders table in the sample database.

First, to get all orders created in June 2003, you use the following query:

SELECT 
    orderNumber, orderdate, requiredDate, shippedDate, status
FROM
    orders
WHERE
    orderDate BETWEEN '2003-06-01' AND '2003-06-30';

Second, calculate the number of shipped orders / the number of canceled orders in June 2003, you use the SUM and IF functions.

SELECT SUM(IF(status = 'Shipped',1,0)) / 
       SUM(IF(status = 'Cancelled',1,0))
FROM orders
WHERE orderDate BETWEEN '2003-06-01' and '2003-06-30';

MySQL issues an error because in June 2003 there was no canceled order created. It means that expression SUM(IF(status = 'Cancelled',1,0)) returns zero.

Third, to prevent the division by zero error, you use the NULLIF function as the following query:

SELECT 
    SUM(IF(status = 'Shipped', 1, 0)) / 
    NULLIF(SUM(IF(status = 'Cancelled', 1, 0)), 0)
FROM
    orders
WHERE
    orderDate BETWEEN '2003-06-01' AND '2003-06-30';

Because there was no canceled order created in June 2003, the SUM(IF(status = 'Cancelled',1,0) expression returns zero, which also makes the NULLIF(SUM(IF(status = 'Cancelled',1,0),0) expression returns a NULL value.

In this tutorial, we have introduced you to NULLIF function, which is very handy in some cases such as preventing division by zero error in queries.