JOIN Approaches | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

JOIN Approaches


I see some SQL Programmers use this type of joins Select…..
from table1 T1 join
table2 T2 join
table3 T3 join
table4 T4
on T4.col=T3.col
on T3.col=T2.col
on T2.col=T1.col
where ……..
But I always use Select ….from
table1 T1 join table2 T2 on T2.col=T1.col
table2 T2 join table3 T3 on T3.col=T2.col
table3 T3 join table4 T4 on T4.col=T3.col
where …….
Is there any significant performance differences?
Which one is most preferrable? Madhivanan Failing to plan is Planning to fail
fromhttp://www.sql-server-performance.com/tuning_joins.asp When you create joins using Transact-SQL, you can choose between two different types of syntax: either ANSI or Microsoft. ANSI refers to the ANSI standard for writing joins, and Microsoft refers to the old Microsoft style of writing joins. For example: ANSI JOIN Syntax SELECT fname, lname, department
FROM names INNER JOIN departments ON names.employeeid = departments.employeeid Former Microsoft JOIN Syntax SELECT fname, lname, department
FROM names, departments
WHERE names.employeeid = departments.employeeid If written correctly, either format will produce identical results. But that is a big if. The older Microsoft join syntax lends itself to mistakes because the syntax is a little less obvious. On the other hand, the ANSI syntax is very explicit and there is no chance you can make a mistake. For example, I ran across a slow-performing query from an ERP program. After reviewing the code, which used the Microsoft JOIN syntax, I noticed that instead of creating a LEFT JOIN, the developer had accidentally created a CROSS JOIN instead. In this particular example, less than 10,000 rows should have resulted from the LEFT JOIN, but because a CROSS JOIN was used, over 11 million rows were returned instead. Then the developer used a SELECT DISTINCT to get rid of all the unnecessary rows created by the CROSS JOIN. As you can guess, this made for a very lengthy query. Unfortunately, all I could do was notify the vendor’s support department about it. The moral of this story is that you probably should be using the ANSI syntax, not the old Microsoft syntax. Besides reducing the odds of making silly mistakes, this code is more portable between database, and eventually, I imagine Microsoft will eventually stop supporting the old format, making the ANSI syntax the only option
—————————————-
Cast your vote
http://www.geocities.com/dineshasanka/sqlserver05.html http://spaces.msn.com/members/dineshasanka

No, both are equivalent. SQL Server semantically optimises this and produces identical execution plans. I guess, it boils down to what you think is more readable. To me, this is your second approach. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)

Dinesh, both of them are ANSI Joins] Frank,
Yes the second is more readable. If I use first method, then join columns should be specified from bottom-to-top order of the tables Madhivanan Failing to plan is Planning to fail
Try it out
SELECT c.*, c.*, od.*
FROM Customers c
JOIN Orders o
JOIN [Order Details] od
ON :confused:rderID = od.OrderID
ON o.CustomerID = c.CustomerID SELECT c.*, c.*, od.*
FROM Customers c
JOIN Orders o
JOIN [Order Details] od
ON o.CustomerID = c.CustomerID
ON :confused:rderID = od.OrderID

Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)

Yes Thats what I meant [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
And sometimes you need to do a 3-way join, where you need to name all participating tables before the ON clause. SELECT a.*, b.*, c.*
FROM a
INNER JOIN b
INNER JOIN c
ON a.col1 = b.col1
AND a.col2 = c.col2
AND b.col3 = c.col3 But of course that is equivalent to: SELECT a.*, b.*, c.*
FROM a
INNER JOIN b ON a.col1 = b.col1
INNER JOIN c ON a.col2 = c.col2 AND b.col3 = c.col3 … and even to this: SELECT a.*, b.*, c.*
FROM a
INNER JOIN b ON a.col1 = b.col1
INNER JOIN c ON a.col2 = c.col2
WHERE b.col3 = c.col3 It will depend on your exact query which expression is the most appropriate – I don’t think there would be much of a performance boost/loss for any variation.
Adriaan, I think your first method wont work so as the second query of Frank [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Madhivanan</i><br /><br />Adriaan, I think your first method wont work so as the second query of Frank [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">[:0][xx(][B)] You’re absolutely right, Madhivanan!<br /><br />
Only one to go, Adriaan. [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><br />–<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
Done! This is already post #1001 …
Check it out:<br /<a target="_blank" href=http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=10587>http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=10587</a> [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />].<br /><br /><hr noshade size="1"><b>Satya SKJ</b><br />Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com/forum>http://www.SQL-Server-Performance.Com/forum</a><br /><center><font color="teal"><font size="1">This posting is provided “AS IS” with no rights for the sake of <i>knowledge sharing.</i></font id="size1"></font id="teal"></center>
Guys — Correct me if I am wrong but one small difference is that ANSI provides FULL OUTER join whereas there is no such syntax in the old style. IT has to be combination of individual sql’s. This has been explained in "inside sql server 7.0" book.
Correct, in the ANSI SQL 92 Compliant Syntax where you JOIN in the ON clause there is support for FULL OUTER JOIN, while in the older (SQL 89, I think) JOIN syntax where you JOIN in the WHERE clause there is no such thing as a FULL OUTER JOIN. INNER JOIN, however, are absolutely in compliance with SQL 92 when specified either in the ON or the WHERE clause.

Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)

Use UNION if you can’t use FULL JOIN: SELECT a.key, b.key
FROM a INNER JOIN b ON a.key = b.key UNION
SELECT a.key, b.key
FROM a LEFT JOIN b ON a.key = b.key
WHERE b.key IS NULL UNION
SELECT a.key, b.key
FROM a RIGHT JOIN b ON a.key = b.key
WHERE a.key IS NULL

That must have been the first time I’ve ever used RIGHT JOIN intentionally![<img src=’/community/emoticons/emotion-2.gif’ alt=’:D‘ />]<br /><br />Of course I could have avoided that by using<br /><br />….<br />UNION<br />SELECT a.key, b.key<br />FROM b LEFT JOIN a ON b.key = a.key<br />WHERE a.key IS NULL<br /><br />
]]>