sql exist vs in

這邊比較一下sql exist 和in的差別


Select * from T1 where x in ( select y from T2 )

LIKE:

select *

from t1, ( select distinct y from t2 ) t2 >

where t1.x = t2.y;

如果使用EXISTS，如同上述的查詢結果
select * from t1 where exists ( select null from t2 where y = x ) 

LIKE: 

for x in ( select * from t1 ) 
loop 
if ( exists ( select null from t2 where y = x.x ) 
then 
OUTPUT THE RECORD 
end if 
end loop 


因此當子查詢比主查詢小的時候使用in  ,反之則使用exist
外大內小=IN，外小內大=EXISTS








參考了http://tw.knowledge.yahoo.com/question/question?qid=1306041509015