Query Performance INNER JOIN ON AND comparison

Question

I would like to know which one is best regarding performance between the 2 queries stated below or do they perform identically?

First one: [without WHERE clause, just AND with ON]

SELECT related_tabid AS tabid, label, t.name
  FROM relatedlists r
     INNER JOIN tab t 
       ON t.tabid = r.tabid
          AND t.name = 'Leads'
          AND r.is_active=1  and r.related_tabid <> 0
          AND t.is_active=1
  ORDER BY label

Second one: [using WHERE clause, AND associated with where instead of ON ]

SELECT related_tabid AS tabid, label, t.name
  FROM relatedlists r
     INNER JOIN tab t 
       ON t.tabid = r.tabid
       WHERE t.name = 'Leads'
             AND r.is_active=1  and r.related_tabid <> 0
             AND t.is_active=1
  ORDER BY label

score 16 · Accepted Answer · answered Mar 27 '13 at 09:09

16

Both queries are the same because the join used is INNER JOIN. INNER JOIN basically it filters only rows that has at least a match on the other table. Even the two tables are interchange, the result is still the same.

But if you are joining them via LEFT JOIN, the two queries are different from each other and will yield different result.

answered Mar 27 '13 at 09:09

John Woo

258,903
69
498
492

1

thanks for your response. if i use `LEFT JOIN` which one will be faster in performance? – Rashidul Islam Mar 27 '13 at 09:11
2

a good article for your question: [INNER JOIN vs LEFT JOIN performance](http://stackoverflow.com/questions/2726657/inner-join-vs-left-join-performance-in-sql-server) – John Woo Mar 27 '13 at 09:14

score 3 · Answer 2 · answered Sep 08 '13 at 05:24

It looks to me that the only difference between the two queries is that one has the t.name = 'Leads' in the WHERE clause and one has it in the JOIN clause. Correct?

There is no difference between the two. The SQL optimizer will handle both of them identically. Do an EXPLAIN on each of them to verify.

score 1 · Answer 3 · answered Dec 02 '14 at 23:07

In theory, the first query is well just be wanting to put text comparison in the 'where', then joins in the definition of the 'logic' of union of tables and the 'where' is only used to specify values that vary when comparing Where t.name = 'Leads'; regards

Query Performance INNER JOIN ON AND comparison

3 Answers3