Is there a logical difference between putting a condition in the ON clause of an inner join versus the where clause of the main query?

Question

Consider these two similar SQLs

(condition in ON clause)

select t1.field1, t2.field1
from
table1 t1 inner join table2 t2 on t1.id = t2.id and t1.boolfield = 1

(condition in WHERE clause)

select t1.field1, t2.field1
from
table1 t1 inner join table2 t2 on t1.id = t2.id
where t1.boolfield = 1

I have tested this out a bit and I can see the difference between putting a condition in the two different places for an outer join. But in the case of an inner join can the result sets ever be different?

Answer 1

For INNER JOIN, there is no effective difference, although I think the second option is cleaner.

For LEFT JOIN, there is a huge difference. The ON clause specifies which records will be selected from the tables for comparison and the WHERE clause filters the results.

Example 1: returns all the rows from tbl 1 and matches them up with appropriate rows from tbl2 that have boolfield=1

Select *
From tbl1
  LEFT JOIN tbl2 on tbl1.id=tbl2.id and tbl2.boolfield=1

Example 2: will only include rows from tbl1 that have a matching row in tbl2 with boolfield=1. It joins the tables, and then filters out the rows that don't meet the condition.

Select *
From tbl1
  LEFT JOIN tbl2 on tbl1.id=tbl2.id
WHERE tbl2.boolfield=1

Answer 2

In your specific case, the t1.boolfield specifies an additional selection condition, not a condition for matching records between the two tables, so the second example is more correct.

If you're speaking about the cases when a condition for matching records is put in the ON clause vs. in the WHERE clause, see this question.