SQL Joins

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Vivek Johari » SQL Joins

26 posts, Page 2 of 3

««1 2 3 »»

SQL Joins

Rate Topic

Display Mode

Topic Options

Author

Message

GilaMonster

Posted Friday, August 14, 2009 4:14 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:28 PM
Points: 37,732, Visits: 29,996

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

MVDBA

Posted Friday, August 14, 2009 4:16 AM

SSCrazy

Group: General Forum Members
Last Login: Monday, April 29, 2013 10:15 AM
Points: 2,261, Visits: 758

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

ta.bu.shi.da.yu

Posted Friday, August 14, 2009 4:47 AM

SSC Veteran

Group: General Forum Members
Last Login: Sunday, July 03, 2011 7:09 AM
Points: 258, 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

Thordog

Posted Friday, August 14, 2009 8:19 AM

Old Hand

Group: General Forum Members
Last Login: Thursday, May 16, 2013 12:53 PM
Points: 381, Visits: 670

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

Post #770965

rtodd-997233

Posted Friday, August 14, 2009 8:48 AM

SSC 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

ankur_johari

Posted Friday, August 14, 2009 12:38 PM

Forum 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

kinglion1109

Posted Friday, August 14, 2009 12:45 PM

Forum Newbie

Group: General Forum Members
Last Login: Friday, August 14, 2009 12:41 PM
Points: 1, Visits: 0

Very good article.

Post #771228

Thordog

Posted Friday, August 14, 2009 12:49 PM

Old Hand

Group: General Forum Members
Last Login: Thursday, May 16, 2013 12:53 PM
Points: 381, Visits: 670

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

Post #771232

mishaluba

Posted Friday, August 14, 2009 5:12 PM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 5:54 PM
Points: 525, Visits: 617

Nice refresher! Thank you.

Post #771379

tmeyer1969

Posted Friday, August 14, 2009 5:41 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, March 11, 2013 9:56 AM
Points: 7, Visits: 176

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 »

26 posts, Page 2 of 3

««1 2 3 »»

Permissions