April 4, 2013 at 11:06 am
Thanks for the question Kapil. It brings up good points regarding joins.
April 4, 2013 at 11:53 am
bev.kaufman (4/4/2013)
I guess the lesson here is that JOIN clauses should be limited to the matching fields, and all other filters go in the WHERE clause. Does that sound right?
Nope.
The lesson should be that, at least in OUTER joins, all the requirements used to determine if a row match or don't match should go to the ON, and all the requirements that determine whether a row should be entirely omitted from the resultset should be in the WHERE.
Putting a filter on the left table in the ON clause of the outer join, as in this question, may be unusual - but I have had cases where I used it to achieve what I wanted.
April 4, 2013 at 12:26 pm
Okay, then how would you have written a left outer join if you wanted to fetch only the records where the left side had flag = 1?
April 4, 2013 at 12:36 pm
bev.kaufman (4/4/2013)
I too had trouble with the wording of the options, and ended up picking the first (and wrong) option because the other three looked like they couldn't be true. But that doesn't mean I would have gotten the right answer.I worked out in my head that Query 1 would produce records 1, 2, 4 and 6, and Query 2 would produce records 1 and 2. After running the code, I am forced to accept the evidence of my eyes, but I still don't understand WHY records 3, 5 and 7 are in the Query 1 mix. I would have thought having t1.flag = 1 as part of the ON clause would have filtered out the 2 flags.
No, in a left join nothing in the ON clause can filter out anything in the left table: it's a left join, so ALL the rows of the left table get used in teh result of the join; a test of some condition on the left rows simply means "no rows in the right table can match a row in the left table that fails this condition", it doesn't say anything at all about which rows of the left table will be used in the results.
I guess the lesson here is that JOIN clauses should be limited to the matching fields, and all other filters go in the WHERE clause. Does that sound right?
It is very common for people (including me) to recommend that people limit themselves that way, because, for some reason that escapes me, many people find it hard to understand how conditions in the ON clause work or what they do; and it's usually a good idea not to use things which one doesn't understand. So yes, I think that sounds right.
Tom
April 4, 2013 at 12:41 pm
bev.kaufman (4/4/2013)
Okay, then how would you have written a left outer join if you wanted to fetch only the records where the left side had flag = 1?
SELECT *
FROM Table1 AS t1
LEFT JOIN Table2 AS t2
ON t1.ID = t2.ID
WHERE t1.Flag = 1;
It is important that you filter on t1.Flag, not on t2.Flag. They may be the same in the sample data provided, but t2.Flag can be NULL as a result of the outer join for rows in Table1 that have no match in Table2.
April 4, 2013 at 12:46 pm
kapil_kk (4/4/2013)
Thanks for the explanation TOM..What about AND if we used in place of WHERE?
You can't use AND in place of WHERE. In a select statement, any AND is either inside the FROM clause (in the ON condition) or inside the WHERE clause, it doesn't exist at the same level as WHERE.
Lon-860191 (4/4/2013)
Changing the where to and will result in the same output from both of the queries.
That 's true for this particular data, but only because every pair of rows which match in the id column match in the flag column as well; actually, I sometimes hate it when someone uses data with this sort of accidental coincidence when illustrating some point, because it might fool people into thinking there's a general rule there when there's nothing but a coincidence
Tom
April 4, 2013 at 12:57 pm
Hugo Kornelis (4/4/2013)
bev.kaufman (4/4/2013)
Okay, then how would you have written a left outer join if you wanted to fetch only the records where the left side had flag = 1?
SELECT *FROM Table1 AS t1
LEFT JOIN Table2 AS t2
ON t1.ID = t2.ID
WHERE t1.Flag = 1;
That's the way I would do it too. But some people would write a subquery inside the FROM clause:
SELECT *
FROM (select * from Table1 where flag = 1) t1
LEFT JOIN Table2 as t2
ON t1.ID = t2.ID ;
It is important that you filter on t1.Flag, not on t2.Flag. They may be the same in the sample data provided, but t2.Flag can be NULL as a result of the outer join for rows in Table1 that have no match in Table2.
Yes, that is very important.
Tom
April 4, 2013 at 1:26 pm
Which means the Flag filter belonged in the WHERE clause, not the JOIN.
I think I'm starting to get it. The JOIN clause is about what gets included on the right side. Were I to rewrite it as
SELECT * FROM Table1
LEFT JOIN Table2
ON Table1.ID = Table2.ID
AND Table2.Flag = 1
you would still get all 7 records from Table 1, but the Table2 fields for record 3 would be null.
I will now stop badgering you about it, and start looking over my existing code to see if any joins need to be rewritten.
April 4, 2013 at 1:38 pm
bev.kaufman (4/4/2013)
Which means the Flag filter belonged in the WHERE clause, not the JOIN.I think I'm starting to get it. The JOIN clause is about what gets included on the right side. Were I to rewrite it as
SELECT * FROM Table1
LEFT JOIN Table2
ON Table1.ID = Table2.ID
AND Table2.Flag = 1
you would still get all 7 records from Table 1, but the Table2 fields for record 3 would be null.
I will now stop badgering you about it, and start looking over my existing code to see if any joins need to be rewritten.
Yes, exactly! You've got it!
Good luck with checking your old code... 😉
April 4, 2013 at 2:17 pm
The underlying problem here is that poorly constructed (but otherwise interesting) questions are finding their way into QOTD without some sort of review. What is the current process for getting questions published? Does anyone have any practical suggestions for improving the vetting process?
...One of the symptoms of an approaching nervous breakdown is the belief that ones work is terribly important.... Bertrand Russell
April 4, 2013 at 7:06 pm
mtassin (4/4/2013)
The wording of the question didn't bug me, the answers were worded poorly.ah well.
ding ding ding +1
nice idea for the question - poorly executed.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 4, 2013 at 11:56 pm
Really a great QotD. No matter whether the wording of question or answer options are wrong. It triggered a great discussion where everyone understood the behaviour of On & Where clause in Outer joins...
Good work Kapil..
--
Dineshbabu
Desire to learn new things..
April 5, 2013 at 12:33 am
Thanks for the question, better luck next time 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 5, 2013 at 2:05 am
Good question and intentions. No hard feelings/discouragement but answer is wrong.
The question can be made more interesting by using the same predicate in join condition and in where clause ie. t1.flag = 1 as join condition in query 1 and as where clause in query 2. And only 2 option answer
Option 1: Query one returns all rows from Table1 irrespective of Flag value and Query two returns only rows from Table1 with Flag=1
Option 2: Query one and two both return same results ie. only rows from Table1 with Flag=1.
IMO SQL gurus even at times fallen victim to overlooking "logical query processing". I can recall well know data architect yelling over a support call - LEFT JOIN is NOT functioning as it is expected to be, must be a MS bug.
April 5, 2013 at 6:51 am
Thanks for the question. I have made this mistake many of times of creating a left join and then filtering the right table.
Viewing 15 posts - 31 through 45 (of 57 total)
You must be logged in to reply to this topic. Login to reply