Difference between INNER JOIN and WHERE?

问题:

First Query:

Select * from table1 inner join table2 on table1.Id = table2.Id

Second Query:

Select * from table1, table2 where table1.Id = table2.Id

What is difference between these query regarding performance which should one use?

回答1:

The two statements you posted are logically identical. There isn't really a practical reason to prefer one over the other, it's largely a matter of personal style and readability. Some people prefer the INNER JOIN syntax and some prefer just to use WHERE.

Refering to Using Inner Joins:

In the ISO standard, inner joins can be specified in either the FROM or WHERE clause. This is the only type of join that ISO supports in the WHERE clause. Inner joins specified in the WHERE clause are known as old-style inner joins.

Refering to Join Fundamentals:

Specifying the join conditions in the FROM clause helps separate them from any other search conditions that may be specified in a WHERE clause, and is the recommended method for specifying joins.

Personaly, I prefer using INNER JOIN. I find it much clearer, as I can separate the join conditions from the filter conditions and using a seperate join block for each joined table.

回答2:

To amplify @Akram's answer - many people prefer the inner join syntax, since it then allows you to more easily distinguish between the join conditions (how the various tables in the FROM clause relate to each other) from the filter conditions (those conditions that should be used to reduce the overall result set. There's no difference between them in this circumstance, but on larger queries, with more tables, it may improve readability to use the inner join form.

In addition, once you start considering outer joins, you pretty well need to use the infix join syntax (left outer join,right outer join), so many find a form of symmetry in using the same style for inner join. There is an older deprecated syntax for performing outer joins in the WHERE clause (using *=), but support for such joins is dying out.