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 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 function is as follows:

NULLIF(expression_1,expression_2);Code language: SQL (Structured Query Language) (sql)

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

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

CASE WHEN expression_1 = expression_2
   THEN NULL
ELSE
   expression_1
END;Code language: SQL (Structured Query Language) (sql)

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 NULLCode language: SQL (Structured Query Language) (sql)

Try It Out

SELECT NULLIF(1,2); -- return 1
Code language: SQL (Structured Query Language) (sql)

Try It Out

SELECT NULLIF('MySQL NULLIF','MySQL NULLIF'); -- return NULL
Code language: SQL (Structured Query Language) (sql)

Try It Out

SELECT NULLIF('MySQL NULLIF','MySQL IFNULL'); -- return MySQL NULLIF
Code language: SQL (Structured Query Language) (sql)

Try It Out

SELECT NULLIF(1,NULL); -- return 1 because 1 <=> NULL
Code language: SQL (Structured Query Language) (sql)

Try It Out

SELECT NULLIF(NULL,1); -- return NULL the first argument
Code language: SQL (Structured Query Language) (sql)

Try It Out

How the statements work.

• NULIF(1,1) returns NULL because 1 is equal 1.
• NULLIF(1,2) returns 1, which is the first argument, because 1 is not equal 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 errorCode language: SQL (Structured Query Language) (sql)

Try It Out

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

SELECT 1/NULLIF(0,0); -- return NULLCode language: SQL (Structured Query Language) (sql)

Try It Out

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';Code language: SQL (Structured Query Language) (sql)

Try It Out

Second, calculate the number of shipped orders / the number of cancelled 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';Code language: SQL (Structured Query Language) (sql)

Try It Out

MySQL issues an error because in June 2003 there was no cancelled 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';Code language: SQL (Structured Query Language) (sql)

Try It Out

Because there was no cancelled 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.