SubQuery behavior

  • Comments posted to this topic are about the item SubQuery behavior

  • 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.

  • Easy one

  • pmadhavapeddi22 (5/20/2015)


    Easy one

    Easy, but batches are too long.

    Query 3 and 4 differs only for the constant value.

  • This was removed by the editor as SPAM

  • 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.

  • 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.

  • Thanks for an easy one. We're at 64% correct now, which is still a bit surprising.

  • Thanks for the question.

  • 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

  • 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

  • Lots of reading, but in the end it was simple. Thanks, Gopi!

  • 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!

  • 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.

  • 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 Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply