这里的问题是您没有在子查询中使用 Table.Column 表示法，表 Order 子查询中没有列 ID 和 ID 真正意味着 Person.ID，而不是 [Order].ID>.这就是为什么我总是坚持在生产代码中为表使用别名.比较这两个查询:

The problem here is that you're not using Table.Column notation in your subquery, table Order doesn't have column ID and ID in subquery really means Person.ID, not [Order].ID. That's why I always insist on using aliases for tables in production code. Compare these two queries:

select * from Person WHERE id IN (SELECT ID FROM [Order]);

select * from Person as p WHERE p.id IN (SELECT o.ID FROM [Order] as o)

第一个会执行但会返回错误的结果，第二个会引发错误.这是因为子查询中可能会引用外部查询的列，因此在这种情况下，您可以在子查询中使用 Person 列.也许您想像这样使用查询:

The first one will execute but will return incorrect results, and the second one will raise an error. It's because the outer query's columns may be referenced in a subquery, so in this case you can use Person columns inside the subquery. Perhaps you wanted to use the query like this:

select * from Person WHERE pid IN (SELECT PID FROM [Order])

但你永远不知道 [Order] 表的架构何时发生变化，以及是否有人从 [Order] 中删除了列 PID那么您的查询将返回表 Person 中的所有行.因此，使用别名:

But you never know when the schema of the [Order] table changes, and if somebody drops the column PID from [Order] then your query will return all rows from the table Person. Therefore, use aliases:

select * from Person as P WHERE P.pid IN (SELECT O.PID FROM [Order] as O)

只是快速说明 - 这不是 SQL Server 特定的行为，它是标准的 SQL:

Just quick note - this is not SQL Server specific behaviour, it's standard SQL:

• SQL Server 演示
• PostgreSQL 演示
• MySQL 演示
• Oracle 演示