When joining, is there a difference when you add the conditions in the join clause or the where clause

Question

If I have a join like:

select u1.*
from users u
  inner join users u2 on u.id = u2.id
where u2.location = 'blah' and u2.level > 2

versus

select u1.*
from users u
  inner join users u2 on u.id = u2.id and u2.location='blah' and u2.level > 2

Can someone explain to me the differences and nuances of each query (assuming there is some kind of a differenc)

In most cases no. Maybe some execution plan/performance nuances — Lukasz Szozda, Jul 19 '18 at 15:29
Like the others have said, more often not, no. Personally, I prefer to put literals in the `WHERE`, rather than in the `ON`. — Thom A, Jul 19 '18 at 15:36

score 1 · Answer 1 · answered Jul 19 '18 at 15:39

There shouldn't be a noticeable difference between the two queries. Nevertheless, I would recommend using the query with the where clause. Having that on a separate line makes your query easier to read, and if you needed to rewrite it, it'll be easier for you or others to find the WHERE clause and fix it.

Here is some additional reading material for you: INNER JOIN ON vs WHERE clause

When joining, is there a difference when you add the conditions in the join clause or the where clause

1 Answers1