exist & in

select a.* from A a 
where exists ( select 1 from B b where a.id=b.id )

复制代码
 1 public List exist(){
 2   List result;
 3   Array A=(select * from A)
 4 
 5   for(int i=0; i<A.length; i++) {
 6      if(exists(A[i].id) {    //执行select 1 from B b where b.id=a.id是否有记录返回
 7          result.add(A[i]);
 8      }
 9   }
10   return result;
11 }
复制代码

 

2.in

select * from A
where id in ( select id from B )

复制代码
 1 public List in(){
 2   List result;
 3   Array A = (select * from A);
 4   Array B = (select id from B);
 5 
 6   for(int i=0; i<A.length; i++) {
 7      for(int j=0; j<B.length; j++) {
 8         if(A[i].id == B[j].id) {
 9            result.add(A[i]);
10            break;
11         }
12      }
13   }
14   return result;  
15 }
复制代码

A表10000条记录,B表1000000条记录,那么最多有可能遍历10000*1000000次,效率很差.

A表10000条记录,B表100条记录,那么最多有可能遍历10000*100次,遍历次数大大减少

结论:

子查询表大的用exists,子查询表小的用in

3. 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';

 

posted @   等风来。。  Views(220)  Comments(0Edit  收藏  举报
努力加载评论中...
------------------------------------------------------------------------------------------------------------ --------------- 欢迎联系 x.guan.ling@gmail.com--------------- ------------------------------------------------------------------------------------------------------------
点击右上角即可分享
微信分享提示