SQL join: where clause vs. on clause

Question

After reading it, this is not a duplicate of Explicit vs Implicit SQL Joins. The answer may be related (or even the same) but the question is different.

---

What is the difference and what should go in each?

If I understand the theory correctly, the query optimizer should be able to use both interchangeably.

Answer 1

This article clearly explains the difference. It also explains the "ON joined_condition vs WHERE joined_condition or joined_alias is null".

The WHERE clause filters both the left and the right side of the JOIN, while the ON clause will always filter the right side only.

1. If you always want to fetch the left side rows and only JOIN if some condition matches then you should the ON clause.
2. If you want to filter the product of joining both sides, then you should use the WHERE clause.

Answer 2

The way I do it is:

Always put the join conditions in the on clause If you are doing an inner join, so do not add any where conditions to the on clause, put them in the where clause

If you are doing a left join, add any where conditions to the on clause for the table in the right side of the join. This is a must because adding a where clause that references the right side of the join will convert the join to an inner join (With one exception described below).

The exception is that when you are looking for the records that are not in a particular table, you would add the refernce to a unique identifier(that is not ever null) in the right join table to the where clause this way "Where t2.idfield is null". So the only time you should reference a table on the right side of the join is to find those records which are not in the table.

Answer 3

On an inner join, they mean the same thing. However you will get different results in an outer join depending on if you put the join condition in the WHERE vs the ON clause. Take a look at this related question and this answer (by me).

I think it makes the most sense to be in the habit of always putting the join condition in the ON clause (unless it is an outer join and you actually do want it in the where clause) as it makes it clearer to anyone reading your query what conditions the tables are being joined on, and also it helps prevent the WHERE clause from being dozens of lines long.

Answer 4

this is my solution.

SELECT song_ID,songs.fullname, singers.fullname
FROM music JOIN songs ON songs.ID = music.song_ID  
JOIN singers ON singers.ID = music.singer_ID
GROUP BY songs.fullname

You must have the GROUP BY to get it to work.

Hope this help.

Answer 5

Normally, filtering is processed in the WHERE clause once the two tables have already been joined. It’s possible, though that you might want to filter one or both of the tables before joining them. i.e, the where clause applies to the whole result set whereas the on clause only applies to the join in question.

Answer 6

I think it's the join sequence effect. In the upper left join case, SQL do Left join first and then do where filter. In the downer case, find Orders.ID=12345 first, and then do join.