Columns in Sub Queries

  • Funny how the column names affect the outcome....

  • great question..

    Thanks for sharing.

  • Cadavre - I fully accept that in the case of the JOIN you illustrate, that the SELECT will locate the columns from the join product OK. But a JOIN explicitly says "take columns from this table and that table and make them all available". That, I would suggest, is not the same as a simple, straightforward SELECT as a sub-query. The darn thing is even wrapped in parenthesis, making it look even more like it should be self-contained!

    Anyway, I am not arguing that it works this way or that, but merely trying to understand why the designers decided it should work the way round it does, when I can see no practical benefit for it do it that way.

  • This gotcha happened to us recently where we used a subquery in a DELETE statement and ended up deleting every row in the table. Lesson learned: Use Aliases.

    Are you suggesting coconuts migrate?

  • I'm with you Bob. I still don't get the "point" of the query working this way for a subquery. So, from a logical perspective, what does the subquery return (how many rows)?

  • Was ready to answer 0 rows returned for the wrong reason; glad I went for the error message and was counted wrong, so that I could understand the underpinnings of the problem. Without an explicit outer join, the query in the sub-select was forgiven because of the parent or first evaluated query. Somehow, I thought that explicit outer joinn language was required for this forgiveness. This is an interesting problem then, in that it shows query behavior on a missing column in a sub-select if that column exists for the initial select. The optimizer and query rewrite, then treat the missing column in a similar fashion then to the forgiveness of an outer join. The column is assumed to exist somewhere. A data modeling tool may catch this inconsistency in some of the modeling checks in the tools. Revisiting the model with unique index patterns for often used or poorly performing queries might also nail the missing column in Category. [/i]

  • Because I always use aliases, for entirely different reasons having to do with readability and speed of programming, I have never run into this one. What a surprise. Thanks Much.

  • Ah well, mark it up as one of SQL-Life's many mysteries, I suppose!

    Thanks, all, for the discussion. Time to move on.

  • I have to admit that I didn't 'get' this until I started to read the responses on here... :blush:

  • Can anybody answer my question? Does the subquery for the IN return anything? What is its result set? Thanks.

  • The CategoryId in subquery exactly means the CategoryId in Product , and you will get all the rows in Product.

  • joshcsmith13 (2/24/2014)

    I'm with you Bob. I still don't get the "point" of the query working this way for a subquery.

    It *has* to work this way for subqueries, otherwise all correlated subqueries would fail. I am sure that if the columns names in the question code had been table-qualified, there would have been much less confusion.

    So, from a logical perspective, what does the subquery return (how many rows)?

    Because it is now a correlated subquery, it has to be re-evaluated (logically - the query optimizer is free to choose a different physical execution as long as it will not affect the results) for each row in the outer query.

    For the Knife, Fork, and Spoon, the correlated subquery becomes "SELECT 1 FROM dbo.Category", and because there are three rows in Category, the result set will be three rows with one column, all with the value 1.

    For the Tumbler, the inner query is "SELECT 2 FROM dbo.Category", so now we again get three rows, but with the value 2 in all of them.

    And thenfor the Pint Glass, the inner query returns three rows with the value 3 in the single column.

    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog:
    SQL Server Execution Plan Reference:

  • I am not happy with the correct answer.

    Because i executed queries like this but it always gave me error as

    Msg 207, Level 16, State 1, Line 2

    Invalid column name 'ABC'.

Viewing 13 posts - 16 through 27 (of 27 total)

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