SQL IN 查询产生奇怪的结果 [英] SQL IN query produces strange result
问题描述
请看下面的表格结构:
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)
为什么下面的查询返回两个结果:
Why does the following query return two results:
select * from Person WHERE id IN (SELECT ID FROM [Order])
订单中不存在 ID.为什么上面的查询会产生结果?我希望它会出错，因为我不按顺序存在.
ID does not exist in Order. Why does the query above produce results? I would expect it to error because I'd does not exist in order.
推荐答案
这里的问题是您没有在子查询中使用 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 IN 查询产生奇怪的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
