Which is better and why?

[rarich01]

1)What’s the benefit to retrieving records from two or more tables by using JOIN statements as opposed to just listing the tables, and then providing the search keys in the WHERE clause?

I.e.: SELECT E.Name, A.Address
FROM Employees AS E INNER JOIN Adresses AS A
ON (E.EmployeeID = A.EmployeeID)

VS.

SELECT E.Name, A.Address
FROM Employees AS E, Addresses AS A
WHERE E.EmployeeID = A.EmployeeID

Any ideas on performance of one vs. the other, especially when multiple tables with many records are involved?

 

[JohnDTampaBay]

1 - Using the JOIN statements is ANSI compliant.

2 - I think that JOINs create a hash table of the records that meet the JOIN criteria, then the WHERE clause is applied. The other method creates a hash of all rows then applies the WHERE clause. The hash table is the second method is probably huge.

I'm sure there are other reasons though. Gurus?

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[fluteplr]

Most sql server compilers will create the same plan from either method, but reason 1 above is correct.

I have to say though that when I am coding adhoc queries I hardly ever use the 'join' systax it just takes too long to type .

 

[swampBoogie]

Specifying the join condition in the where clause is also ANSI compliant.

 

[JohnDTampaBay]

My statement was a bit incomplete. From BOL (Specifying Joins in FROM or WHERE Clauses):

[ul][li]The rows selected by a query are filtered first by the FROM clause join conditions, then the WHERE clause search conditions, and then the HAVING clause search conditions. Inner joins can be specified in either the FROM or WHERE clause without affecting the final result.[/li]

[li]Outer join conditions, however, may interact differently with the WHERE clause search conditions, depending on whether the join conditions are in the FROM or WHERE clause. Therefore, the ability to specify Transact-SQL outer joins in the WHERE clause is not recommended, is no longer documented, and will be dropped in a future release.[/li][/ul]

From BOL (Transact-SQL Joins):
[ul][li]In earlier versions of Microsoft® SQL Server™ 2000, left and right outer join conditions were specified in the WHERE clause using the *= and =* operators. In some cases, this syntax results in an ambiguous query that can be interpreted in more than one way. SQL-92 compliant outer joins are specified in the FROM clause and do not result in this ambiguity.[/li][/ul]

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[pascalsql]

Correctly indented, the join syntax is much easier to read and displays the relational logic.

select M.name, F.my_col, O.yet_another_col
from maintable M
join foreigntable F on F.id = M.id -- only relation here
join othertable O on O.ref = F.ref
where
(
M.city = 'Paris' and
F.foo = 'blah' and
O.date ='01/01/2003'
)