March 21, 2011 at 3:09 am
I have following query...
select * from WorkManagement_Story
where WorkManagement_Story.storyId in
(Select Storyid from
WorkManagement_ResourceToFocusteam)
WorkManagement_ResourceToFocusteam is not having column Storyid
So If executed individually, the subquery gives the following error:
Select Storyid from
WorkManagement_ResourceToFocusteam
Invalid column name 'Storyid'.
But if I execute the complete query then it returns the resultset.
I think its just not validating the inner query inside IN clause...
Can anyone throw some light on this?
March 21, 2011 at 3:37 am
SQL is validating it. The column there is perfectly valid because it's a column from the outer query. Any subquery can reference any column of any table defined in the outer query. If it couldn't, correlated subqueries would be impossible to write.
This is one reason why you should qualify columns with the table name. The following will fail.
select * from WorkManagement_Story wms
where wms.storyId in
(Select wmrf.Storyid from
WorkManagement_ResourceToFocusteam wmrf)
p.s. Don't use select *. Lazy coding.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 22, 2011 at 2:55 am
Thanks a lot for prompt reply 🙂 !
But I don't understand how it works internally & refers to the column in out query & not in the table in inner query?
Regards,
Neeta
March 22, 2011 at 2:59 am
It's not about internal workings.
You did not qualify the column with the table name. Hence SQL had to look through all the available tables (both in the subquery and in the outer query) to find which table that column belonged to. Both the tables in the subquery and the tables in the outer query are in scope at that point, so SQL looks through both. It found one table that had that column name, a table in the outer query, so it was happy that the reference was valid.
What you wrote was completely equivalent to
select * from WorkManagement_Story
where WorkManagement_Story.storyId in
(Select WorkManagement_Story.Storyid from
WorkManagement_ResourceToFocusteam)
That is perfectly valid T-SQL syntax.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 22, 2011 at 3:09 am
Thanks a lot for more details! 🙂
March 22, 2011 at 3:54 am
Hey,
What will happen when both the tables will have this column? Which column it will refer to?
Regards,
Neeta
March 22, 2011 at 3:59 am
Probably an ambiguous column error.
March 22, 2011 at 4:27 am
I just tried an example...
It refers to the column in the table mentioned in inner query...
Doesn't give error... 😉
Regards,
Neeta.
March 22, 2011 at 4:32 am
OK. It'd be an ambiguous column error if you were to join these hypothetical tables on the IDs.
March 22, 2011 at 4:39 am
Neeta Patil (3/22/2011)
I just tried an example...It refers to the column in the table mentioned in inner query...
Yup. Binding is first to the tables in the inner, then if no match is found to the outer.
Point still stands, qualify your column names and you won't get odd surprises
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply