在sql语句多表连接中，in、exists、join哪个效率更高一点？

Question

如题所述

Answer 1

EXISTS、IN与JOIN，都可以用来实现形如“查询A表中在（或不在）B表中的记录”的查询逻辑。

在查询的两个表大小相当的情况下，3种查询方式的执行时间通常是：
EXISTS <= IN <= JOIN
NOT EXISTS <= NOT IN <= LEFT JOIN
只有当表中字段允许NULL时，NOT IN的方式最慢：
NOT EXISTS <= LEFT JOIN <= NOT IN

但是如果两个表中一个较小，一个较大，则子查询表大的用exists，子查询表小的用in，因为in 是把外表和内表作hash 连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询。而无论那个表大，用not exists都比not in要快。这是因为如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；而not extsts 的子查询依然能用到表上的索引。

IN的好处是逻辑直观简单（通常是独立子查询）；缺点是只能判断单字段，并且当NOT IN时效率较低，而且NULL会导致不想要的结果。
EXISTS的好处是效率高，可以判断单字段和组合字段，并不受NULL的影响；缺点是逻辑稍微复杂（通常是相关子查询）。
JOIN用在这种场合，往往是吃力不讨好。JOIN的用途是联接两个表，而不是判断一个表的记录是否在另一个表。