Where and Join On Clause the Same?

  • Bradley Deem

    SSCrazy

    Points: 2565

    Comments posted to this topic are about the item Where and Join On Clause the Same?

  • Lynn Pettis

    SSC Guru

    Points: 442339

    Excellent question. This is one of the things you have to watch for when converting a query that uses the old style outer joins to the ANSI standard outer joins. What may seem to be a filter condition in the where clause may aslo need to be moved to the FROM clause as part of the join criteria.

  • dunnjoe

    SSCrazy

    Points: 2180

    Good question. I had to debug an issue just like this last week with one of the developers. We decided we liked the old style syntax better!

  • sjsubscribe

    SSCrazy

    Points: 2080

    An explanation of why the order of evaluation, "from" followed by "where" has this effect is in order here. Merely stating that the order of evaluation as the reason misses an important lesson.

  • Lynn Pettis

    SSC Guru

    Points: 442339

    dunnjoe (5/4/2009)


    Good question. I had to debug an issue just like this last week with one of the developers. We decided we liked the old style syntax better!

    I prefer the ANSI style better. It separates the join criteria from filter criteria.

  • john.botibol

    Valued Member

    Points: 54

    Good question but not well presented. Try it on a database which is case sensitive and you get "none of the above"! Case is always important and as they say at the FBI Academy "To assume makes ..." (courtesy of the Silence of the Lambs)

  • sknox

    SSChampion

    Points: 12292

    Here's how I see the difference. It may help you to understand the order of execution, or it may just confuse you, but it's how I read the statements semantically. Your results may vary. 😛

    SELECT *

    FROM T1

    LEFT JOIN T2

    ON t1.col1 = t2.col1

    WHERE t2.col1 IS NULL

    is telling the server to filter the result set to those rows with a NULL value in the column output from t2.col1, whereas

    SELECT *

    FROM T1

    LEFT JOIN T2

    ON t1.col1 = t2.col1

    AND t2.col1 IS NULL

    is telling the server to condition the join on there being a NULL value in t2.col1 (i.e, to filter the input from t2 into the join to rows with a NULL in t2.col1)

  • prabhu_hl007

    SSC Journeyman

    Points: 96

    Question is superb. Its simple but gives major difference.

    For the first statement, It joins and evaluates the where condition. But for second it evaluates the condition while applying joins .Thats the difference.:-)

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply