Understanding INNER join in detail

  • jcrawf02

    SSC-Insane

    Points: 24198

    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

    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: 442332

    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

    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

    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: 6127

    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: 996622

    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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

  • Jeff Moden

    SSC Guru

    Points: 996622

    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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

  • Jeff Moden

    SSC Guru

    Points: 996622

    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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

  • Jeff Moden

    SSC Guru

    Points: 996622

    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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

  • mjaiswal

    SSC Veteran

    Points: 251

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

  • psedhai2000

    Grasshopper

    Points: 14

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

  • jericsmith

    SSC Rookie

    Points: 31

    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

    what do you think the correct results are?

  • jericsmith

    SSC Rookie

    Points: 31

    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.

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

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