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

1	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 expression:

1 2 3 4 5

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.

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

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

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

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

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

1	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

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:

1	SELECT 1/0; -- cause error

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

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

1 2 3 4 5 6

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 cancelled orders in June 2003, you use the SUM and IF functions.

1 2 3 4

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

1 2 3 4 5 6 7

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