2

Possible Duplicate:
Explicit vs implicit SQL joins

I have two queries in SQL Server.

First query:

Select * 
From Stack, Overflow
Where Stack.Id = Overflow.StackId

Second query:

Select * 
From Stack 
Inner Join Overflow On Overflow.StackId = Stack.Id

These two queries return the same results.

So what is the difference between this two queries in terms of performance?

And which one do you prefer?

Community
  • 1
  • 1
Stack User
  • 1,378
  • 5
  • 19
  • 40

2 Answers2

3

Use query #2 - it's the proper ANSI/ISO SQL Standard JOIN syntax, and is preferred over #1.

For one: your JOIN condition is where it belongs - on the JOIN - and doesn't clutter up your WHERE clause. Your WHERE clause should contain only things that you actually use to constrain your result set.

And secondly: since you have to define your JOIN condition on the JOIN, you're less likely to "forget" about it and unwantingly produce a cartesian product.

And lastly: since you define INNER JOIN or LEFT OUTER JOIN, your query becomes more readable and easier to udnerstand - for someone else looking at your query, and for yourself in six months when you have to go back and maintain your code.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
2

The difference is that the first query is implicitly joining two tables, where the second is explicitly joining two tables.

I prefer the second one, just because you're being verbose explicit in stating exactly how two tables are joined together. I suppose you are in the first one as well, but as the query grows it would get a lot harder to read.

Performance will mostly depend on your table indexes.

WWW
  • 9,734
  • 1
  • 29
  • 33