首页 > 代码库 > exist & in

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‘;