PostgreSQL中带FROM与不带FROM的子查询差异及疑问

先给出测试用的表结构与初始化数据：

CREATE TABLE IF NOT EXISTS processing_client
(
    id serial NOT NULL,
    client_id int NOT NULL,
    updated_at timestamp NOT NULL
);

INSERT INTO processing_client(client_id, updated_at) VALUES (1, NOW()), (1, NOW());

两条待对比的UPDATE语句 #

SQL #1（带FROM的子查询） #

UPDATE processing_client
SET updated_at = now()
WHERE id IN (
    SELECT id 
    from processing_client -- 此处包含FROM
    WHERE client_id = 1
    LIMIT 1
);

这条语句始终影响1行，符合预期。

SQL #2（不带FROM的子查询） #

UPDATE processing_client
SET updated_at = now()
WHERE id IN (
    SELECT id -- 此处无FROM
    WHERE client_id = 1
    LIMIT 1
);

这条语句始终影响表中所有client_id=1的行（示例中为2行），和预期不符。

疑问解答 #

1. 为什么两条SQL结果不同？ #

SQL #1的子查询是独立的非关联子查询，它明确从processing_client表中查询符合client_id=1的1条id，主查询用这个固定的id匹配更新。

而SQL #2的子查询未指定FROM，它会直接引用主查询当前正在处理的行的字段，属于相关子查询，并非隐式使用主表作为查询来源。也就是说，子查询里的id和client_id都是主查询当前行的字段，不是从独立的查询结果中获取的。

2. 为什么SQL #2即使有LIMIT 1，影响行数却不是1？ #

SQL #2的子查询是和主查询逐行关联执行的：主查询遍历每一行时，都会执行一次这个子查询。子查询里的WHERE client_id=1是判断当前主查询行的client_id是否为1，SELECT id返回的是当前行的id，LIMIT 1在这里只是限制子查询返回1行（但本来就只返回当前行的1个值）。

对于表中client_id=1的每一行，子查询都会返回该行的id，主查询的id IN (当前行id)结果为真，所以所有client_id=1的行都会被更新；如果表中有client_id≠1的行，子查询的WHERE条件不成立，返回空集合，id IN ()为假，不会更新。

3. SQL #2的执行逻辑是什么？会被如何转换？ #

SQL #2的执行逻辑等价于逐行检查主表的每一行，最终效果和直接判断client_id一致，等价转换写法如下：

UPDATE processing_client
SET updated_at = now()
WHERE client_id = 1;

也可以用EXISTS更直观地体现关联逻辑：

UPDATE processing_client
SET updated_at = now()
WHERE EXISTS (
    SELECT 1
    WHERE client_id = 1 -- 此处client_id为当前主查询行的字段
    LIMIT 1
);

另外补充你提供的SQL #1的等价分步执行逻辑：

DECLARE _idList int;
-- 步骤1：先获取包含1个元素的id列表
SELECT id into _idList
from processing_client
WHERE client_id = 1
LIMIT 1;
-- 步骤2：在条件中使用该id列表
UPDATE processing_client
SET updated_at = now()
WHERE id = _idList;

内容的提问来源于stack exchange，提问作者huoshan12345