Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2014
    Posts
    35

    Unanswered: Understanding join scenarios with multiple tables?

    Hi Everyone,

    I have a general question concerning joins. Below is a table scenario that I would like advice on -

    SELECT *
    FROM TABLE_A T0
    INNER JOIN TABLE_B T1 ON T1.[Some_Column] = T0.[Some Column]
    LEFT JOIN TABLE_C T2 ON T2.[Some_Column] = T0.[Some Column]

    Does the above indicate that all records in common between TABLE_A & TABLE_B will be returned, then the records from TABLE_C will be joined to the initial 'result set' (that is the result of joining TABLE_A & TABLE_B), or will TABLE_C simply be joined to TABLE_A regardless of the inner join between TABLE_A & TABLE_B?

    Any help with explaining the processing that goes on in this scenario, and the result set that one would expect to receive will be greatly appreciated.

    Kind Regards,

    David

  2. #2
    Join Date
    Feb 2004
    Posts
    492
    I'd say your first answer comes closest. Records in common between A & B, records in table C optional (you may have nulls).

  3. #3
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    Records are nothing like rows. This is one of my manias; when people still think in terms of physical storage and file systems, they never really learn to think abstractly.

    The infixed notation is done left to right, so your example with parens is:

    SELECT *
    FROM (Table_A AS T0
    INNER JOIN
    Table_B AS T1
    ON T1.some_column = T0.some_column)
    LEFT OUTER JOIN
    Table_C AS T2
    ON T2.some_column = T0.some_column;

    >> Does the above indicate that all records*[sic] in common*between Table_A and Table_B will be returned, .. <<

    No, that is an INTERSECT, not a JOIN.

    >> then the records [sic] from Table_C will be joined to the initial 'result set' (that is the result of joining Table_A and Table_B), or will Table_C simply be joined to Table_A regardless of the INNER JOIN between Table_A and Table_B? <<

    Since you did not give the first join result a name, T0 is exposed for the second join. However, you could force the first join into a local working table:

    SELECT *
    FROM (Table_A AS T0
    INNER JOIN
    Table_B AS T1
    ON T1.some_column = T0.some_column)
    AS T10
    LEFT OUTER JOIN
    Table_C AS T2
    ON T2.some_column = T10.some_column;

    I have some articles on the scoping rules in my books. See chapter 37 of SQL FOR SMARTIES 5-th edition. It is basically like any block structured language.

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    seems I am constantly handing out this link and for all rdbms. Robert's and the white papers of Terry's that he points to in this blog are the best descriptions I have seen for folks attempting to use left outer joins.
    http://catterallconsulting.blogspot....tes-right.html
    Dave

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •