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
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?
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.