PostgreSQL IN语句 [英] Postgresql IN statement

问题描述

当配置文件表中的 user_id 列不存在时，为什么此查询删除所有用户？

Why is this query deleting all users when the user_id column does not exist at the profile table?

初始查询：

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;

运行初始查询返回

删除100

为什么会这样？

是否应该在出错时停止执行或返回false或null？

Shouldn't it have stopped the execution upon error or return false or null?

运行PostgreSQL Server 9.0

Running PostgreSQL Server 9.0

推荐答案

此行为对于ANSI是正确的

This behavior is correct per ANSI standards.

如果未限定的列名称未在内部范围中解析，则将考虑外部范围。因此，您实际上在执行无意识的关联子查询。

If the unqualified column name doesn't resolve in the inner scope then the outer scope will be considered. So effectively you are doing an unintentional correlated sub query.

只要表配置文件至少包含一行然后

As long as the table profile contains at least one row then

 FROM users
 WHERE user_id IN (
        SELECT user_id FROM profile
        )

最终将匹配个用户中的所有行（除了如果 users.user_id为NULL 如 WHERE NULL IN（NULL）都不为真）。为避免此可能的问题，您可以使用两个部件名称。

will end up matching all rows in users (except any where users.user_id IS NULL as WHERE NULL IN (NULL) does not evaluate to true). To avoid this possible issue you can use two part names.

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

会给出错误

列p.user_id不存在：

column p.user_id does not exist:

这篇关于PostgreSQL IN语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！