not in 和not exist
平时业务中免不了要判断2张表join之后其中一张A表中不存在B表中的数据
举个例子:
某网站包含两个表,Customers 表和 Orders 表。编写一个 SQL 查询,找出所有从不订购任何东西的客户。
Customers 表:
±—±------+
| Id | Name |
±—±------+
| 1 | Joe |
| 2 | Henry |
| 3 | Sam |
| 4 | Max |
±—±------+
Orders 表:
±—±-----------+
| Id | CustomerId |
±—±-----------+
| 1 | 3 |
| 2 | 1 |
±—±-----------+
例如给定上述表格,你的查询应返回:
±----------+
| Customers |
±----------+
| Henry |
| Max |
±----------+
此时我的第一想法就是用not in
,一把梭
select Name as Customers from Customers
where id not in (select CustomerId from Orders )
但是其实用not exists
也是可以的
select Name as Customers from Customers
where not exists (select 1 from Orders where Customers.id = Orders.CustomerId )
in
是把外表和内表作hash 连接(即将内表和外表做一个笛卡尔积,然后按照条件进行筛选),exists
是对外表作loop循环
,每次loop循环再对内表进行查询
。使用exists
关键字进行查询的时候,首先,我们先查询的不是子查询的内容,而是查我们的主查询的表,也就是说,我们先执行的sql
语句是:
select Name from Customers
然后,根据表的每一条记录,执行以下语句,依次去判断where后面的条件是否成立:
select 1 from Orders where Customers.id = Orders.CustomerId
;exists
语句中只要返回true or false
就可以了,所以可以直接select id
或者select 1
一直以来认为exists
比in
效率高的说法是不准确的。
如果查询的两个表大小相当,那么用in
和exists
差别不大。
如果两个表中一个较小,一个是大表,则子查询表大的用exists
,子查询表小的用in
。
demo案例来自力扣
https://leetcode-cn.com/problems/customers-who-never-order/submissions/