Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2006
    Posts
    111

    Unanswered: inner join transitivity?

    where a, b and c are tables

    is

    a inner join b
    on a.* = b.*
    inner join c
    on a.* = c.*

    the same as

    a inner join b
    on a.* = b.*
    inner join c
    on b.* = c.*

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I assume that by "*" you mean the same column or set of columns? Because what you have written would not compile.

    If the former, then yes.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jul 2006
    Posts
    111
    yes, correct assumption. and thanks for the answer, i can finally sleep now =)

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    It would be of great benefit to learn to read execution plans. You would have seen that the plans for these two queries are the same and answered the question yourself.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Logically it is the same, but I've seen it to be viewed by Optimizer as an "unbalanced" join, so that adding "where b.<key> = c.<key>" to the first would balance it and allow for full usage of indexes across all 3 tables.
    Last edited by rdjabarov; 07-15-10 at 11:05. Reason: was "QP", needs "Optimizer"
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

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