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

Groups > comp.databases.ms-sqlserver > #728

Re: How does it do it

Show all headers | View raw

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

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Find similar

Thread

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

csiph-web