May 19, 2015 at 10:04 pm
Comments posted to this topic are about the item SubQuery behavior
May 19, 2015 at 11:25 pm
Thanks for this question which seems easy according to me.
I was surprised by the low percentage of good choice ( 33% ) , but I understood when I discovered that there were only 9 attempts : with a so low count , the percentage has no meaning.
I needed 10 minutes to think about this QOT , as I was fearing for a trap and SQL Server 2008 is too far from me.
After my choice validation , I have tested against 2012 and 2014 : no change.
May 20, 2015 at 12:08 am
Easy one
May 20, 2015 at 1:59 am
pmadhavapeddi22 (5/20/2015)
Easy one
Easy, but batches are too long.
Query 3 and 4 differs only for the constant value.
May 20, 2015 at 2:11 am
This was removed by the editor as SPAM
May 20, 2015 at 4:30 am
Nice idea, but a shame there was only one answer that had query 1 working. I didn't have to read any of the other queries as this answer had to be the correct one.
May 20, 2015 at 5:18 am
I thought the question was going to be how many rows are returned from each query. I would have picked 2, 1, 0, 2. However, I ran the queries and was surprised to see that the first query returns only one row. I expected the result of the subquery to be 1 and that used as a filter against all rows from EMPLOYEE. Learned something.
May 20, 2015 at 6:20 am
Thanks for an easy one. We're at 64% correct now, which is still a bit surprising.
May 20, 2015 at 6:44 am
Thanks for the question.
May 20, 2015 at 7:34 am
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:
select stuf from mystuff m
where not exists
(
select 1 from otherstuff
where stuf = m.stuf
)
so in the subquery, stuff is resolved to be a column in otherstuff. Of course if otherstuff ever changes, removing column "stuf", I'm, well, stuffed! So, generally, aliasing is safer and easier to maintain. One exception might be when the subquery references the same table like:
select stuf from mystuff m
where not exists
(
select max(something) from mystuff
where stuf = m.stuf
group by stuf
having max(something) > some_max_value
)
I feel that is a little safer since the same table is involved in the outer and inner query.
Gerald Britton, Pluralsight courses
May 20, 2015 at 8:17 am
Thanks, interesting question.
- 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
May 20, 2015 at 8:38 am
Lots of reading, but in the end it was simple. Thanks, Gopi!
May 20, 2015 at 9:00 am
I really like the question for illustrating this behavior of columns referenced in a subquery.
Like Toreador pointed out, though, after reading query 1, it was clear it would run successfully, so I didn't have to even read the rest (I did though, just because I'm paranoid :-))
Thanks for the question!
May 20, 2015 at 9:25 am
I recalled this subquery behavior from another question a year back or more. Thankfully I have never seen any unaliased column references like this in production! Thanks for the question.
May 21, 2015 at 1:15 am
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.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply