leetcode一个题目及思考：in和exists到底什么时候用最合适？
题目如下：
编写一个 SQL 查询，找出每个部门工资最高的员工。
（同样的解法MySQL 8.0之后也可以方便的使用开窗函数去解，可以参考前几篇文章）
https://mp.weixin.qq.com/s/BFf3-DBuyHjwldac3Hy95g
我们本次使用in的方式：
我们来看下in的几种等效方式
Employee 表包含所有员工信息
Department 表包含公司所有部门的信息
预期结果
解决思路：每个部门的最高工资，都能想到按照部门分组，取max（salary），
关键是需要去到名字，名字存在employee中，group by的字段加不上去，比较麻烦。
可能就会有同学想到子查询用salary和部门id找到人的名字。
我突然想起看到有同学写的sql：
select * from table A where id =（select xx_id from table B where name='XXX'）
and dep_id=（select dep_id from table B where name='XXX'）
我只想问：
select * from table A where (id,dep_id) in (select * from table_B where name='XXX')
他不香吗？但是可能会有很多同学用的多字段in 比较少，不容易想起。而且上面写法效率相当低下。
我们来看下这题：直接说leetcode上的写法。
SELECT
e.NAME AS Employee,
e.Salary,
d.NAME AS Department
FROM
department d
LEFT JOIN employee e ON d.id = e.dep_id
WHERE
-- leetcode解法，多个字段in 这个可能平时用的少的，不容易想到
-- (e.dep_id,e.salary) in (SELECT dep_id, MAX( salary ) AS salary FROM employee GROUP BY dep_id )
EXISTS (
SELECT
*
FROM
( SELECT dep_id, MAX( salary ) AS salary FROM employee GROUP BY dep_id ) AS t
WHERE
e.dep_id = t.dep_id
AND e.salary = t.salary
)
还有其他改写方式么？来看看
MySQL 8.0也支持代码块了，改写后更美观了。可读性更强了
WITH cte AS ( SELECT dep_id, MAX( salary ) AS salary FROM employee GROUP BY dep_id ) SELECT
e.NAME AS Employee,
e.Salary,
d.NAME AS Department
FROM
department d
LEFT JOIN employee e ON d.id = e.dep_id
WHERE
EXISTS ( SELECT 1 FROM cte AS t WHERE e.dep_id = t.dep_id AND e.salary = t.salary )
还可以怎么改？MySQL 8.0 同样有ANY写法
SELECT
e.NAME AS Employee,
e.Salary,
d.NAME AS Department
FROM
department d
LEFT JOIN employee e ON d.id = e.dep_id
WHERE (e.dep_id,e.salary) = ANY (SELECT dep_id, MAX( salary ) AS salary FROM employee GROUP BY dep_id )
实际上这两个是等效的，这里我们不深究
SELECT s1 FROM t1 WHERE s1 <> ANY (SELECT s1 FROM t2);
SELECT s1 FROM t1 WHERE s1 <> SOME (SELECT s1 FROM t2);
我们看下上面两个语句执行计划：
本次我们不看join 的Block Nested Loop 这个join优化等下次在研究
我们可以看到使用exists时候我们是很好优化走索引的，本次子句内的数据较少，
看不出什么太大差异。下面我们来看下另外一个，看看怎么用in和exists
下面我们来看下今天主题 in exists。看看到底什么时候该使用in 什么时候使用exists
同样的实验：最近遇到的sql
表1：pr_scenic_appoint （5w数据)
表2：pr_scenic_appoint_tourist（80w数据）
废话不多说了，我们说下in 和exists的原理和结论吧
1.in：先查询in后面的pr_scenic_appoint_tourist表，然后再去pr_scenic_appoint 中过滤，也就是先执行子查询，结果出来后，再遍历主查询。
小结：in先执行子查询，也就是 in()所包含的语句。
子查询查询出数据以后，将前面的查询分为n次普通查询(n表示在子查询中返回的数据行数)
2.exists：主查询是内层循环，先查询出pr_scenic_appoint ，查询pr_scenic_appoint 就是外层循环，然后会判断是不是存在id和 pr_scenic_appoint_tourist表中的appoint_id相等，相等才保留数据，查询users表就是内层循环
这里所说的外层循环和内层循环就是我们所说的嵌套循环，而嵌套循环应该遵循“外小内大”的原则
小结：如果子查询查到数据，就返回布尔值true；如果没有，就返回布尔值false。
返回布尔值true则将该条数据保存下来，否则就舍弃掉。
exists查询，是查询出一条数据就执行一次子查询
总结：
1.尽量使用 小表驱动大表。
2.in适合于外表大而内表小的情况。
3.exists适合于外表小而内表大的情况。
留下的思考：join 的Block Nested Loop 怎么优化？下次我们研究下
