Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Joins


SQL Joins

Author
Message
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47261 Visits: 44392
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, MVP, M.Sc (Comp Sci)
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


MVDBA
MVDBA
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2421 Visits: 860
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
ta.bu.shi.da.yu
ta.bu.shi.da.yu
SSC Veteran
SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)

Group: General Forum Members
Points: 251 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 Smile

Random Technical Stuff
Thordog
Thordog
Mr or Mrs. 500
Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)

Group: General Forum Members
Points: 572 Visits: 865
I'd like to work for a company where I am my boss's boss. Cool (self join exampl)

____________________________________________________________________________________________
Remember as you walk down lifes road, don't forget to stop and pee on the bushes - Thordog

rtodd-997233
rtodd-997233
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
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
ankur_johari
ankur_johari
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 1
This article contains all the basic knowledge of joins,with the relevant and proper examples and explanations....
kinglion1109
kinglion1109
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 0
Very good article.
Thordog
Thordog
Mr or Mrs. 500
Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)

Group: General Forum Members
Points: 572 Visits: 865
I thought it was full of hot air... Cool

____________________________________________________________________________________________
Remember as you walk down lifes road, don't forget to stop and pee on the bushes - Thordog

Misha_SQL
Misha_SQL
SSChasing Mays
SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)

Group: General Forum Members
Points: 607 Visits: 985
Nice refresher! Thank you.



tmeyer1969@gmail.com
tmeyer1969@gmail.com
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 275
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search