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 «««1011121314»»

Understanding INNER join in detail Expand / Collapse
Author
Message
Posted Friday, October 23, 2009 10:41 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 8:08 PM
Points: 1,328, Visits: 19,288
c.ludwig-667859 (10/23/2009)
From the looks of it an innter join is the same a just a plain join. Why use the word "inner"?
No material difference, but part of the standard.


---------------------------------------------------------
How best to post your question
How to post performance problems
Tally Table:What it is and how it replaces a loop

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
Post #808051
Posted Friday, October 23, 2009 10:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 27, 2009 8:45 AM
Points: 2, Visits: 9
join is shorthand for inner join, there are other kinds of joins though: LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, so if you wanted to be explicit, you'd say INNER JOIN
Post #808052
Posted Friday, October 23, 2009 11:07 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 12:03 AM
Points: 20,703, Visits: 32,344
sqlservercentral.w.micmurphys (10/23/2009)
join is shorthand for inner join, there are other kinds of joins though: LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, so if you wanted to be explicit, you'd say INNER JOIN


And if you want to be really explicit LEFT OUTER JOIN and RIGHT OUTER JOIN.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #808067
Posted Friday, October 23, 2009 11:10 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 26, 2012 6:24 AM
Points: 3, Visits: 10
I think that is where I need help... understanding the differences between all the joins and how to use them. Are there any articles on outer joins etc.?
Post #808070
Posted Friday, October 23, 2009 1:15 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 21, 2014 12:08 PM
Points: 1,414, Visits: 4,540
having seen my share of queries with too many joins, can't you just use foreign keys to cut down on the number of joins?

https://plus.google.com/100125998302068852885/posts?hl=en
http://twitter.com/alent1234
x-box live gamertag: i am null
[url=http://live.xbox.com/en-US/MyXbox/Profile[/url]
Post #808131
Posted Friday, October 23, 2009 2:56 PM
SSC-Addicted

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

Group: General Forum Members
Last Login: Friday, November 9, 2012 2:48 PM
Points: 493, Visits: 636
I think it's funny that such a basic posting got so much discussion activity. It's seems like a reasonable enough posting for beginners to me. Anyway, I just have one thing to add. Why not use:

INSERT #InnerJoinTest1
DEFAULT VALUES
go 5

Instead of:

WHILE SCOPE_IDENTITY() <5 OR SCOPE_IDENTITY() IS NULL
Post #808171
Posted Friday, October 23, 2009 5:51 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:40 AM
Points: 35,265, Visits: 31,754
Former Member (7/8/2008)
Gischump is absolutely correct. I apologize for the rude remarks and concur with the earlier post that this should never have been published w/o first being edited. I will cancel my SQL Server Membership immediately.


I know it's an old post but, jeez, way to hang in there.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #808211
Posted Friday, October 23, 2009 6:45 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:40 AM
Points: 35,265, Visits: 31,754
RobinC-661862 (10/23/2009)
While this was a good beginner article and I liked the visual references, there is a serious danger in showing this to beginners - Joins with > and < in them are a well known way to bring a SQL Server to its knees.

(The Mighty) Jeff Moden wrote this Article about the RBAR server death that is a triangular join

As for the Grammar Nazi's - I didn't find the typos too distracting.


Thanks for the reference and the kudo, Robin...


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #808223
Posted Friday, October 23, 2009 7:12 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:40 AM
Points: 35,265, Visits: 31,754
I realize this is a republished article from some time ago but I agree with many of the others ... if you understand that the author probably speaks English as a second language, this is a good article for beginners to understand how the relations within Inner Joins work. It doesn't tell people where to use such things nor was it designed to do that... it's a root primer. It was simply a very straight forward, in-your-face, here's-how-they-work article beginner's article.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #808226
Posted Friday, October 23, 2009 7:14 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:40 AM
Points: 35,265, Visits: 31,754
Toby White (10/23/2009)
Anyway, I just have one thing to add. Why not use:

INSERT #InnerJoinTest1
DEFAULT VALUES
go 5

Instead of:

WHILE SCOPE_IDENTITY() <5 OR SCOPE_IDENTITY() IS NULL


Ummmm... because that's RBAR as well?


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #808227
« Prev Topic | Next Topic »

Add to briefcase «««1011121314»»

Permissions Expand / Collapse