Recently I encountered some SQL join queries where the ON clause of join had few more conditions with AND. I used to think that this is same as putting the conditions in the WHERE clause. However, that was not the case.
Below are the images of my test tables and results of different queries. The thing to note is that when an AND is used inside the ON clause of a join, the result set is applied only to the table in question for that join. However, a condition that is applied on a WHERE clause is applicable for the whole result.
First, the test data -
Left Join with AND in ON Clause
Left Join with WHERE clause
Left join with 3 or more tables depends on the table selected in the ON condition
Below are the images of my test tables and results of different queries. The thing to note is that when an AND is used inside the ON clause of a join, the result set is applied only to the table in question for that join. However, a condition that is applied on a WHERE clause is applicable for the whole result.
First, the test data -
Normal Left Join -
Left Join with AND in ON Clause
Left join with 3 or more tables depends on the table selected in the ON condition
Normal Inner Join
Inner join with AND in ON clause
Inner join with WHERE clause