Understanding INNER join in detail

Question

Understanding INNER join in detail

Viewing 15 posts - 121 through 134 (of 134 total)

You must be logged in to reply to this topic. Login to reply

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 1

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[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"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."

sqlservercentral.w.micmurphys Grasshopper Points: 16 More actions · Answer 2

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

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.

c.ludwig-667859 Grasshopper Points: 15 More actions · Answer 4

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.?

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 5

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?

Toby White SSCertifiable Points: 6187 More actions · Answer 6

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

Jeff Moden SSC Guru Points: 1003875 More actions · Answer 7

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003875 More actions · Answer 8

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[/url] 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... :blush:

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003875 More actions · Answer 9

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003875 More actions · Answer 10

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mjaiswal SSC Veteran Points: 251 More actions · Answer 11

The shown result of query 4 & 5 are wrong. Kindly correct it.

psedhai2000 Grasshopper Points: 14 More actions · Answer 12

thankz sreeju i got my answer & it solved again thankzzzzzzzz.....hoping again like this solution......tc

jericsmith SSC Rookie Points: 31 More actions · Answer 13

Wow. If your demographics are gaining knowledge from this article, then I hope they're not out there writing production queries. You shouldn't have your hands in the database if this isn't second nature.

jericsmith SSC Rookie Points: 31 More actions · Answer 14

jericsmith

SSC Rookie

Points: 31

October 27, 2009 at 6:58 am

#1071166

what do you think the correct results are?

jericsmith SSC Rookie Points: 31 More actions · Answer 15

this bb program sucks. That last post was a question for mjaiswal who says query 4 & 5 are wrong. Please post what you believe to be the correct answers.