Why not "Invalid column name XYZ" error in subquery; although column name is not in subquery table?
sql, sql-server, sql-server-2005, subquery, t-sql
Solution
Subqueries inherit columns from outer queries.
I guess your SomeOtherDb.dbo.Customer does have a CustomerId column (which also seems likely from the names).
Which then also probably means that you are not doing with the subquery what you want to do with it - if the table in the subquery does not have a CustomerId column (and it seems so, otherwise there would be no error when running the subquery in itself), then the subquery selects and returns the outside CustomerId, and since that is the only column in the subquery, the subquery is useless.
Problem
When I run this query ``` SELECT CustomerId FROM Stocks.dbo.Suppliers ``` It gives me this error. Invalid column name 'CustomerId'. This error is valid as there is no column CustomerId in Suppliers table; but when I use same query in subquery it does not give any error E.g. ``` SELECT * FROM SomeOtherDb.dbo.Customer WHERE CustomerId In( SELECT CustomerId FROM Stocks.dbo.Suppliers) ``` Here I am expecting same error "Invalid column name" but query runs without any error. Fully qualified name is just convention both dbs are on same server. CustomerId does exists in SomeOtherDb.dbo.Customer table but not in subquery. Why is this behavior? Is this something with subquery? Thanks.