Mysql:通过NOT EXISTS选择参与全部事件的数据

Mysql:通过NOT EXISTS选择参与全部事件的数据 – WhiteNight's Site

2023年10月12日

文章访问量:21

标签:Mysql

总结下NOT EXISTS嵌套使用的详解和心得。

什么是NOT EXISTS

简单介绍

先来说说EXISTS,如果存在一条或多条记录就返回true,不然返回false。即EXISTS只返回真值或假值,在SQL中作为条件判断语句使用。简单来说EXISTS就像一个C语言中的循环语句。具体用例可以参考这篇文章181. 超过经理收入的员工。这里只讲下SQL语句

SELECT A.name AS Employee
FROM Employee AS A
WHERE EXISTS(
  SELECT B.*FROM Employee AS B
  WHERE A.managerId=B.id AND A.salary>B.salary
)

内层的EXISTS就是一个for循环,而整个SQL语句就像一个双重for循环语句。只要EXISTS中有一行的数据满足WHERE条件,那内层的循环就会返回true。而外层的SELECT就知道:“哦,这一行name满足我的条件,所以我要从数据库中选取该行的数据”。

而NOT EXISTS那就是顾名思义,如果把EXISTS改成NOT EXISTS,此时若某一行的数据满足内层的WHERE条件,那么WHERE NOT EXISTS就会返回true。此时对于外层的SELECT来说就是:“哦,这一行满足内层的WHERE条件,而我要找的是不满足该条件的数据,所以不从数据库中选取该数据”。

NOT EXISTS实例

Leetcode题目

以Leetcode题目:1045. 买下所有产品的客户为例。

题目要求找出“买下所有产品”的客户,简单来说就是找出满足“买下所有产品”条件的数据行。

用EXISTS去写,实在是不好想。直接选取买了全部产品的客户,那该如何遍历Customer表呢?WHERE条件该怎么处理呢?不如反过来想:选取没有购买全部产品的客户。如果在product表和customer表中,找不齐某个用户购买的产品id(来自product表),就说明这个客户没有购买全部产品。那最后加个NOT不就能选取买了全部产品的客户吗?

那先来想下怎么选取“没有购买全部产品的客户”。由于Custom表有重复值,那么得先去重。

SELECT A.customer_id FROM (SELECT DISTINCT customer_id FROM Customer) AS A

写者注

如果写成SELECT DISTINCT A.customer_id FROM CUstomer AS A,在Leetcode运行会超时。这点可能涉及到数据库的优化之类的(SQL优化:谓词下推)。我的个人理解是前者先从Customer表中去重,从Customer表中选取不重复的数据生成更小的虚拟表virtualCustomer表。对这个更小的表进行WHERE条件的判断并将结果作为新的虚拟表virtualCustomer2,最后SELECT是从vitrualCustomer2中选取的。
但是后者的DISTINCT是放SELECT中的。SELECT虽然写在SQL语句的开头,但实际上它在数据库中的执行顺序是在最后。相当于我先从Customer表中选取所有数据生成虚拟表vitrualCustomer。接下来的WHERE条件判断会对表中所有id都执行一遍,执行的结果生成另一个虚拟表vitrualCustomer2,最后再对这个虚拟表去重SELECT得到我们要的数据。
前者是先筛掉重复数据,再对剩下的数据进行条件判断。后者是先条件判断,再筛掉重复的customer_id。明显后者在条件判断的时候还会对多余的数据进行处理,效率比前者低。
更具体的内容会在本文的后半部分提到。我之前没接触过SQL优化,这里是碰到这个问题发现自己实在没法解决,按着“可能和SQL优化有关”的思路就去网上找了下,最后找到了谓词下推这个SQL优化的方法。

id去重之后,既然要选“没有购买全部产品”的客户,最后再加NOT得到“买了全部产品客户”的集合。很明显最外层要一个NOT EXISTS。

# Write your MySQL query statement below
SELECT A.customer_id FROM (SELECT DISTINCT customer_id FROM Customer) AS A
WHERE NOT EXISTS(
    
)

接下来想下怎么选择“没有购买全部产品”的客户。很明显要从Product表中先选产品id,再在Customer表中查询。因为只要没有购买全部产品,那么在Customer表中查询没被购买的产品id时,查询结果会返回空。

这意味着我们不能用EXISTS。因为如果用EXISTS,那么只要该客户买了任意产品,EXISTS就会返回真值。最后就变成了“查找不存在(第一次NOT EXISTS)有购买过任意一件产品(第二次EXISTS)的客户”。而我们要的是“查找不存在(第一次NOT EXISTS)没有购买过任何一件产品(第二次NOT EXISTS)的客户”.

确定要用NOT EXISTS之后,那就是写两个判断条件去匹配,不难理解就不作解释了。

由于EXISTS最后返回的都是布尔值,所以SELECT 什么都是没问题的,SELECT 1也是OK的。最后大致代码如下

# Write your MySQL query statement below
SELECT A.customer_id FROM (SELECT DISTINCT customer_id FROM Customer) AS A
WHERE NOT EXISTS(
    SELECT 1 FROM Product AS B
    WHERE NOT EXISTS(
        SELECT 1 FROM Customer AS C
        WHERE A.customer_id=C.customer_id AND B.Product_key=C.Product_key
    )    
)

写者注

回到我们一开始提到的SQL优化问题。假设表中有100个id,其中80条是重复的。在 A.customer_id=C.customer_id中,如果你把去重留到最后,那么这里的A.customer_id会包含这80条重复的id。此时只有当判断条件WHERE全部结束后才会去掉重复的id。而先去重(谓词下推)再进行判断, 那么A.customer_id=C.customer_id只需要从这20条id中一条一条判断,执行次数要比前者少很多。
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值