Where and Join On Clause the Same?

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

  • 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.

  • 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!

  • 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.

  • 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.

  • 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)

  • 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)

  • 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 7 (of 7 total)

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