[大数据]MySQL EXISTS 关键字使用

是什么?

这个问题其实很好理解，简单来说，EXISTS 就是mysql中的一个关键字。

能干啥？

Exists关键字的主要作用就是检查SQL语句中的子查询是否至少会返回一行数据。使用EXISTS 关键字的子查询 其实并不返回数据，而是返回 布尔类型的true或 false。

怎么用？

那么EXISTS 关键字到底该怎么用呢，我们通过例子来进行简单讲解。所有的例子都基于MySQL 5.7版本。

EXISTS 语法

根据Mysql5.7版本的开发手册，其语法格式如下：

SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t2);

理论上来说，Exists 关键字后边的subquery(子查询，又名“相关子查询”) 可以以 SELECT * 或者 其他 任意字段 开头，比如你可以 使用：

SELECT * FROM t1 WHERE EXISTS (SELECT 'A' FROM t2);

或者

SELECT * FROM t1 WHERE EXISTS (SELECT '法外狂徒张三' FROM t2);

等等任何你想使用的字段查询，这都是可以的，因为 MySQL 会忽略掉Exists 关键字 后边subquery中的字段列表，因此在这个地方你使用任何字段都是可以的。但是在我们实际工作中，习惯使用
SELECT 1 来代替，如下所示：

SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t2);

EXISTS 执行顺序

我们以以下SQL语句为例：

SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t1.id = t2.id);

顺序如下：

1. 首先执行外部查询，并缓存结果集：

SELECT * FROM  t1；

2. 遍历外部查询结果集的每一行Record(记录)，然后代入子查询中作为条件进行查询。

SELECT 1 FROM t2 WHERE t1.id = t2.id；

3. 如果子查询有返回结果，则EXISTS子句返回true，则外部查询的这一行记录可作为结果行返回，否则不能作为结果。

SQL语句示例

为了便于演示，我们创建两张表结构非常简单的表，班级表（class）和 学生表（student）
class表结构：

CREATE TABLE `class` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL comment '班级名称',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4

-- 创建数据
INSERT INTO class(id, name)VALUES(1, '一年级');
INSERT INTO class(id, name)VALUES(2, '二年级');
INSERT INTO class(id, name)VALUES(3, '三年级');
INSERT INTO class(id, name)VALUES(4, '四年级');

student表结构：

CREATE TABLE `student` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `class_id` bigint(20) NOT NULL comment '班级表id',
  `name` varchar(100) DEFAULT NULL comment '学生名称',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4

-- 创建数据
INSERT INTO student (id, class_id, name) VALUES(1, 2, '张三');
INSERT INTO student (id, class_id, name) VALUES(2, 2, '李四');
INSERT INTO student (id, class_id, name) VALUES(3, 1, '王五');
INSERT INTO student (id, class_id, name) VALUES(4, 1, '赵六');
INSERT INTO student (id, class_id, name) VALUES(5, 1, '王七');
INSERT INTO student (id, class_id, name) VALUES(6, 3, '赵八');
INSERT INTO student (id, class_id, name) VALUES(7, 4, '周久');
INSERT INTO student (id, class_id, name) VALUES(8, NULL, '小二');

• 查询出一年级所有的学生

select * from student where exists (select 1 from class  where  class_id = 1);

• 查询出不是一年级的所有学生

select * from student where not exists (select 1 from class  where  class_id = 1);

• 查询出未分配班级的学生

select * from student s where exists (select 1 from class c where s.class_id is null );

• 查询出 学生主键id 等于 班级主键id的学生

select s.* from student s where exists (select 1 from class c where s.id = c.id);

因为表结构相对简单，无法构造较负责的查询逻辑。在项目中根据业务逻辑灵活使用即可。

EXISTS 和 IN 查询原理比较

其实能使用exists查询的语句基本上都能使用 IN 实现，但是两者还是一些区别的。

• EXISTS ： 外表先进行查询，然后循环查询结果，将查询结果放入exists的子查询中进行条件验证，true 则保留，false则丢弃。

• IN ： 先查询内表，将内表的查询结果当做条件提供给外表查询语句进行比较。

由上可以得出结论：

1. 外层小表，内层大表（左小表，右大表）： exists 比 in 的效率高。
2. 外层大表，内层小表（左大表，右小表）： in 比 exists 的效率高。