为了账号安全,请及时绑定邮箱和手机立即绑定

MySQL NULLIF

标签:
MySQL

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

MySQL NULLIF - Orders Table

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

MySQL NULLIF returns NULL

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

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

点击查看更多内容
TA 点赞

若觉得本文不错,就分享一下吧!

评论

作者其他优质文章

正在加载中
  • 推荐
  • 评论
  • 收藏
  • 共同学习,写下你的评论
感谢您的支持,我会继续努力的~
扫码打赏,你说多少就多少
赞赏金额会直接到老师账户
支付方式
打开微信扫一扫,即可进行扫码打赏哦
今天注册有机会得

100积分直接送

付费专栏免费学

大额优惠券免费领

立即参与 放弃机会
意见反馈 帮助中心 APP下载
官方微信

举报

0/150
提交
取消