为什么不是“无效的列名 XYZ"?子查询错误；虽然列名不在子查询表中? [英] Why not "Invalid column name XYZ" error in subquery; although column name is not in subquery table?

问题描述

当我运行这个查询时

SELECT CustomerId FROM Stocks.dbo.Suppliers

它给了我这个错误.列名CustomerId"无效.此错误是有效的，因为供应商表中没有列 CustomerId；但是当我在子查询中使用相同的查询时，它不会给出任何错误，例如

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 确实存在于 SomeOtherDb.dbo.Customer 表中，但不存在于子查询中.

CustomerId does exists in SomeOtherDb.dbo.Customer table but not in subquery.

为什么会出现这种行为?这是子查询的东西吗?

Why is this behavior? Is this something with subquery?

谢谢.

推荐答案

子查询从外部查询继承列.

Subqueries inherit columns from outer queries.

我猜您的 SomeOtherDb.dbo.Customer 确实有一个 CustomerId 列(从名称看来也很可能).

I guess your SomeOtherDb.dbo.Customer does have a CustomerId column (which also seems likely from the names).

这也可能意味着你没有用子查询做你想用它做什么 - 如果子查询中的表没有 CustomerId 列(看起来是这样，否则运行时不会出错子查询本身)，然后子查询选择并返回外部的 CustomerId，因为这是子查询中唯一的列，所以子查询没有用.

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.

这篇关于为什么不是“无效的列名 XYZ"?子查询错误；虽然列名不在子查询表中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！