What' the difference between these 2 sql commands?

Question

select * from StudySQL.dbo.id_name n
inner join StudySQL.dbo.id_sex s
on n.id=s.id
and s.sex='f'

select * from StudySQL.dbo.id_name n
inner join StudySQL.dbo.id_sex s
on n.id=s.id
where s.sex='f'

The result are identical. So any difference between them?

Add

I made several more interesting tries.

select * from StudySQL.dbo.id_name n

1 | baby
3 | alice

select * from StudySQL.dbo.id_class c

1 | math      
3 | physics   
3 | english   
4 | chinese 


select * from StudySQL.dbo.id_name n
left join StudySQL.dbo.id_class c
on n.name='alice'

name  id    id  class
baby    1   NULL    NULL
alice   3   1           math      
alice   3   3           physics   
alice   3   3           english   
alice   3   4           chinese   


select * from StudySQL.dbo.id_name n
left join StudySQL.dbo.id_class c
on n.name='baby'

name    id  id  class
baby    1   1           math      
baby    1   3           physics   
baby    1   3           english   
baby    1   4           chinese   
alice   3   NULL    NULL


select * from StudySQL.dbo.id_name n
left join StudySQL.dbo.id_class c
on n.name<>''

name    id  id  class
baby   1    1           math      
baby   1    3           physics   
baby   1    3           english   
baby   1    4           chinese   
alice  3    1           math      
alice  3    3           physics   
alice  3    3           english   
alice  3    4           chinese   

So I thinnk it's reasonable to say, the on clause decides which rows should be joined. While the where clause decides which rows should be returned.

If this is true, I think it's better to write detailed restrictions in the on clause so that fewer rows needs to be joined. Becuase the join is an expensive operation.

Answer 1

Like Marc Romero said, the ON clause is to specify the relation between tables, the WHERE clause is to specify the filter condition. In this particular query, you'll see the same results, but in others if you don't understand the difference you could get unexpected results. Consider the queries (yours with LEFT JOINS instead of INNER) below:

SELECT *
FROM StudySQL.dbo.id_name n
LEFT JOIN StudySQL.dbo.id_sex s ON n.id=s.id AND s.sex='f'

SELECT *
FROM StudySQL.dbo.id_name n
LEFT JOIN StudySQL.dbo.id_sex s on n.id=s.id
WHERE s.sex='f'

The first will return info from StudySQL.dbo.id_name and the information from the StudySQL.dbo.id_sex table associated for any records that have sex ‘f’. The second will return all names, but only names that have sex ‘f’ will have information from StudySQL.dbo.id_sex populated.

Answer 2

There's no difference while this is an INNER JOIN but the results would be very different if you used an OUTER JOIN. Used with an LEFT OUTER JOIN the second query would implicitly become an INNER JOIN.

By using AND the predicate is identifying when a row from s should be joined to n. In an inner join, a negative result here would prevent both the n and s sides to be omitted.

Answer 3

Some interesting discussion of this topic is found here:

INNER JOIN ON vs WHERE clause

They should always return the same results, but may result in slightly different query plans and performance due to when the filtered rows are being removed. This behaviour will depend on exactly what server you are using.

Answer 4

The ON clause is for especify the relation between tables, the WHERE clause is for especify the filter condition. Maybe it's more a conceptual problem that a result/performance problem in this case.