which is best for performance filtering on where or filtering on join

ahmed salah

I work on sql server 2019 i don't know which is best for performance filtering on where condition or filtering on join
with another meaning which is best

first query

 select A.*  from A
 inner join B on A.ID=B.ID
 INNER JOIN C ON C.ID=B.ID
 WHERE B.name='Hazard'
    
 OR

second query

 select A.*  from A
 inner join B on A.ID=B.ID and B.name='Hazard'
 INNER JOIN C ON C.ID=B.ID

as
suppose table A have milion rows
table B have 2 milion rows
table c have 500 thousand rows

so which is best first query or second query

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sachin Singh4y ago
It depends when we left join and if there are any repeated data then it behaves as cross join to overcome that we use the filter on Join directly using AND , in this case if you will use where then that won't work.
Vishal Yelve4y ago
Theoretically, no, it shouldn't be any faster. The query optimizer should be able to generate an identical execution plan. However, some database engines can produce better execution plans for one of them (not likely to happen for such a simple query but for complex enough ones). You should test both and see (on your database engine).