SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Bradley Deem / Where and Join On Clause the Same? / Latest Posts

RE: Where and Join On Clause the Same?

prabhu_hl007 — Fri, 15 May 2009 03:10:45 GMT

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

RE: Where and Join On Clause the Same?

sknox — Thu, 07 May 2009 11:46:43 GMT

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. :-P[code]SELECT * FROM T1LEFT JOIN T2 ON t1.col1 = t2.col1WHERE t2.col1 IS NULL[/code]is telling the server to filter [b]the result set[/b] to those rows with a NULL value in the column output from t2.col1, whereas[code]SELECT * FROM T1LEFT JOIN T2 ON t1.col1 = t2.col1 AND t2.col1 IS NULL[/code]is telling the server to condition the join on there being a NULL value in t2.col1 (i.e, to filter [b]the input from t2 into the join[/b] to rows with a NULL in t2.col1)

RE: Where and Join On Clause the Same?

john.botibol — Tue, 05 May 2009 08:43:46 GMT

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)

RE: Where and Join On Clause the Same?

Lynn Pettis — Mon, 04 May 2009 10:50:27 GMT

[quote][b]dunnjoe (5/4/2009)[/b][hr]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![/quote]I prefer the ANSI style better. It separates the join criteria from filter criteria.

RE: Where and Join On Clause the Same?

sjsubscribe — Mon, 04 May 2009 10:01:52 GMT

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.

RE: Where and Join On Clause the Same?

dunnjoe — Mon, 04 May 2009 08:51:13 GMT

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!

RE: Where and Join On Clause the Same?

Lynn Pettis — Mon, 04 May 2009 06:54:36 GMT

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.

Where and Join On Clause the Same?

Bradley Deem — Sat, 02 May 2009 13:40:30 GMT

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