performance for a SQL clause with where and a left join

Question

Let say I have 2 tables, TblA et TblB. TblA has millions of records each month while TblB has detailled informations for products.

Which query will be faster :

select
    t1.*, t2.name
from 
    TblA as t1
left join 
    TblB as T2 on t1.idProduct = t2.idProduct
where 
    t1.month = 6

Or this one :

select 
    t1.*, t2.name
from 
    (select * from tblA where month = 6) as t1
left join 
    TblB as t2 on t1.idProduct = t2.idProduct

My guess is the second one will be faster because I pre-select only the month I want before doing the left join.

Are database system pre-build to handle the where clause before doing the left join?

Thanks!

Answer 1

run them both and look at the query plans
it will even give you a split on estimated time

Answer 2

It depends somewhat on which RDBMS you're using. However, most of them will interpret both of those queries as exactly the same. You can determine if that's true in your database by running an explain plan on both queries. If the plan is the same, then the optimizer has correctly identified that both queries are equivalent and come up with the same plan for both.

In general, SQL doesn't specify an order of operations; it lets the optimizer consider all of the options and choose the best one. Nesting the queries doesn't typically change that - almost any optimizer will flatten the query you give it as much as possible.