April 4, 2013 at 5:34 am
Hugo Kornelis (4/4/2013)
Great idea for a question, Kapil!But ... (isn't there always a "but"?)
When I saw the question in the newsletter, I expected the answer options to be phrased in terms of the supplied data. Like, for instance, "Both query 1 and query 2 returns rows marked thrid, fourth and ninth" (not saying this is either the right answer or even a believable distracter, just giving a random example). After all, what is the point of giving sample data in the question if you're not using it in the answer?
So I worked out both queries in my head and knew the result sets to expect when I clicked on the link to the website - and found that the answer options were just descriptions instead of actual data.
Then, the descriptions were not very clear worded. Kapil already admitted so himself, so I won't pound on that.
And finally, the answer marked as "correct" is not correct. I'm afraid Kapil himself has fallen victim to misunderstanding his own words, or to misphrasing what he had in mind. And the people claiming that answer options 2 and 4 are the same are also wrong.
Option 4, marked as correct, starts with: "Query 1 return all rows of table 2 where Flag =1 with (...)". You can even stop reading there (though I did read the whole answer). This already makes the answer option wrong. Row "eighth" in Table2 has Flag=1, but should not be returned by Query 1. (To make very sure I was not mistaken, I copied and pasted the code in SSMS, fixed the lowercase/uppercase inconsistencies, and executed the code - I was not mistaken).
Option 2, marked as incorrect, reads: "Query 1 will return all rows of table 1 and Query 2 will return only rows where condition matched Flag = 1". This is correct. Query 1 returns all rows from table1 (because there is no WHERE filter to cut out Table1 rows, and a LEFT join will never remove rows from the table listed on the left hand side). Rows "First" and "Second" are matched to the corresponding row from Table2, because the entire join condition evaluaties as true - the ID values match and Flag = 1. The other rows are "matched" to null values, because there are no rows in Table2 that satisfy the join - either the ID values don't match, or Flag is not 1 (or both). And rows "Eighth" and "Ninth" from Table2 do not appear anywhere, because they can also not be matched to any row in Table1 with the join condition evaluating to true.
Too bad that a question with a good idea suffers from poor execution. But I'm going to say to Kapil what I said yesterday to the author of that day's question: having good ideas for questions (and the guts to submit them) is the most important. Don't give up, keep submitting questions. I'm sure the next will be better!
Another great explanation from Hugo.
+1
April 4, 2013 at 5:46 am
I like the question. Not of course the form and wording of the answers (|I won't say more than that as Hugo has covered it fullyHugo) but the idea of a question that illustrates one thing about outer joins that catches many people out and another than catches some.
The explanation would have been clearer if it had mentioned those two traps. The first is that in a join a condition in the ON clause does absolutely nothing other than help determine which rows in the two tables match each other. For example in a LEFT JOIN a condition included in the join clause that refers only to the left hand table means nothing other than "no right hand row matches a left hand row that fails to match this condition" and certainly says nothing about which left hand rows will be visible in the result of the join (they all will, regardless of any such condition). The second is that conditions in the WHERE clause have no effect on the join: the join is carried out using the conditions in the ON clause; only after the join has been completed will the WHERE clause do its filtering - so a condition in the WHERE clause can ensure that some rows in the join's result set are excluded from the results of the select statement. For example in the WHERE clause that a condition that refers only to a column derived from the right hand table may eliminate all rows which took values from some particular rows in the left hand table, and this is true for select statements which use inner joins, left joins, right joins, or full joins.
Despite its faults, this is a good question because it does highlight important facts about joins. So keep up the good work, Danny.
Tom
April 4, 2013 at 6:05 am
As many else wrote, bad choices... both answers are correct... Better with QotD where right/wrong is not up for discussion...:doze:
April 4, 2013 at 6:56 am
Thanks Hugo for verifying the answers. I couldn't for the life of me figure out what I'd missed.
April 4, 2013 at 7:03 am
Thanks Kapil for the early correction & thanks to everyone for the explanations.
April 4, 2013 at 7:16 am
L' Eomot Inversé (4/4/2013)
I like the question. Not of course the form and wording of the answers (|I won't say more than that as Hugo has covered it fullyHugo) but the idea of a question that illustrates one thing about outer joins that catches many people out and another than catches some.The explanation would have been clearer if it had mentioned those two traps. The first is that in a join a condition in the ON clause does absolutely nothing other than help determine which rows in the two tables match each other. For example in a LEFT JOIN a condition included in the join clause that refers only to the left hand table means nothing other than "no right hand row matches a left hand row that fails to match this condition" and certainly says nothing about which left hand rows will be visible in the result of the join (they all will, regardless of any such condition). The second is that conditions in the WHERE clause have no effect on the join: the join is carried out using the conditions in the ON clause; only after the join has been completed will the WHERE clause do its filtering - so a condition in the WHERE clause can ensure that some rows in the join's result set are excluded from the results of the select statement. For example in the WHERE clause that a condition that refers only to a column derived from the right hand table may eliminate all rows which took values from some particular rows in the left hand table, and this is true for select statements which use inner joins, left joins, right joins, or full joins.
Despite its faults, this is a good question because it does highlight important facts about joins. So keep up the good work, Danny.
Thanks for the explanation TOM..
What about AND if we used in place of WHERE?
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 4, 2013 at 7:55 am
Changing the where to and will result in the same output from both of the queries.
April 4, 2013 at 8:03 am
For better understand about the topic, we may need know the "Logical Query Processing" by Itzik Ben-Gan.
http://www.sql.co.il/books/insidetsql2008/Logical%20Query%20Processing%20Poster.pdf
--------------------------------------
;-)“Everything has beauty, but not everyone sees it.” ― Confucius
April 4, 2013 at 8:19 am
The "correct" answer is clearly wrong and I do not like seeing "Incorrect" in my list of QotD answers when I wasn't wrong 🙁
Query 1 does not return all rows from table2 where flag=1.
Query 1 returns all rows from table1 and only the rows from table2 where table2 IDs match a table1 row with flag=1.
Query 2 will return only rows where table1 and table2 IDs match and the matching row in table2 has flag=1.
Logically, Answer 2 was correct. In Query1, all rows were returned from table1. In Query2, only rows matching the condition of Flag = 1 were returned.
April 4, 2013 at 8:24 am
Lon-860191 (4/4/2013)
Changing the where to and will result in the same output from both of the queries.
Sorry Lon,
Changing the WHERE to AND will result in different output rows... that's why I posted this as QOTD
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 4, 2013 at 8:33 am
SELECT * FROM table1 t1 LEFT JOIN table2 t2 on t1.id = t2.id
AND t1.flag = 1
SELECT * FROM table1 t1 LEFT JOIN table2 t2 on t1.id = t2.id
WHERE t2.flag = 1
Was this meant to compare "flag" from t1 in query1 and t2 in query2?
b) Query 1 will return all rows of table 1 and Query 2 will return only rows where condition matched Flag = 1
d) Query 1 return all rows of table 2 where Flag =1 with all rows of table 1 while Query 2 only return rows from table 1 and table 2 where FLAG = 1
The query parser would have rejected an ambiguous reference to "Flag" - I'm similarly confused. 🙂
"all rows of table 2" sounds to me like 'right join' so I ignored that answer. I understood that filtering for values in the right table of a left join effectively turns the result into an inner join but I misread the answers' meaning(s).
good discussion follows though, so that's value too.
April 4, 2013 at 8:42 am
nice question....
manik
Go Fast As Possible
Manik
You cannot get to the top by sitting on your bottom.
April 4, 2013 at 9:51 am
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.
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?
April 4, 2013 at 10:02 am
+1
April 4, 2013 at 10:42 am
The wording of the question didn't bug me, the answers were worded poorly.
ah well.
Viewing 15 posts - 16 through 30 (of 57 total)
You must be logged in to reply to this topic. Login to reply