Why not "Invalid column name XYZ" error in subquery; although column name is not in subquery table?

Question

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.

Answer 1

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.

Answer 2

Hi,

The answer to your question ("why no error") is above, but maybe a little help on how to avoid this type of issue in future: instead of using a subquery to do this, use a left join:

SELECT C.* 
FROM SomeOtherDb.dbo.Customer AS C
LEFT JOIN Stocks.dbo.Suppliers AS S ON C.CustomerId = S.CustomerId
WHERE S.CustomerID Is Null

This query, when constructed with a join that is possible of course, will always perform as well as your original one or better - and you have the added benefit of avoiding that nasty problem above. Because in this construct you will naturally use table names, it will be more obvious when there is a problem, like the same table name on both side of the equal sign. Subqueries suck, I am on a permanent crusade against them.

(that said, I know lots of people are on a crusade against aliasing, which I used above to simplify / condense the code :))