sanketahir1985 (2/19/2010)
Obviously first onebecause in first query both the table will be scanned only for condition a.name=b.name
In second query whole table2 will be scanned then it will be compared with table1.
Are you sure about that?
The query optimiser is not stupid. It is capable of recognising that the IN is a condition, just like the inner join, and optimising appropriately. In the absence of other conditions, it's likely that both tables will be scanned regardless (unless one of them is very, very small)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability