SQL 'WHERE' versus 'ON' (inner join)

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](https://www.google.com/search?q=SQL+%27WHERE%27+versus+%27ON%27+%28inner+join%29&ie=utf-8&oe=utf-8&aq=t&rls=org.mozilla:en-US:official&client=firefox-a)? [It's the first result on Google, and it's even right on this site](http://stackoverflow.com/questions/1018822/inner-join-on-vs-where-clause). :( — bhamby, Apr 24 '13 at 21:14
But don't use the `IN` operator; in this case a JOIN is a lot faster ... — Stefan Brendle, Apr 24 '13 at 21:15
Here's another heavily upvoted [possible dupe](http://stackoverflow.com/q/1018822/119477) — Conrad Frix, Apr 24 '13 at 21:18
I think here's a similar thread with good explanation: http://stackoverflow.com/questions/1018822/inner-join-on-vs-where-clause — Ashish Sachdeva, Apr 24 '13 at 21:26

score 1 · Accepted Answer · edited Jul 29 '20 at 18:25

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 '20 at 18:25

Community

1
1

answered Apr 24 '13 at 21:15

Filip

2,285
1
19
24

score 1 · Answer 2 · answered Apr 24 '13 at 21:15

1

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

answered Apr 24 '13 at 21:15

Mathew Thompson

55,877
15
127
148

SQL 'WHERE' versus 'ON' (inner join)

2 Answers2

Linked