PostgreSQL IN语句

Question

为什么这个查询会删除所有用户，当profile表中不存在user_id列时？

初始查询：

DELETE FROM users
    WHERE user_id IN (
        SELECT user_id FROM profile
        )
    AND user_id != 35
    AND user_id != 6; 

更具体地说，

IN语句查询返回：

SELECT user_id FROM profile;

ERROR:  column "user_id" does not exist
LINE 1: SELECT user_id FROM profile;

运行初始查询会返回

DELETE 100

为什么会出现这种情况？

它不应该在错误发生时停止执行或返回false或null吗？

运行PostgreSQL Server 9.0

- Jimmy Kane

---

可能是重复的问题：SQL Server 2008管理工具未检查我的查询语法。 - Martin Smith

这是正确的行为。子查询引用外部查询的列是完全有效的。如果您想避免这种错误，请使用两个部分的名称。如果您使用DELETE FROM users WHERE user_id IN (SELECT p.user_id FROM profile p)，会出现无效的列名错误。 - Martin Smith

然而，如果表profile中不包含任何行，则它不会从users中删除任何行。 - Colin 't Hart

@MartinSmith，您能否友好地回答一下这个问题，这样我就可以关闭它了吗？ - Jimmy Kane

Answer 1

这个行为符合ANSI标准。

如果未经限定的列名在内部作用域中无法解析，则将考虑外部作用域。 因此，实际上您正在执行一个非故意的相关子查询。

只要表profile至少包含一行，则：

 FROM users
 WHERE user_id IN (
        SELECT user_id FROM profile
        )

如果使用users.user_id IS NULL作为查询条件，会匹配到users表中所有的行（除了其中user_id列为NULL的行），因为WHERE NULL IN (NULL)不会返回true。为了避免这种可能出现的问题，可以使用两部分命名。

DELETE FROM users
WHERE  user_id IN (SELECT p.user_id
                   FROM   profile p) 

会报错

列 p.user_id 不存在：

- Martin Smith

---

再次感谢您提供详细的描述。非常感激！ - Jimmy Kane

真是太疯狂了，我正在谷歌搜索postgresql的"in"语句行为，然后这篇文章出现了。恰好这就是我的问题所在。感谢你的帖子。 - Ben Gripka