Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

SQL Joins Expand / Collapse
Author
Message
Posted Friday, August 14, 2009 4:14 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:13 AM
Points: 40,609, Visits: 37,070
ta.bu.shi.da.yu (8/14/2009)
But I think that calling it a left outer join is a bit of a misnomer. Could be a right outer join to, right?


If it was specified as FROM Table1 t1 RIGHT OUTER JOIN Table1 t2 ON .... yes. Self-join is not a technical term. All it indicates is that a table is joined to itself. The term 'self join' doesn't define what the join type is.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #770784
Posted Friday, August 14, 2009 4:16 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 8, 2014 3:25 AM
Points: 2,283, Visits: 781
nope

the use of left or right determines which side of the join returns all results

my example is clearly a left outer join

if this were right outer then the results would be different

i would be asking for "show me all employeesand the name of the person they manage - include all people that do no manage anyone"

.eg

select e1.*,e2.name from employee e1 left outer join employee e2 on e1.managerid=e2.employeeid

assume "maggie os" has no manager and "paul smith" manages no-one

returns (example data)

Name id active manager
john smith 1 1 fred jones
abe lincoln 2 1 fred jones
maggie os 3 1 NULL
paul smith 4 1 fred jones
.......


select e1.*,e2.name from employee e1 RIGHT outer join employee e2 on e1.managerid=e2.employeeid


returns (example data)

Name id active manager
john smith 1 1 fred jones
abe lincoln 2 1 fred jones
NULL NULL NULL Paul Smith
Paul Smith 4 1 fred jones












MVDBA
Post #770787
Posted Friday, August 14, 2009 4:47 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, July 3, 2011 7:09 AM
Points: 233, Visits: 494
Very interesting - I've never thought of it that way before. But absolutely, that makes total sense.

You'll have to forgive the newbie questions, but I am the eternal learner :)


Random Technical Stuff
Post #770795
Posted Friday, August 14, 2009 8:19 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 2:51 PM
Points: 473, Visits: 796
I'd like to work for a company where I am my boss's boss. (self join exampl)

____________________________________________________________________________________________
Remember as you walk down lifes road, don't forget to stop and pee on the bushes - Thordog
Post #770965
Posted Friday, August 14, 2009 8:48 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, August 17, 2009 9:40 AM
Points: 25, Visits: 10
I agree with you. A self join is not a 'type' of join because it only means that your two tables in your join are the same. It is no different from any other join. What if you join a table to a VIEW of itself? Is THAT a self join? Not even close, because the view might a have a where clause that eliminates some records....

The biggest indicator that SELF join is not a type of join, is that you cannot use the word 'SELF' in the syntax of your SQL....all the other types of joins are valid SQL statements
Post #771000
Posted Friday, August 14, 2009 12:38 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, August 15, 2009 1:29 PM
Points: 1, Visits: 1
This article contains all the basic knowledge of joins,with the relevant and proper examples and explanations....
Post #771224
Posted Friday, August 14, 2009 12:45 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 14, 2009 12:41 PM
Points: 1, Visits: 0
Very good article.
Post #771228
Posted Friday, August 14, 2009 12:49 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 2:51 PM
Points: 473, Visits: 796
I thought it was full of hot air...

____________________________________________________________________________________________
Remember as you walk down lifes road, don't forget to stop and pee on the bushes - Thordog
Post #771232
Posted Friday, August 14, 2009 5:12 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 4:03 PM
Points: 542, Visits: 802
Nice refresher! Thank you.


Post #771379
Posted Friday, August 14, 2009 5:41 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 11, 2014 11:41 AM
Points: 7, Visits: 234
nice article, but I'm puzzled by the statement that you "feel that people are afraid of Joins in SQL Server".

I mean, joins (inner/equi, at least) are one of the most basic things that someone has to know about T-SQL, learned in the same breath as 'select','update','delete','where', etc ...

Now if you said that outer, self and cross-apply joins are what people are afraid (or more likely unaware) of, I'd buy it. Otherwise, I'd say that the person didn't know jack about SQL if they don't know what a simple innner join is.
Post #771381
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse