May 21, 2015 at 2:49 am
Hugo Kornelis (5/21/2015)
g.britton (5/20/2015)
Great question! It illustrates the benefits and dangers of using unaliased column names in queries. IIRC SQL looks in the current namespace for a match on the column name, moving from the inside towards the outermost, in scope row set. Handy if you know what you're doing. Confounding at times when the column is an outer reference. Personally I only use this when I'm referring to the immediate row set, like this:[... snipped ...]
I feel that is a little safer since the same table is involved in the outer and inner query.
The explanation is correct - for un-qualified column references, SQL Server will work from the innermost scope outward until it finds a hit.
But my rule for avoiding conflicts and unclear code is much simpler and much more straightforward than yours. Any query that uses more than a single reference to a single table, I will always add a mnemonic alias for each table, and I will always use that to table-qualify each and every column reference. No exceptions.
Hugo : +1 . A wise and careful behavior that I will try it every time . And thanks for this tip.
May 21, 2015 at 5:01 am
Easy one. A practice exam question was about the same behaviour. Once you've spent hours finding out why your answer isn't correct, you tend to never forget it again 😀
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 21, 2015 at 5:35 pm
Shouldn't the subqueries contain a TOP 1 ?
May 21, 2015 at 8:46 pm
Well, if you wanted to make sure that changes to the data wouldn't result in the queries' returning an error because of using = with a subquery that returns more than 1 value, then that would be a way to do it.
With the particular data provided, though, none of the subqueries return more than 1 value anyway, so it's not necessary.
Cheers!
May 22, 2015 at 7:12 am
Revenant (5/20/2015)
Lots of reading, but in the end it was simple. Thanks, Gopi!
Too much reading, in fact! I could have saved a lot of time by reading the answers before the question. But definitely simple. and definitely illustrates the need for using table aliases to qualify all column names if you want your code to read as if it means what it actually means.
Tom
May 22, 2015 at 10:36 am
Rick Harker (5/21/2015)
Shouldn't the subqueries contain a TOP 1 ?
No. It would change the meaning of the queries. As posted, they implement "give me the department ID for Human Resource and make sure to throw a tantrum if there is a data quality issue". Adding TOP 1 changes this to "give me a department ID for Human Resource and if there is a data quality issue, pretend you never noticed and definitely don't tell anyone".
I know which version I prefer. (Allthough in reality, I would (a) not use a subquery for this anyway, but rather a join; and (b) have declared a UNIQUE constraint on the Name column in the Department table to prevent data quality issues.)
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply