To JOIN or not to JOIN?

[TonyScarpelli]

Below are two SELECT statements.

Can anyone tell me why a JOIN in the second statement is better/faster or whatever than the first statement?
In other words, why use a JOIN?

SELECT Pur_head.PurDate, Pur_head.PONum, Pur_head.company as Vendor, Pur_head.ChargeBack, Pur_item.Descrip, Pur_item.OrderQty as Qty, Pur_item.Cost, (Pur_item.OrderQty * Pur_item.Cost) as Subtotal
FROM Pur_head, Pur_item
WHERE Pur_item.ReqID = Pur_head.ReqID
AND Pur_head.ChargeBack = 1
AND Pur_head.PurDate BETWEEN @StartDate AND @EndDate
ORDER BY Pur_head.PurDate, Pur_head.PONum

SELECT H.PurDate, H.PONum, H.company as Vendor, H.ChargeBack, I.Descrip, I.OrderQty as Qty, I.Cost, (I.OrderQty * I.Cost) as Subtotal
FROM Pur_head H INNER JOIN Pur_item I
ON I.ReqID = H.ReqID
AND H.ChargeBack = 1
AND H.PurDate BETWEEN @StartDate AND @EndDate
ORDER BY H.PurDate, H.PONum

Thanks.


Tony Scarpelli
Clinical Engineering Dept.
Maine Medical Center
Portland, Maine 04102

 

[bborissov]

Because it is more readable.
Also SQL Optimizer will transform your first query to second one anyway. Also with JOIN syntax you have more flexibility.
What if you want ALL records from Pur_head and only matching ones from Pur_item?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[SQLSister]

First, the old syntax is harder to understand and maintain particularly once you start having multiple joins.

Second, this syntax cannot be used in SQL Server 2000 and above for left and right joins and get correct results. (The scary part is that the syntax is allowable, it just gives wrong results sometimes as it sometimes interprets the syntax as a cross join).

Mixing the where clause items and the join conditions makes it harder to really understand a complex query.

Finally (and this is purely based on the people I persionally have encountered, your mileage may vary), people who use the comma syntax seem to have less understanding of what the table relationships really are and what they need to do to get information correctly out of a database.

Thoroughly understanding joins is fundamental to being able to pull the correct results out of your queries. There is no substitute to putting in the time to really learn this stuff. I have never known anyone who took the time to learn how to use the inner join, left join, etc syntax who ever went back to the other way after they learned it.

"NOTHING is more important in a database than integrity." ESquared

 

[TonyScarpelli]

So this is why my IS SQL people want me to write my code like that. I'm too much from the old school, I guess. <g>

Thanks all. Very good explanations.

CU


Tony Scarpelli
Clinical Engineering Dept.
Maine Medical Center
Portland, Maine 04102