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 argumentHow the statements work.
NULIF(1,1)returnsNULLbecause 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')returnsNULLbecause two arguments are the same stringNULLIF('MySQL NULLIF','MySQL NULLIF')returnsMySQL NULLIFbecause two strings are not equal.NULLIF(1,NULL)returns 1 because 1 is not equal toNULLNULLIF(NULL,1)return the first argument i.e.,NULL, becauseNULLis 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.