Columns in Sub Queries

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.

tabinsc SSCommitted Points: 1812 More actions · Answer 4

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.

Tony
------------------------------------
Are you suggesting coconuts migrate?

joshcsmith13 Hall of Fame Points: 3381 More actions · Answer 5

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)?

methody.myers SSC Journeyman Points: 78 More actions · Answer 6

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]

Dave Vroman SSC Eights! Points: 821 More actions · Answer 7

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.

Bob JH Cullen SSCrazy Points: 2082 More actions · Answer 8

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

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

david.annable SSC Veteran Points: 281 More actions · Answer 9

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

joshcsmith13 Hall of Fame Points: 3381 More actions · Answer 10

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

tinkang SSC Journeyman Points: 79 More actions · Answer 11

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

Hugo Kornelis SSC Guru Points: 64780 More actions · Answer 12

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: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

guptashailesh.87 SSC Enthusiast Points: 177 More actions · Answer 13

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