April 5, 2013 at 7:44 am
Dineshbabu (4/4/2013)
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..
Thanks 🙂
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 5, 2013 at 10:00 am
Great reminder that the "ON" clause and "WHERE" clause conditions are not equivalent.
I generally don't filter in the ON clause because it is easy for the query to get messed up when it is updated by others (or worse, when I do it).
I have done this a few times when there are a large number of records that can be removed during the join - mostly I don't do it now, it has come back to haunt me too often either during maintenance or a last minute requirements change.
Thanks also to chgn01 for the pdf reference.
Have a great weekend!
-Chris C.
April 7, 2013 at 10:20 pm
Really Bad luck,
April 8, 2013 at 2:13 am
I replied to this QOTD quite early on but I have revisited it a few times due to the comment emails I get from it.
Never before have I seen so many comments stating that the question and answer was badly worded and yet mitigated by the "Well done for trying" after-taste.
I do echo this sentinement myself but I also applaud at least one commenter who asked something on the lines of
Who edits the QOTD submissions and why did they not pick up on this?
When I put forward a QOTD myself it took at least two weeks before being published. Thats two weeks where someone at SQL Server Central needs to validate and quality check the question.
Dave
David Bridge
David Bridge Technology Limited
www.davidbridgetechnology.com
April 8, 2013 at 3:32 am
DavidBridgeTechnology.com (4/8/2013)
Who edits the QOTD submissions and why did they not pick up on this?
Steve has replied to these and similar questions in the past; I don't think anything has changed since then.
As far as I know, the only one looking at the QotD before it goes live (other than the submitter) is Steve. And he has a lot of work on his plate, so he lacks the time to do a thorough in-depth review.
The logical follow-up question would be "why not recruit some people to serve as second eye". I have suggested this to Steve myself. His reply was that he has tried that before, but found that, no matter how well it starts, eventually there will be a time when the editor starts to fall behind. Jobs, families, other obligations - real life happens. When the continuity of the daily question was at risk because of this, Steve decided to put matters in his own hands again.
April 8, 2013 at 5:25 am
A Shocker for me !:w00t:
I actually started doubting what made my option 2 get wrong. But thanks to the Discussion with the question that actually made me say to myself, "Things Happen and Keep Trying and Learning"
Cheers 🙂
Angad
--Angad Singh
If I Stop Learning, I Cease to Be A SIKH !
April 9, 2013 at 2:01 am
kapil_kk (4/4/2013)
Hi all,I also thinks that the options are not well written... Sorry for that 🙁 ..
My point of concern was to to throw light on different behaviors of AND and WHERE clause when use with LEFT and INNER JOIN..
--Query 1
SELECT
*
FROM table1 t1
LEFT JOIN table2 t2
on t1.id = t2.id AND t1.flag = 1
--Query 2
SELECT
*
FROM table1 t1
left JOIN table2 t2
on t1.id = t2.id
WHERE t2.flag = 1
If you execute these queries you will get same amount of rows in case of INNER JOIN while no of rows gets different when you put LEFT JOIN in this...
+1
The provided answers will all be wrong!
The first query will return ALL records from table1 WHERE flag = 1 and - due to LEFT JOIN - depending records from TABLE2
The second query will only return records which fit with both tables because the WHERE clause is the prediction!
The LEFT JOIN will be ignored in this case
Microsoft Certified Master: SQL Server 2008
MVP - Data Platform (2013 - ...)
my blog: http://www.sqlmaster.de (german only!)
April 10, 2013 at 4:45 am
Hugo Kornelis (4/4/2013)
...When I saw the question in the newsletter, I expected the answer options to be phrased in terms of the supplied data.
...
I agree. It reduces the change of misreading the answer.
April 10, 2013 at 6:33 am
B should have been the answer. Even as a SQL noob I knew this
April 24, 2013 at 4:04 am
The options were not well defined but the question's intentions were good.
July 5, 2013 at 1:01 pm
I just tried this question and picked B (correct answer was D, it says).
Even though I see the point of the issues with the question wording, I want to praise the author posting a question like this. I had never given enough thought to these finer points of outer joins versus WHERE clauses. This question has shown me I need to study this harder.
Thanks again.
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
December 18, 2013 at 7:57 pm
None of the options is 100% accurate. 🙂
June 10, 2015 at 12:00 am
The question is nice. The differnce between an AND in the ON of the left join and a WHERE on the other side.
It is a pity of the weak elaborations of the answers.
Viewing 13 posts - 46 through 57 (of 57 total)
You must be logged in to reply to this topic. Login to reply