Tom.Thomson (9/7/2011)
But of course one could say that the query processing ought to notice that there is no ambiguity in number 4, because any possible ambiguity is eliminated by the ON clause of the join. Would such ambiguity elimination be a good thing or a bad thing? I'm in two minds about it.
Because the two values are equal, I agree that it's not technical ambiguity; however, you can use nonequalities in the joins too like:
ON Column1 > Column2
Shouldn't happen often but the complexities in the query optimizer would be too difficult for Microsoft to handle 😀
And to make it clear, it was due to the ORDER BY clause so the subject of the QOTD was still okay. If you were to remove the ORDER BY clauses, all 4 queries would run properly. Query 1 and 4 fail because of the invalid ORDER BY clauses -- not because of any other reason.