December 22, 2008 at 8:53 pm
Hi,
Can anyone explain me the different table joins in SQL with an example?
Thank you.
"I Love Walking In The Rain So No One Can See Me Crying ! " ~ Charlie Chaplin
December 22, 2008 at 10:10 pm
Self Join,
Inner Join,
Left Outer Join,
Right Outer Join,
Cross Join,
Full Outer Join.
You should find lot of articles either on this site or Books online which explains with useful examples.
Here is a sample:
http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1340999,00.html
December 22, 2008 at 10:31 pm
Just play with it a bit and you'll understand. Bellow is a small script that creates 2 tables and inserts 2 records for each table. All you have to do is write select statements with different types of join and see the difference.
create table LeftTbl (LeftCol int not null primary key)
go
create table RightTbl (RightCol int not null primary key)
go
insert into LeftTbl (LeftCol)
select 1 union select 2
go
insert into RightTbl (RightCol)
select 1 union select 3
go
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 22, 2008 at 10:44 pm
jchandramouli (12/22/2008)
Hi,Can anyone explain me the different table joins in SQL with an example?
Thank you.
I believe that Books OnLine has this.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 23, 2008 at 6:08 am
Draw two intersecting circles. Circle 1 is your first table and Circle 2 is the second.
An inner join is the area within the intersection of the circles.
A left join is just the first circle including the intersection.
A right join is just the second circle including the intersection.
Go forth and join!
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 23, 2008 at 8:08 am
And a FULL OUTER JOIN would be both cricles? Hmmm,
I am curious to see how you are going to fit a Cross-Join into that example Grant. 😀
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 23, 2008 at 8:13 am
Don't mess with the magic... Pay no attention to that man behind the curtain...
It's just an illustration I've used for years to show beginners how joins work. If you really don't understand what's happening, it gets the message across.
However, I have no idea how I would do a cross-join. It might require three dimensions, or four...
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 23, 2008 at 8:50 am
Heh.
Cross-Joins are set-multiplications. The only metaphors I have ever seen for them are grids and rectangular areas.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply