6

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?

Improve this question
1
  • 3
    As far as the optimizer is concerned, there is no difference. The optimizer will select identical plans for both your queries. Personally, I prefer the inner join syntax. Commented Jun 6, 2011 at 6:59

2 Answers 2

Reset to default
13

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

+1 for preferring the inner join syntax. I find it much more reasonable as it explicitly states what you are trying to do.
1

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.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.