sql server - SQL inner join vs subquery -

i working on following queries:

query 1: select * taba inner join tabb on taba.id=tabb.id query 2: select * taba id in (select id tabb) query 3: select taba.* taba inner join tabb on taba.id=tabb.id 

i investigate these queries sql server profiler , found interesting facts.

• query 1 takes 2.312 seconds
• query 2 takes 0.811 seconds
• query 3 takes 0.944 seconds

taba 48716 rows

tabb 62719 rows

basically asking why query 1 taking long time, not query 3. know 'sub query' slower inner join here query 2 fastest; why?

if had guess it's because query 1 pulling data both tables. queries 2 , 3 (aprox same time) pulling data taba.

one way check running following:

set statistics time on set statistics io on 

when ran

select * sys.objects 

i saw following results.

sql server parse , compile time:     cpu time = 0 ms, elapsed time = 104 ms.  (242 row(s) affected) table 'worktable'. scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. table 'sysschobjs'. scan count 1, logical reads 10, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. table 'syssingleobjrefs'. scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. table 'syspalnames'. scan count 1, logical reads 2, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.  sql server execution times:    cpu time = 0 ms,  elapsed time = 866 ms. 

you can take @ # of scans, logical reads , physical reads each query. physical reads of course take longer , represent reading disk cache. if of reads logical reads table in cache.

i willing bet if see lot more logical reads on tabb on query 1 on 2 , 3.

edit:

just out of curiosity did tests , blogged results here.