4

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?

stu
  • 8,461
  • 18
  • 74
  • 112

3 Answers3

6

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
Bill
  • 4,425
  • 3
  • 21
  • 22
  • +1 For your 2nd sentence. Although, in mysql this is not the case! mysql figures out what you are trying to do and lets left joins through anyway – Bohemian Jul 19 '12 at 19:04
  • Can you explain that a little more? – stu Jul 19 '12 at 19:06
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.

Community
  • 1
  • 1
Alessandro Menti
  • 1,290
  • 20
  • 28
1

Both versions return the same data.

Although this is true for an inner join, it is not true for outer joins.

Stylistically, there is a third possibility. In addition to your two, there is also:

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

Which is preferable all depends on what you want to highlight, so you (or someone else) can later understand and modify the query. I often prefer the third version, because it emphasizes that the query is only using certain rows from the table -- the boolean condition is very close to where the table is specified.

In the other two cases, if you have a long query, it can be problematic to figure out what "t1" really means. I think this is why some people prefer to put the condition in the ON clause. Others prefer the WHERE clause.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I'm no database internals expert, but I can't imagine making a derived table is likely to be the best way to go. It doesn't buy you anything except making the sql and the parsing and the processing more complex, no? – stu Jul 19 '12 at 19:05
  • But that's what I wanted to know, if there's no difference in an inner join but there is in an outer join, thanks. – stu Jul 19 '12 at 19:05
  • @stu . . . A subquery does not create a derived table, at least on the major databases. Query optimizers are smart enough to detect what is happening, and run the query in one pass. – Gordon Linoff Jul 19 '12 at 19:58