SQL IN 查询产生奇怪的结果

Question

请看下表结构:

CREATE TABLE Person (id int not null, PID INT NOT NULL, Name VARCHAR(50))
CREATE TABLE [Order] (OID INT NOT NULL, PID INT NOT NULL)

INSERT INTO Person VALUES (1,1,'Ian')
INSERT INTO Person VALUES (2,2,'Maria')
INSERT INTO [Order] values (1,1)

为什么以下查询返回两个结果:

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

订单中不存在 ID。为什么上面的查询会产生结果？我希望它出错，因为我不按顺序存在。

Answer 1

这种行为虽然不直观，但在 Microsoft 的知识库中有很好的定义:

KB #298674 : PRB: Subquery Resolves Names of Column to Outer Tables

从那篇文章:

To illustrate the behavior, use the following two table structures and query:

CREATE TABLE X1 (ColA INT, ColB INT)
CREATE TABLE X2 (ColC INT, ColD INT)
SELECT ColA FROM X1 WHERE ColA IN (Select ColB FROM X2)

The query returns a result where the column ColB is considered from table X1.

By qualifying the column name, the error message occurs as illustrated by the following query:

SELECT ColA FROM X1 WHERE ColA in (Select X2.ColB FROM X2)

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'ColB'.

人们多年来一直在提示这个问题，但微软不会解决它。毕竟，它符合标准，该标准基本上规定:

If you don't find column x in the current scope, traverse to the next outer scope, and so on, until you find a reference.

以下连接“错误”中的更多信息以及此行为是设计使然并且不会改变的多个官方确认(因此您必须更改您的 - 即 始终使用别名 ):

Connect #338468 : CTE Column Name resolution in Sub Query is not validated
Connect #735178 : T-SQL subquery not working in some cases when IN operator used
Connect #302281 : Non-existent column causes subquery to be ignored
Connect #772612 : Alias error not being reported when within an IN operator
Connect #265772 : Bug using sub select

在您的情况下，如果您使用比 ID、OID 和 PID 更有意义的名称，则发生这种“错误”的可能性要小得多。是否 Order.PID指向 Person.id或 Person.PID ?设计您的表格，以便人们无需询问您就可以找出关系。 A PersonID应该总是 PersonID ，无论它在模式中的哪个位置；与 OrderID 相同.为完全模棱两可的模式节省几个输入字符并不是一个好的代价。

你可以写一个 EXISTS代替条款:

... FROM dbo.Person AS p WHERE EXISTS 
(
  SELECT 1 FROM dbo.[Order] AS o
  WHERE o.PID = p.id -- or is it PID? See why it pays to be explicit?
);