SQL中in和exists用法的区别

一、in 与 exists 的区别

1、exists、not exists 一般都是与子查询一起使用,In 可以与子查询一起使用,也可以直接in (a,b.....)

2、exists 会针对子查询的表使用索引,not exists 会对主子查询都会使用索引。in 与子查询一起使用的时候,只能针对主查询使用索引,not in 则不会使用任何索引

  注意:一直以来认为 exists 比 in 效率高的说法是不准确的。

  in 是把外表和内表作 hash 连接,而 exists 是对外表作 loop 循环,每次 loop 循环再对内表进行查询。

  如果查询的两个表大小相当,那么用 in 和 exists 差别不大。

  如果两个表中一个较小,一个是大表,则子查询表大的用exists,子查询表小的用in:

-- 例如:表A(小表),表B(大表)
-- 例子1
select * from A where cc in (select cc from B)
-- 效率低,用到了A表上cc列的索引;

select * from A where exists(select cc from B where cc=A.cc)
-- 效率高,用到了B表上cc列的索引。

-- 相反的例子2:
select * from B where cc in (select cc from A)
-- 效率高,用到了B表上cc列的索引;

select * from B where exists(select cc from A where cc=B.cc)
-- 效率低,用到了A表上cc列的索引。

  not in 和 not exists 如果查询语句使用了 not in 那么内外表都进行全表扫描,没有用到索引;而 not extsts 的子查询依然能用到子表上的索引。

  因为not in实质上等于!= and != ...,因为 != 不会使用索引,故 not in 不会使用索引。

  所以无论那个表大,用 not exists 都比 not in 要快

3、exists 与 in 都可以实现一个目的,二者都可以用来过滤数据。

select count(1) from t1;     --160W
select count(1) from t2;     --90W

SELECT count(1)
FROM t1 a
WHERE EXISTS (SELECT accountid
FROM t2 b
WHERE a.keyid = b.keyid AND a.ideaid = b.ideaid);
--主大子小,不适合使用exist,因为exist只会利用子表t2的复合索引keyid+ideaid,而子表内容要小与主表,主表由于无法使用索引,查询效率低下.

select count(1) from t1 a where accountid in (SELECT accountid
FROM t2 b
WHERE a.keyid = b.keyid AND a.ideaid = b.ideaid);
--主大子小,适合用in,因为in只会使用主表t1里面的复合主键keyid-ideaid,在主表大于子表的情况下,会很好的利用主表的索引

  后二条sql的执行结果都是一样的,说明 exists 与 in 在用法上可以达到一个目的,不同的地方是:

(1)性能的考虑:此时就按子表大主表小用exists,子表小主表大用 in 的原则就可以。

(2)写法的不同:exists 的where条件是"......  where exists (..... where a.id=b.id)",in的where条件是: " ...... where  id in ( select id .... where a.id=b.id)"

4、exists 的原理

  exists 做为 where 条件时,是先对 where 前的主查询询进行查询,然后用主查询的结果一个一个的代入exists的查询进行判断,如果为真则输出当前这一条主查询的结果,否则不输出。

-- 比如如下:
表A
ID NAME
1 A1
2 A2
3 A3

表B
ID AID NAME
1 1   B1
2 2   B2
3 2   B3

表A和表B是一对多的关系 A.ID --> B.AID

SELECT ID , NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE A.ID = B.AID)
-- 执行结果为
1 A1
2 A2 

  原因可以按照如下分析:

SELECT ID , NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID = 1)
-->SELECT * FROM B WHERE B.AID = 1有值返回真所以有数据

SELECT ID , NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID = 2)
-->SELECT * FROM B WHERE B.AID = 2有值返回真所以有数据

SELECT ID , NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID = 3)
-->SELECT * FROM B WHERE B.AID = 3无值返回真所以没有数据 

  NOT EXISTS 就是反过来。

5、in 与 = 的区别

select name from student where name in (zhang,wang,li,zhao);
--
select name from student where name=zhang or name=li or name=wang or name=zhao
-- 的结果是相同的。

  in 的字段也可以与其它字段建复合索引

-- 比如:T1包含下面key, accountd,groupid
SELECT   *
  FROM   T1  a
 WHERE       a.groupid = 2001
         AND a.accountid = 1001
         AND a.key IN (abc, def, ala);
-- 上面的sql可以将accountid,key建成复合索引

 

SQL中in和exists用法的区别

上一篇:【INDEX】Postgresql索引介绍


下一篇:Android ART虚拟机