Results 1 to 9 of 9
  1. #1
    Join Date
    May 2009
    Posts
    4

    Unanswered: Explain how joins work

    Ok, I've been seeing some weird stuff in my joins lately. And I'm confused as to exactly how joins work 'behind the scenes' so to speak.

    Say I have a join like this:

    Code:
    Select a.*
    From a
    join b on (a.id = b.id and a.bbb = b.bbb)
    join c on (c.id = b.id and c.ccc = b.ccc)
    Where a.key1 = 'whatever'
    and a.key2 = 'whatever'
    and a.key3 = 'whatever'
    Now, here's my understanding of how this simple join would work.

    At first, a result set is generated by scanning the a table. The result set would be isolated by the where clause.

    So you have a result set in memory (or a table if you will).

    Then you join table b to a. This is where I'm confused.
    It is my understanding that when b is joined to a, the 'a' table is NOT re-queried. Rather the b table is queried and matched/joined only to the result set we have in memory already. In short, the b table is joined to the table in memory, and the first table in the query is never scanned a second time in subsequent queries. Only the existing result set in memory is joined to the following tables.

    Then after b is joined to a, c is joined that that query in the same way. It is only joined to the existing record set in memory?

    Is my understanding on this correct?

    Thanks.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Your best option is to look at the execution plan of your query in question and see for yourself!

    Just because you specify the joins in that order, doesn't mean that is the order they will be "executed".
    George
    Home | Blog

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    And the execution plan will vary based upon the data (the number of records, the cardinality, etc).
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mssqlchaos
    Is my understanding on this correct?
    the part about not re-querying a table is absolutely correct

    conceptually, the sequence you outlined (filter table a rows, then join the b rows to them, then join c rows to what's left after the b rows have been joined) is also correct

    when you specify WHERE conditions on only one table, there is a very strong likelihood that the database engine will retrieve that table first, as in this example

    when you specify WHERE conditions on more one table, you will definitely need to examine the EXPLAIN output to see what it does
    Last edited by r937; 05-29-09 at 14:09.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Psst, Rudy, EXPLAIN is MySQL
    George
    Home | Blog

  6. #6
    Join Date
    May 2009
    Posts
    4
    Quote Originally Posted by gvee
    Your best option is to look at the execution plan of your query in question and see for yourself!

    Just because you specify the joins in that order, doesn't mean that is the order they will be "executed".
    How do I 'look at the execution plan' of a query? And can you do this with any/every database?

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Click CTRL + M in management studio. Execute your query. you now have a graphical execution plan of all the executed code. Look in Books online for detailed info on what it all means.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mssqlchaos
    And can you do this with any/every database?
    yes, but as you have just seen, the method may vary
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Nice save

    *high five*
    George
    Home | Blog

Posting Permissions

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