John John - 4 years ago 225
SQL Question

Performance of join vs pre-select on MsSQL

I can do the same query in two ways as following, will #1 be more efficient as we don't have join?

1



select table1.* from table1
inner join table2 on table1.key = table2.key
where table2.id = 1


2



select * from table1
where key = (select key from table2 where id=1)

Answer Source

These are doing two different things. The second will return an error if more than one row is returned by the subquery.

In practice, my guess is that you have an index on table2(id) or table2(id, key), and that id is unique in table2. In that case, both should be doing index lookups and the performance should be very comparable.

And, the general answer to performance question is: try them on your servers with your data. That is really the only way to know if the performance difference makes a difference in your environment.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download