SQL in, not in, exists, not exists

网上很多人在讨论In和Exists的性能对比，本人也搞不懂哪个性能更佳，只是一般在小表中用In，而在大表中用Exists。下面只是举例如果使用，以勉被喷了。

1、In。

1 select * from table1 a
2 where a.Id in (select Id from table2)
3 
4 select * from table1 a
5 where a.Id not in (select Id from table2)

 

2、Exists。

 1 select * from QVS_CUSTPACKINGSLIPPRINTJOUR a
 2 where exists 
 3 (select PRINTID from QVS_CustPackingSlipPrintTrans b 
 4 where a.PRINTID = b.PRINTID and b.DATAAREAID in ('QCN'))
 5 and a.DATAAREAID in ('QCN')
 6 
 7 select * from QVS_CUSTPACKINGSLIPPRINTJOUR a
 8 where not exists 
 9 (select PRINTID from QVS_CustPackingSlipPrintTrans b 
10 where a.PRINTID = b.PRINTID and b.DATAAREAID in ('QCN'))
11 and a.DATAAREAID in ('QCN')
posted @ 2012-09-03 09:29  Sprite.z  Views(291)  Comments(0)  Edit  收藏  举报