SQL query Optimization — Use Subqueries or not

So I have two queries

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


select key, A, B, C, D, E, F
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?

>Solution :

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.

Leave a Reply