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.

orders table

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

MySQL NULLIF example

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

MySQL NULLIF function example

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.

Was this tutorial helpful?