which gives best performance?

Question

Please go through the following query, and help me to understand which one is efficient and how?

In logical query execution, where clause will be executed after completion of join, so I thought the query 2 will gain the performance benefits. Is that right?

Query 1:

select a.*, b.* 
from table1 a 
join table2 b on a.colA = b.colA
where a.ColB = 'Somevalue'

Query 2:

select a.*, b.* 
from 
    (select * 
     from table1 
     where ColB = 'Somevalue' ) a 
join table2 b on a.colA = b.colA

Thanks in advance

probably duplicate of http://stackoverflow.com/questions/14052596/subquery-v-s-inner-join-in-sql-server — Deep, Nov 21 '14 at 05:32
*If you have two horses and you want to know which of the two is the faster then **race your horses*** - see [Which is faster?](http://ericlippert.com/2012/12/17/performance-rant/) by Eric Lippert for more background — marc_s, Nov 21 '14 at 05:41

score 0 · Answer 1 · answered Mar 29 '20 at 15:36

0

I don't know about SQL,
But my advice here is:

Less code + Less lines + Less words always gives better performance

, This rule is working in every language.

answered Mar 29 '20 at 15:36

Sorry IwontTell

466
10
29

score -1 · Accepted Answer · answered Nov 21 '14 at 07:25

-1

select a.*, b.* 
from table1 a 
join table2 b on a.colA = b.colA and a.ColB = 'Somevalue'

answered Nov 21 '14 at 07:25

Vasily

5,707
3
19
34

which gives best performance?

2 Answers2