Where and Join On Clause the Same?

Question

Where and Join On Clause the Same?

Bradley Deem

SSCrazy

Points: 2565
More actions
May 2, 2009 at 1:40 pm

#204261

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

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

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

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 More actions · Answer 2

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 More actions · Answer 3

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: 442467 More actions · Answer 4

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 More actions · Answer 5

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: 12388 More actions · Answer 6

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 More actions · Answer 7

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.:-)