mysql exists in join_子查询、left join、 exists 替代 not in

如果要实现一张表有而另外一张表没有的数据时，我们通常会这么写：

SELECT id FROM user WHERE id NOT IN (SELECT id FROM student)

not in不会走索引, 可以用exists替代

SELECT id FROM user WHERE NOT exists (SELECT id FROM student WHERE user.id = student.id)

也可以用left join替代

SELECT id FROM user LEFT JOIN student ON user.id = student.id WHERE student.id = null

in: 是把外表和内表作hash 连接，在将user.id = student.id的数据保留；这样当子查询记录多时，hash连接后的数据量是特别大的。

EXISTS: 包括 NOT EXISTS子句的返回值是一个Boolean值; EXISTS先查出user表，将每一条记录的id带入子查询，根据查询返回的结果是否空值来确定当条记录是否加入结果集。exists是对外表作loop循环，每次loop循环再对子表进行查询。这样子表的查询语句会执行 (外表查出的记录数) 次; 所以一直以来认为exists比in效率高的说法是不准确的。

可以理解为：将外查询表的每一行，代入子查询作为检验，如果子查询返回的结果取非空值，则EXISTS子句返回TRUE，这一行可作为外查询的结果行，否则不能作为结果。

in 和 exists的区别场景:

IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。

如果子查询得出的结果集记录较少，主查询中的表较大且又有索引时应该用in, 反之如果外层的主查询记录较少，子查询中的表大，又有索引时使用exists。其实我们区分in和exists主要是造成了驱动顺序的改变(这是性能变化的关键)，如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询，所以我们会以驱动表的快速返回为目标，那么就会考虑到索引及结果集的关系了 ，另外IN时不对NULL进行处理。

EXISTS与IN的使用效率的问题，通常情况下采用exists要比in效率高，因为IN不走索引，但要看实际情况具体使用

而not in 和not exists: 如果查询语句使用了not in 那么内外表都进行全表扫描(hash连接后的数据进行逐条判断user.id = student.id是否成立)，不会用到索引; 而not exists 的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快。

子查询

MySQL从4.1版本开始支持子查询，使用子查询进行SELECT语句嵌套查询，可以一次完成很多逻辑上需要多个步骤才能完成的SQL操作。

缺点：

子查询虽然很灵活，但是执行效率并不高，原因：执行子查询时，MySQL需要创建临时表，查询完毕后再删除这些临时表，所以，子查询的速度会受到一些影响，这里多了一个创建和销毁临时表的过程。对子查询结果进行操作时索引会失效

优化方式：

可以使用JOIN、LEFT JOIN等方式代替子查询，这些方式不需要建立临时表，因此速度比子查询快。

扩展

在插入记录前，需要检查这条记录是否已经存在，只有当记录不存在时才执行插入操作，可以通过使用 EXISTS 条件句防止插入重复记录。

inset into user(username)select '123456' from dual where not EXISTS (select username from user where username='123456')