SQL query Optimization -- Use Subqueries or not

Question

So I have two queries

select key, A, B, C, D, E, F
from
(select key, A, B, C from table1) t1
join 
(select key, D, E, F from table2) t2
on t1.key = t2.key

and

select key, A, B, C, D, E, F
from
table1 join table2 
on table1.key = table2.key

Are these queries the same in terms of querying time?
If not, which one runs faster and why?

Does this answer your question? [Join vs. sub-query](https://stackoverflow.com/questions/2577174/join-vs-sub-query) — Shmiel, Jul 01 '22 at 14:09

score 2 · Accepted Answer · answered Jul 01 '22 at 14:10

In practice, a good optimizer might execute the first subquery version using the second's query plan. But if not, then the first query might execute more slowly than the second. This is because the subqueries on the two tables would force your SQL database to materialize the two tables as intermediate result sets, before joining them. Doing so takes execution time, space, and also precludes the use of any index during the join of the subqueries. So, you should probably lean towards using the second version.

SQL query Optimization -- Use Subqueries or not

1 Answers1