SQL 中 EXISTS 用法详解

EXISTS

首先来看一个示例

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

像列 LIKE 字符串或者列 BETWEEN 值 1 AND 值 2这样的谓词需要指定 2 个以上的参数，而 EXIST 的左侧并没有任何参数。因为 EXIST 是只有 1 个参数的谓词。所以，EXIST 只需要在右侧书写 1 个参数，该参数通常都会是一个子查询。如果子查询返回任何行，EXISTS 子查询为 TRUE。

EXISTS 语句是对外表作 loop 循环，每次 loop 循环再对内表进行查询。或许你一直认为 EXISTS 比 IN 语句的效率要高，这种说法是不准确的。

那到底该如何选择呢？

• 如果查询的两个表大小相当，那么用 EXISTS 和 IN 差别不大
• 如果两个表中一个较小，一个是大表，则子查询表大的用 EXISTS，子查询表小的用 IN

来看两个示例，假设 表 t1 为小表，表 t2 为大表

1. 子查询为表 t2

select * from t1 
where id in (select id from t2) 
//效率低，用到了 t1 表上 id 列的索引；
 
select * from t1 
where exists(select id from t2 where id=t1.id) 
//效率高，用到了 t2 表上 id 列的索引。 

1. 子查询为表 t1

select * from t2 
where id in (select id from t1) 
//效率高，用到了t2 表上 id 列的索引；
 
select * from t2 
where exists(select id from t1 where id=t2.id) 
//效率低，用到了 t1 表上 id 列的索引。

上述示例中，在大表查询中使用了索引的用法效率更高。

NOT EXISTS

就像 EXIST 可以用来替换 IN 一样， NOT IN 也可以用 NOT EXIST来替换。

select * from t2 
where id not in (select id from t1) 
//效率低，内外表都进行全表扫描，不能用到索引；

select * from t2 
where not exists(select id from t1 where id=t2.id)
//效率高，用到了 t1 表上 id 列的索引。

如果查询语句使用了 NOT IN 那么内外表都进行全表扫描，没有用到索引；而 NOT EXISTS 的子查询依然能用到表上的索引。所以可以得出结论:

• 所以无论哪个表大，用 NOT EXISTS 都比 NOT IN 要快。

-- END --