学习笔记 - 数据库 - exists 和 in

exists 和 in

exists 子查询：先获取外表所有记录 ，再把外层查询结果放入内层 sql 测试，符合条件返回true则取出该行数据。先查询外层后查询内层

in 型子查询：先查询内层 sql ，把内层结果和外表作笛卡尔积，再过滤出条件满足的记录

带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。

对数据表：

user:
+----+-------+---------+
| id | name  | addr    |
+----+-------+---------+
|  1 | tom   | street1 |
|  2 | DIVA  | street2 |
|  3 | simis | street2 |
+----+-------+---------+

record:
+----+------+-----------+---------+
| id | msg  | date      | user_id |
+----+------+-----------+---------+
|  1 | bai  | 1233      |       1 |
|  2 | wds  | 132233    |       2 |
|  3 | west | asf132233 |       1 |
|  4 | hfd  | 34fw      |    NULL |
+----+------+-----------+---------+

有以下两句 sql ：

mysql> select * from user u where u.id in (select r.user_id from record r);
mysql> select * from user u where exists (select * from record r where r.user_id=u.id);

当使用 in 进行子查询时，会首先执行子句 select r.user_id from record r，获得表record 的所有 user_id 的集合，然后将外表的所有记录集取出，用每一条判断其 id 是否在子查询结果中。

当使用 exists 进行子查询时，会首先获取外表user的所有记录，然后用每一条和内表record 的每一条记录依次比对r.user_id=u.id为true|fasle,如果为true则取出外表user该条数据记录。

对使用 in 的子查询，执行过程：

1. 获取子查询结果

   表B:
   +----+---- +-----+---------+            +----+
   |    |     |     |         |            |    |
   +----+---- +-----+---------+            +----+
   |    |     |     |         |   in (sql) |    |
   +----+---- +-----+---------+   =====>   +----+
   |    |     |     |         |                符合条件的记录集 B1
   +----+---- +-----+---------+
   |    |     |     |         |
   +----+---- +-----+---------+

2. 获取外表的记录集

   表A:
   +----+-------+---------+
   |    |       |         |
   +----+-------+---------+
   |    |       |         |
   +----+-------+---------+
   |    |       |         |
   +----+-------+---------+

3. 将外表的记录一条一条的和内表查询结果进行条件过滤,最后获得结果

   表A:
   +----+-------+---------+                +----+
   |    |       |         |                |    |
   +----+-------+---------+        ×       +----+
   |    |       |         |                |    |  
   +----+-------+---------+                +----+
   |    |       |         |                  B1
   +----+-------+---------+
   
       u.id in (...)
           ====>       结果

对于 exists 子查询则是：

遍历外表的所有记录直接和内表的所有记录比对判断条件为 true | false

表A:                            表B:
+----+-------+---------+        +----+---- +-----+---------+
| a1 |       |         |        | b1 |     |     |         |
+----+-------+---------+        +----+---- +-----+---------+
| a2 |       |         |        | b2 |     |     |         |
+----+-------+---------+  loop  +----+---- +-----+---------+
| a3 |       |         |        | b3 |     |     |         |
+----+-------+---------+        +----+---- +-----+---------+
                                | b4 |     |     |         |
                                +----+---- +-----+---------+

        where (...)
            ====>       结果

例如 a1 会和b1、b2、b3、b4进行 where 的条件判断，a2、a3、a4同样也是。

总结：

1. in 是把外表和内表作hash 连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询。
2. in 的子句能够从内表中获取到记录，但是 exists 会依赖外表的字段。

参考：
SQL中关于EXISTS谓词的理解
Sql语句中IN和exists的区别及应用