I can do the same query in two ways as following, will #1 be more efficient as we don't have join?
select table1.* from table1
inner join table2 on table1.key = table2.key
where table2.id = 1
select * from table1
where key = (select key from table2 where id=1)
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, 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.