CODEWITHSUNDEEP
sqldatabaseselectjoin
dido
dido

Reputation: 3407

What is the difference between these two SQL queries

Is the second query a short form of the first? Is the second one doing something like an implicit JOIN?? They both return the same result...So I'd like to know which one is better to use. Using SQL Server 2008. Thanks for your answers.

First Query

SELECT b.columnOne, a.columnTwo
FROM tableA As a JOIN tableB As b ON a.id = b.id

Second Query

SELECT b.columnOne, a.columnTwo 
FROM tableA As a, tableB as b
WHERE a.id = b.id

Upvotes: 2

Views: 148

Answers (4)

Marco
Marco

Reputation: 57573

First query is the correct one.
Execution plan for second query is "translated" as the first one because engine understands that you're joining tables, so in this case they are the same.
Anyway when you use queries involving more than one related tables, you should always use JOIN syntax (because it was born for this) and avoid WHERE: with the latter, if engine is not able to translate to JOIN syntax, you could create huge queries because you multiply the number of records within each table (it's called cartesian product).

Upvotes: 1

Nitin S
Nitin S

Reputation: 7591

Both the queries will return same results,

Take a look at the article on MS SQL Joins - http://www.codeproject.com/Articles/102805/SQL-Joins

First query is the correct one and we can apply more filters on the condition using Joins - Left Outer Join, Right Outer Join, Full Outer Join etc

Upvotes: 0

Yann Sagon
Yann Sagon

Reputation: 577

Specifing the join separately (first form) is cleaner in my opinion. Then you can add your search criteria in the WHERE clause. But yes the two form are equivalent.

Upvotes: 0

Yuck
Yuck

Reputation: 50825

They are identical. The second one is not using ANSI-standard join whereas the first one is.

Many, although not all, developers prefer the first version (ANSI-standard) as it keeps the JOIN logic in one place. It allows filter logic to be kept in the WHERE clause.

Upvotes: 4

Related Questions