MySQL NULLIF

Summary: in this tutorial, you will learn about the MySQL NULLIF function and how to use the NULLIF function 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 in MySQL 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)

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 operator:

CASE WHEN expression_1 = expression_2    THEN NULL ELSE    expression_1 END;

Please be careful not to confuse the NULLIF function with the IFNULL function.

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

MySQL NULLIF examples

Let’s take a look at the following statements:

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 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

The NULLIF function is often used to prevent the division by zero error. 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

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, NULLIF(0,0) returns NULL that makes the statement return NULL.

Let’s take a look at the following example:

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

SELECT *  FROM orders WHERE orderDate BETWEEN '2003-06-01' AND  '2003-06-30';

Second, you can calculate the number of shipped orders / the number of cancelled orders in June 2003:

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 cancelled order created.

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 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 NULL.

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.

Related Tutorials

• MySQL IFNULL

原文链接：http://outofmemory.cn/mysql/function/mysql-nullif