Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #726 > unrolled thread

How does it do it

Back to article view | Back to comp.databases.ms-sqlserver

Contents

  How does it do it "Phil Hunt" <aaa@aaa.com> - 2011-10-03 13:30 -0400
    Re: How does it do it Erland Sommarskog <esquel@sommarskog.se> - 2011-10-03 23:34 +0200

#726 — How does it do it

In the studio query design, I can join any tables up. Now I understand Inner 
Join is associative. But if there is an Outer join in the mix, how does the 
studio determine the order of the joint ? 

[toc] | [next] | [standalone]

#728

Phil Hunt (aaa@aaa.com) writes:
> In the studio query design, I can join any tables up. Now I understand
> Inner Join is associative. But if there is an Outer join in the mix, how
> does the studio determine the order of the joint ? 
 
I don't know. And the reason I don't know is because I never use the
Query Designer. And I can't really recommend usage of it. There are
just too many SQL constructs it does not support.

On the other hand, I do know the difference between LEFT and RIGHT JOIN.
Well, tney are not very different. These two queries are equal:

SELECT a.col1, b.col2
FROM   a
LEFT   JOIN b ON a.col0 = b.col0

SELECT a.col1, b.col2
FROM   b
RIGHT  JOIN a ON a.col0 = b.col0

Both queries will return all rows in table a. For the rows where there is no 
matching row in b, the value of b.col2 will be NULL.

So when do you use LEFT JOIN and when do you use RIGHT JOIN? The answer 
is that you always use LEFT JOIN and never RIGHT JOIN. At least that is 
what I do. RIGHT JOIN gives me headache, because everything is 
backwards. 

The simple rule is: in a LFFT JOIN all rows in the table on the left side 
are retained, while the table on the right side is filtered by the ON 
clause.



-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.ms-sqlserver

csiph-web