{"id":2379,"date":"2013-05-14T22:26:00","date_gmt":"2013-05-15T06:26:00","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=2379"},"modified":"2020-04-11T02:02:20","modified_gmt":"2020-04-11T09:02:20","slug":"mysql-nullif","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-control-flow-functions\/mysql-nullif\/","title":{"rendered":"MySQL NULLIF"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn about the MySQL NULLIF<\/strong> function and how to use it to prevent the division by zero error in a query.<\/p>\n\n\n\n

Introduction to MySQL NULLIF function<\/h2>\n\n\n\n

The NULLIF<\/code> function is one of the control flow functions that accepts 2 arguments. The NULLIF<\/code> function returns NULL<\/code> if the first argument is equal to the second argument, otherwise it returns the first argument.<\/p>\n\n\n\n

The syntax of the NULLIF<\/code> function is as follows:<\/p>\n\n\n

NULLIF(expression_1,expression_2);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The NULLIF<\/code> function returns NULL<\/code> if expression_1 = expression_2<\/code> is true<\/code> , otherwise it returns expression_1<\/code><\/p>\n\n\n\n
Notice that the NULLIF<\/code> function is similar to the following expression that uses the CASE expression<\/a>:<\/p>\n\n\n
CASE WHEN expression_1 = expression_2\n   THEN NULL\nELSE\n   expression_1\nEND<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
MySQL NULLIF examples<\/h2>\n\n\n\n
Let’s take a look at some examples of using the NULLIF<\/code> function to understand how it works.<\/p>\n\n\n
SELECT<\/span> NULLIF<\/span>(1<\/span>,1<\/span>); -- return NULL<\/span><\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
SELECT<\/span> NULLIF<\/span>(1<\/span>,2<\/span>); -- return 1<\/span>\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
SELECT<\/span> NULLIF<\/span>('MySQL NULLIF'<\/span>,'MySQL NULLIF'<\/span>); -- return NULL<\/span>\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
SELECT<\/span> NULLIF<\/span>('MySQL NULLIF'<\/span>,'MySQL IFNULL'<\/span>); -- return MySQL NULLIF<\/span>\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
SELECT<\/span> NULLIF<\/span>(1<\/span>,NULL<\/span>); -- return 1 because 1 <=> NULL<\/span>\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
SELECT<\/span> NULLIF<\/span>(NULL<\/span>,1<\/span>); -- return NULL the first argument<\/span>\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
How the statements work.<\/p>\n\n\n\n
Try It Out<\/a><\/p>\n\n\n\n
Try It Out<\/a><\/p>\n\n\n
Try It Out<\/a><\/p>\n\n\n
Try It Out<\/a><\/p>\n\n\n
Try It Out<\/a><\/p>\n\n\n
Try It Out<\/a><\/p>\n\n\n
Note that you should not confuse the NULLIF<\/code> function a similar function called IFNULL function<\/a>.<\/p>\n\n\n\n