今天我们说下MySQL中in和exists的用法和区别:
exists表示存在，经常和子查询配合使用。我们来举个例子:
mysql> select * from course;
+----+----------+
| id | name |
+----+----------+
| 1 | 张三 |
| 2 | lucy |
| 3 | jack |
| 4 | Tom |
| 5 | Shanshan |
| 6 | Liulang |
| 7 | 胡扯 |
| 8 | 张三 |
| 9 | 李四 |
+----+----------+
9 rows in set (0.01 sec)
mysql> select * from teacher;
+----+---------------+------------+--------+
| id | name | time | scores |
+----+---------------+------------+--------+
| 1 | 张三 | 2020-06-28 | 77 |
| 2 | 李四 | 2020-06-28 | 88 |
| 3 | 王五 | 2020-06-28 | 66 |
| 4 | 赵六 | 2020-06-28 | 49 |
| 5 | 张三 | 2020-06-29 | 81 |
| 6 | 胡八 | 2020-06-29 | 92 |
| 7 | 柳九 | 2020-06-29 | 100 |
| 8 | 王五 | 2020-06-29 | 28 |
| 9 | thisisok | 2020-07-02 | 75 |
| 10 | whoisthis | 2020-07-02 | 16 |
| 11 | NULL | NULL | 92 |
| 12 | quickto22nick | 2020-07-02 | NULL |
+----+---------------+------------+--------+
12 rows in set (0.01 sec)
我们来看下exists的用法
mysql> select * from course where exists(select * from teacher where teacher.id = course.id);
+----+----------+
| id | name |
+----+----------+
| 1 | 张三 |
| 2 | lucy |
| 3 | jack |
| 4 | Tom |
| 5 | Shanshan |
| 6 | Liulang |
| 7 | 胡扯 |
| 8 | 张三 |
| 9 | 李四 |
+----+----------+
9 rows in set (0.00 sec)
exists用于检查子查询是否至少会返回一行数据,如果返回空集exists返回值就是false，否则就是true,该子查询实际不返回任何数据。
exists对外表用loop逐条查询，每次查询都会查看exists的条件语句
当子查询返回为真时，则外层查询语句将进行查询
当子查询返回为假时，外层查询语句将不进行查询或者查询不出任何记录。
not exists 与exists条件相反，就是当exists结果集有返回值时，条件为false否则为true
我们来看下实例:
mysql> select * from course where not exists(select * from teacher where course.id = teacher.id);
Empty set (0.01 sec)
可以看到如果以course表为基准，则不存在的没有，因为teacher表内容比course表多，那么如果我们将两个表倒过来呢
mysql> select * from teacher where not exists(select * from course where course.id = teacher.id);
+----+---------------+------------+--------+
| id | name | time | scores |
+----+---------------+------------+--------+
| 10 | whoisthis | 2020-07-02 | 16 |
| 11 | NULL | NULL | 92 |
| 12 | quickto22nick | 2020-07-02 | NULL |
+----+---------------+------------+--------+
3 rows in set (0.00 sec)
可以看出，teacher表有3条记录是course表中没有的
in查询
in查询相当于多个or条件的叠加，我们先来看下实例
查询tacher表id在1，2，3行的
mysql> select * from teacher th where th.id in (1,2,3);
+----+--------+------------+--------+
| id | name | time | scores |
+----+--------+------------+--------+
| 1 | 张三 | 2020-06-28 | 77 |
| 2 | 李四 | 2020-06-28 | 88 |
| 3 | 王五 | 2020-06-28 | 66 |
+----+--------+------------+--------+
3 rows in set (0.02 sec)
mysql> select * from teacher th where th.id!= 1 and th.id!=2 and th.id!=3;
+----+---------------+------------+--------+
| id | name | time | scores |
+----+---------------+------------+--------+
| 4 | 赵六 | 2020-06-28 | 49 |
| 5 | 张三 | 2020-06-29 | 81 |
| 6 | 胡八 | 2020-06-29 | 92 |
| 7 | 柳九 | 2020-06-29 | 100 |
| 8 | 王五 | 2020-06-29 | 28 |
| 9 | thisisok | 2020-07-02 | 75 |
| 10 | whoisthis | 2020-07-02 | 16 |
| 11 | NULL | NULL | 92 |
| 12 | quickto22nick | 2020-07-02 | NULL |
+----+---------------+------------+--------+
9 rows in set (0.01 sec)
in查询的子条件返回结果必须只有一个字段
in()语句只会执行一次，会将符合子查询条件的记录全都查出来，假设结果集为B，共有m条记录并且缓存起来，然后在将子查询条件的结果集分解成m个，再进行m次查询
select * from A where id in(select id from B)
我们来看下实例:
mysql> select * from teacher where id in(select id from course);
+----+----------+------------+--------+
| id | name | time | scores |
+----+----------+------------+--------+
| 1 | 张三 | 2020-06-28 | 77 |
| 2 | 李四 | 2020-06-28 | 88 |
| 3 | 王五 | 2020-06-28 | 66 |
| 4 | 赵六 | 2020-06-28 | 49 |
| 5 | 张三 | 2020-06-29 | 81 |
| 6 | 胡八 | 2020-06-29 | 92 |
| 7 | 柳九 | 2020-06-29 | 100 |
| 8 | 王五 | 2020-06-29 | 28 |
| 9 | thisisok | 2020-07-02 | 75 |
+----+----------+------------+--------+
9 rows in set (0.01 sec)
可以看出是要查找出teacher表中id和course表中id的交集对应的值
以上查询使用了in语句,in()只执行一次,它查出B表中的所有id字段并缓存起来.之后,检查A表的id是否与B表中的id相等,如果相等则将A表的记录加入结果集中,直到遍历完A表的所有记录
好了，今天先到这里吧
