#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2008
    Posts
    25
    Rep Power
    0

    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
  3. Headless Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,972
    Rep Power
    9647
    Originally Posted by MrDavo
    Does the above indicate that all records in common between TABLE_A & TABLE_B will be returned,
    Yes (so far): because it's an INNER JOIN, if the record from TABLE_A doesn't match up with anything in TABLE_B then the entire row is skipped.

    Originally Posted by MrDavo
    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?
    JOINs aren't so much about combining two tables together but about taking a resultset (which initially consists of the data from one table) and merging in data from another table.
    The resultset starts with rows from TABLE_A. Next the system decides to merge in matching data from TABLE_B. It evaluates the condition not on TABLE_A + TABLE_B but on the current resultset + TABLE_B. Matching rows from the resultset are kept and rows from TABLE_B are merged in, resultset rows without matches are discarded (because it's an INNER JOIN). Then TABLE_C is merged in the same way, except this time if there's a resultset row that doesn't have any matches, it is kept and a row of all NULLs that's "from" TABLE_C is merged in.

    The system could decide that it wants to merge TABLE_C before TABLE_B but that's fine because the end result will be the same.

IMN logo majestic logo threadwatch logo seochat tools logo