SQL 'WHERE' versus 'ON' (inner join) [duplicate]

Question

When doing an SQL query,

is there any significant (or any at all) difference in performance between 'WHERE' and 'ON'?

I know there is a significant difference in the resulting set between the two for a LEFT or RIGHT JOIN.

What about an INNER JOIN?

Is there any drawback to using 'ON' for each of my selects rather than 'WHERE' at the end?

Did you even try to search? It's the first result on Google, and it's even right on this site. :( — bhamby
– bhamby, Commented Apr 24, 2013 at 21:14
But don't use the IN operator; in this case a JOIN is a lot faster ... — Stefan Brendle
– Stefan Brendle, Commented Apr 24, 2013 at 21:15
I think here's a similar thread with good explanation: stackoverflow.com/questions/1018822/… — Ashish Sachdeva
– Ashish Sachdeva, Commented Apr 24, 2013 at 21:26

Community · Accepted Answer · 2020-07-29 18:25:24Z

1

Performance wise, they should be the same. However, see this article for an opinion of an industry veteran regarding the readability and maintenace-friendliness.

While the old-style joins still work, you can't do an outer join anymore

edited Jul 29, 2020 at 18:25

CommunityBot

11 silver badge

answered Apr 24, 2013 at 21:15

Filip

2,3591 gold badge19 silver badges25 bronze badges

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

Comments

Mathew Thompson · Accepted Answer · 2013-04-24 21:15:42Z

1

Execution plans for both are identical. Many people prefer the use of ON though.

answered Apr 24, 2013 at 21:15

Mathew Thompson

56.5k15 gold badges131 silver badges151 bronze badges

Collectives™ on Stack Overflow

SQL 'WHERE' versus 'ON' (inner join) [duplicate]

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related