Are inner and outer joins commutative? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Are inner and outer joins commutative?

Author

Topic

Quimbly
Starting Member

4 Posts

Posted - 2006-11-24 : 12:56:54

In other words...

Are these select statements equivalent (logically, performance-wise, etc.)?

#1:

select A.Apple, B.Bob, C.Cat, D.Dog
from A_Table
left join B_Table B on B.ID = A.B_ID
left join C_Table C on C.ID = A.C_ID
left outer join D_Table D on D.ID = A.D_ID
where A.IQ > 100

#2:

select A.Apple, B.Bob, C.Cat, D.Dog
from A_Table
left outer join D_Table D on D.ID = A.D_ID
left join B_Table B on B.ID = A.B_ID
left join C_Table C on C.ID = A.C_ID
where A.IQ > 100

#3:

select A.Apple, B.Bob, C.Cat, D.Dog
from A_Table
left join B_Table B on B.ID = A.B_ID
left outer join D_Table D on D.ID = A.D_ID
left join C_Table C on C.ID = A.C_ID
where A.IQ > 100

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-11-24 : 13:07:50

sql server will probably create the same execution plan for all 4.
but it's not necessary. does it create same plans for all 4? if it does then they'll all be the same.

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-24 : 13:12:15

NO !

If it would have been INNER JOIN, then they all are equivalent
But in case of LEFT/RIGHT join case is different.

In case #1, table D will be LEFT JOINed with the resultset of LEFT JOIN between A,B and C
In case #2, table D will be LEFT JOINed with only table A
In case #3, table D will be LEFT JOINed with the resultset of LEFT JOIN between A and B.

BTW, LEFT JOIN and LEFT OUTER JOIN are same!

Harsh Athalye
India.
"Nothing is Impossible"

Quimbly
Starting Member

4 Posts

Posted - 2006-11-24 : 13:49:08

I've heard that join order can affect performance of a query. Would this not be the case with these examples?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-24 : 13:58:04

INNER JOIN only fetches matching records for both tables.
LEFT JOIN takes all records from forst table and only the matching records from second table.

So, yes, there can be some performance issues regarding type of JOIN.
But the business rules decides how the recordset should look like.

Peter Larsson
Helsingborg, Sweden

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-11-24 : 17:23:45

All your joins are left joins from A, so the order wont in any way affect the performance nor the logic. I would actually go so far and say that the order NEVER makes any difference, but foward references isnt allowed so that at times dictate a certain order.

What can make a difference is where you place the join conditions, either in the FROM (as ON <expression>) or in the WHERE.

-- Alice came to a fork in the road. "Which road do I take?" she asked. "Where do you want to go?" responded the Cheshire cat. "I don't know," Alice answered. "Then," said the cat, "it doesn't matter."

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-11-24 : 22:59:53

http://www.sqlteam.com/item.asp?ItemID=11122

Madhivanan

Failing to plan is Planning to fail

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-25 : 13:52:49

The way you understand commutative here is important, because the examples you gave are actually not examples of whether or not a join is commutative.

Outer joins are not commutative because this
SELECT ...
FROM TableA
LEFT OUTER JOIN TableB ON ...
is not the same as
SELECT ...
FROM TableB
LEFT OUTER JOIN TableA ON ...

However, that's not what you asked. You asked if this
SELECT ...
FROM TableA
LEFT OUTER JOIN TableB ON ...
LEFT OUTER JOIN TableC ON ...
is the same as this
SELECT ...
FROM TableA
LEFT OUTER JOIN TableC ON ...
LEFT OUTER JOIN TableB ON ...

Those are logically the same but they are not an example of the commutative properties of outer joins. Logically there is no difference and the results will be the same either way.

Note that inner joins are commutative and this
SELECT ...
FROM TableA
INNER JOIN TableB ON ...
is the same as
SELECT ...
FROM TableB
INNER JOIN TableA ON ...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources