为什么不显示“无效的列名 XYZ”子查询错误；虽然列名不在子查询表中？
当我运行这个查询时，
SELECT CustomerId FROM Stocks.dbo.Suppliers
它给了我这个错误。列名“CustomerId”无效。此错误有效，因为供应商表中没有列 CustomerId；但是当我在子查询中使用相同的查询时，它不会给出任何错误，例如
SELECT *
FROM SomeOtherDb.dbo.Customer
WHERE CustomerId In( SELECT CustomerId
FROM Stocks.dbo.Suppliers)
这里我期待相同的错误“无效的列名”，但查询运行时没有任何错误。
完全限定名称只是约定两个数据库都在同一服务器上。
CustomerId 确实存在于 SomeOtherDb.dbo.Customer 表中，但不存在于子查询中。
为什么会出现这种行为？这是子查询的东西吗？
谢谢。
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
子查询继承外部查询的列。
我猜您的 SomeOtherDb.dbo.Customer 确实有一个 CustomerId 列（从名称来看也很可能）。
这也可能意味着您没有对子查询执行您想要执行的操作 - 如果子查询中的表没有 CustomerId 列（看起来是这样，否则在运行子查询时不会出现错误）本身），然后子查询选择并返回外部 CustomerId，并且由于这是子查询中唯一的列，因此子查询是无用的。
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.
您的问题的答案（“为什么没有错误”）在上面，但也许对将来如何避免此类问题有一点帮助：不要使用子查询来执行此操作，而是使用左连接：
此查询在构造时当然，使用一种可能的连接，其性能将始终与您原来的连接一样好或更好 - 而且您还有避免上述令人讨厌的问题的额外好处。因为在这个构造中你自然会使用表名，所以当出现问题时会更明显，比如等号两边的表名相同。子查询很糟糕，我正在对它们进行永久的讨伐。
（也就是说，我知道很多人都在反对别名，我上面用它来简化/压缩代码:)）
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:
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 :))