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

John Woo · Accepted Answer · 2013-03-27 09:09:00Z

17

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, 2013 at 9:09

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Rashidul Islam Over a year ago

thanks for your response. if i use LEFT JOIN which one will be faster in performance?

John Woo Over a year ago

a good article for your question: INNER JOIN vs LEFT JOIN performance

Andy Lester · Accepted Answer · 2013-09-08 05:24:18Z

3

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.

answered Sep 8, 2013 at 5:24

Andy Lester

94.2k16 gold badges106 silver badges162 bronze badges

Comments

0rochimaru · Accepted Answer · 2014-12-02 23:07:38Z

1

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

answered Dec 2, 2014 at 23:07

0rochimaru

671 silver badge9 bronze badges

Collectives™ on Stack Overflow

Query Performance INNER JOIN ON AND comparison

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related