Page 1 of 3 123 LastLast
Results 1 to 15 of 32
  1. #1
    Join Date
    Jul 2006
    Posts
    111

    Unanswered: Inner Join On Vs Where

    Hi All,

    When is one more appropriate?

    SELECT
    *
    FROM
    TABLEA
    LEFT OUTER JOIN TABLEB
    ON TABLEA.Column1 = TABLEB.Column1
    AND TABLEB.Column2 = 3

    VERSUS

    SELECT
    *
    FROM
    TABLEA
    LEFT OUTER JOIN TABLEB
    ON TABLEA.Column1 = TABLEB.Column1
    WHERE
    TABLEB.Column2 = 3

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    they are not the same

    the second query in effect is an inner join

    suppose in the second query that there is a row from A that has no match in B

    what happens? all the columns in the result row that would've come from B are set to NULL

    then the WHERE clause comes along and says wait a sec, this other column from B has to be equal to this value here

    and since NULL is not equal to anything, all the unmatched rows from A are discarded

    in effect, an inner join
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2006
    Posts
    111
    thanks that was very helpful...

    a somewhat related question

    is the following query

    SELECT
    *
    FROM
    [SOMEARBITRARYTABLEJOINSBLOCK]
    INNER JOIN TABLEB
    ON [ATABLEFROMTHEARBITRARYJOINABOVE].ColumnX = TABLEB.ColumnY

    the same as

    SELECT
    *
    FROM
    [SOMEARBITRARYTABLEJOINSBLOCK]
    LEFT OUTER JOIN TABLEB
    ON [ATABLEFROMTHEARBITRARYJOINABOVE].ColumnX = TABLEB.ColumnY


    if TABLEB.ColumnY is a primary key?




    thanks again...

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    No, the first one will return only rows that match ColumnX to ColumnY. The second one will return all records from SOMEARBITRARYTABLEJOINSBLOCK, and return NULL for the fields from ATABLEFROMTHEARBITRARYJOINABOVE that do not match based on equality of ColumnX to ColumnY.

    As to r937 explanation, it's almost correct, because it is doing the same thing as INNER JOIN. The difference is that the second query in your original post may be more expensive than the first one, because filtering (WHERE clause) comes after the JOIN, as opposed to the INNER JOIN, where its result is already filtered out by the join itself.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by rdjabarov
    As to r937 explanation, it's almost correct, because it is doing the same thing as INNER JOIN. The difference is that the second query in your original post may be more expensive than the first one, because filtering (WHERE clause) comes after the JOIN, as opposed to the INNER JOIN, where its result is already filtered out by the join itself.
    but the first query in the first post isn't an INNER JOIN, it's a LEFT OUTER JOIN

    more expensive is irrelevant if they are fundamentally different queries

    so i was actually slightly more than "almost correct"

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    I didn't say they were the same, I was comparing INNER JOIN with LEFT OUTER JOIN plus WHERE, which would return the same result, thus - your analogy of the latter with INNER JOIN is correct. The only thing I was addint to it was the fact that LEFT OUTER JOIN plus WHERE might be more expensive, that's all!
    (edited)Besides, those two queries are "fundamentally different" ONLY in their mathematical representation, while in terms of the resultset they are producing they are IDENTICAL.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by rdjabarov
    (edited)Besides, those two queries are "fundamentally different" ONLY in their mathematical representation, while in terms of the resultset they are producing they are IDENTICAL.
    what??!!

    are you talking about the queries in post #1?

    because they are not identical

    mathematical or otherwise

    they produce different results
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    No-no-no-no, calm down. I am saying that LEFT OUTER JOIN plus WHERE is "fundamentally different" from INNER JOIN query ONLY in mathematical representation, while in the resultset they both produce they are IDENTICAL...Do I catch myself repeating what I just said? My kids are right, I'm getting old...
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    ah, i see, you are talking about your two queries

    i was talking about g11DB's queries

    and i still say i was correct in my first reply

    thank you for your understanding

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    No Sir, this is what you said in your first reply, so I was going strictly by what you said:
    Quote Originally Posted by r937
    ...the second query in effect is an inner join...
    And as I said, you were almost correct. In fact, since you said "in effect", - you're absolutely correct, because the effect of both queries is the same, because the resultset is the same. The difference might be in the cost. So there, I think we're clear on that, heh?!
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Heh - not wanting to throw petrol on the fire (but kind of wanting to too ) the QP will transform that second query (in the first post) into an inner join. An outer join is less flexible when determining the optimum plan so if the QP spots that an outer join can be converted to an inner join it will do so. As such, it will literally become an inner join at the physical level, as well as being in effect an inner join at the logical level.

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by rdjabarov
    No Sir, this is what you said in your first reply, so I was going strictly by what you said:

    And as I said, you were almost correct. In fact, since you said "in effect", - you're absolutely correct, because the effect of both queries is the same, because the resultset is the same. The difference might be in the cost. So there, I think we're clear on that, heh?!
    please, mister rdjabarov, please go back and look at the two queries in post #1

    they do NOT produce the same results!!!

    the second query cannot return unmatched A rows, whereas the first one can

    so we are finally clear on that, eh?

    YOU made the error here, dude, not me

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by rdjabarov
    No Sir,...
    Quote Originally Posted by r937
    please, mister rdjabarov,...
    I'm glad were all keeping this civil...

    Quote Originally Posted by pootle flump
    Heh - not wanting to throw petrol on the fire (but kind of wanting to too
    Well, almost all of us.
    If it's not practically useful, then it's practically useless.

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

  14. #14
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Quote Originally Posted by r937
    please, mister rdjabarov, please go back and look at the two queries in post #1

    they do NOT produce the same results!!! ...
    For Pete's sake, R!!! I never said the the 2 queries from the first post are the same, or that they produced the same result!!! All I said was to support YOUR analogy of the SECOND query with a query that uses INNER JOIN, just like you said in your first reply.

    And since pootle is playing around with matches, QP does not "rewrite or reorganize or reinterpret" the query, it just executes it based on the plan that Optimizer generated. It's the Optimizer that may (or may not!!!) "re-write" the LEFT OUTER JOIN plus WHERE into an INNER JOIN. And you're right, it may happen, but is not guaranteed.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Pootle, you are grounded.
    If it's not practically useful, then it's practically useless.

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

Posting Permissions

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