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

    Join Date
    Dec 2004
    Posts
    139
    Rep Power
    10

    Theoretically, are these two queries the same, efficiency-wise?


    SELECT foo FROM bar b
    INNER JOIN foot f
    ON b.id = f.id
    WHERE f.age = 22


    SELECT foo FROM bar b
    LEFT JOIN foot f
    ON b.id = f.id
    WHERE f.age = 22


    The only difference is the join type. By putting the condition of the joined table's column in the WHERE clause (i.e f.age = 22), the result set should
    always be the same. But is the LEFT join less efficient or would they be processed the same way?
    Thanks,
    Dan

    Operating system ubuntu 12.04
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,785
    Rep Power
    348
    What does the execution plan say?
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2004
    Posts
    139
    Rep Power
    10
    Unfortunately I don't have access to a server right now. The question came up in conversation. If anybody knows the answer without having to investigate, please consider sharing your wisdom.
    Thanks,
    Dan

    Operating system ubuntu 12.04
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,785
    Rep Power
    348
    Originally Posted by dano2
    Unfortunately I don't have access to a server right now. The question came up in conversation. If anybody knows the answer without having to investigate, please consider sharing your wisdom.
    It completely depends on the DBMS being used. I guess there are some where the optimizer is smart enough and other where it isn't

    (Postgres uses exactly the same query plan for both versions when joining a table with ~300k rows to a table with ~4 million rows)
    Last edited by shammat; November 2nd, 2013 at 04:54 AM.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

IMN logo majestic logo threadwatch logo seochat tools logo