数据库查询-关于exists的使用

索引使用

1）select * from A where id in(select id from B)　　 -->效率低，用到了A表上id列的索引

2）select * from A where exists(select id from B where id=A.id) -->效率高，用到了B表上id列的索引

3）select * from B where id in(select id from A)　　 -->效率高，用到了B表上id列的索引

4）select * from B where exists(select id from A where id=B.id)　-->效率低，用到了A表上id列的索引

 

select a1 from a where a1 not EXISTS(select 1 FROM B WHERE a.a1=b.a1)

select a1 from a where a1 not in(select a1 FROM B WHERE a.a1=b.a1)

 

select * from t
where exists(select 1 from t1 where t.no=t1.no and t1.name='211')
and exists(select 1 from t2 where t.no=t2.no and t2.name='211')

与下面的查询结果一样
select no from t1 where t1.name='211'
interact
select no from t2 where t2.name='211'

union 并集操作，不包括重复行
union all 并集操作，包括重复行
intersect 交集，不包括重复行
minus 差操作，不包括重复行