sjimmo (1/4/2011)
Got it wrong, but learned something - which is the keyHugo,
The knowledge tested is good, but the way the question has been built suggests to me that the author tried to trick people into overlooking key elements
Many have said, and usually do of most questions that the QOD's are tricks but I question the abilities of many. Don't take offense, but answer this (not necessarily to me) - a portion of many DBA's time is spent reviewing code from developers who have varing levels of expertise in writing SQL code. Isn't part of what we are supposed to be doing looking for things such as this?
I know, fine one to talk when I got it wrong, but...:-)
Agreed but normally when you are helping someone troubleshoot a problem, you're given the code and what the output is and what it should be. It's easy to figure out the problem that way.
On these QOTD's, we are given a subject that says one thing, leading us to believe that's what is being tested, and then when you look at the answer, it was all about something else completely different.
If we got the output provided to us and was asked to state why some of the records were missing, I bet more people would have figured out it was due to the "UNION" versus "UNION ALL" but that's not the way the question was presented.