PostgreSQL 16左连接查询结果矛盾：指定ID无返回行

原始查询语句 #

SELECT g.id              AS gateway_id,
       ga.id             AS account_id
FROM public.gateways AS g
         LEFT JOIN public.gateway_accounts AS ga ON (g.id = ga.gateway_id)
where gateway_id = 'a298cf64-af91-4bc4-bd14-126a2d551f93';

场景描述 #

public.gateways表共有3条数据，其中一条ID为a298cf64-af91-4bc4-bd14-126a2d551f93，但public.gateway_accounts表中无匹配该行的关联数据。

预期查询返回一条account_id为NULL的行，但实际无任何结果；若将WHERE子句改为不等于指定ID，会返回另外两行，符合预期；若移除WHERE子句，会返回全表数据，目标行account_id为NULL，与预期一致。三种结果集的关联不符合预期——前两种合并无法得到第三种。

表结构DDL #

CREATE TABLE gateways
(
    id             uuid    DEFAULT gen_random_uuid() NOT NULL
        CONSTRAINT gateways_pk
            PRIMARY KEY
);
CREATE TABLE gateway_accounts
(
    id             uuid    DEFAULT gen_random_uuid() NOT NULL
        CONSTRAINT gateway_accounts_pk
            PRIMARY KEY,
    gateway_id     uuid                              NOT NULL
);
-- yes, there is no foreign key

原因解析 #

核心问题是SQL的执行顺序规则：WHERE子句的执行优先级远高于SELECT子句的别名解析。你以为WHERE里的gateway_id是SELECT中定义的别名（对应g.id），但数据库会优先在FROM子句涉及的表中查找同名列——恰好gateway_accounts表存在gateway_id列，因此数据库会自动将WHERE条件解析为：

WHERE ga.gateway_id = 'a298cf64-af91-4bc4-bd14-126a2d551f93'

由于目标gateway在gateway_accounts中无匹配数据，LEFT JOIN后ga.gateway_id的值为NULL，而SQL中NULL = '任意值'的结果是UNKNOWN，WHERE子句会过滤掉所有结果为UNKNOWN的行，所以最终没有返回数据。

对应其他场景的解释 #

• 当WHERE子句改为不等于指定ID时，ga.gateway_id != 'xxx'对于另外两行（在gateway_accounts中有匹配数据）来说，ga.gateway_id有有效值，条件成立；而目标行的ga.gateway_id是NULL，NULL != 'xxx'结果仍为UNKNOWN，被过滤，因此只返回另外两行。
• 移除WHERE子句时，LEFT JOIN会保留所有gateways表的行，目标行的account_id自然为NULL，这是LEFT JOIN的正常行为。

解决方法 #

1. 明确指定列来源（推荐）：直接引用表的原始列，避免别名歧义：

SELECT g.id              AS gateway_id,
       ga.id             AS account_id
FROM public.gateways AS g
         LEFT JOIN public.gateway_accounts AS ga ON (g.id = ga.gateway_id)
WHERE g.id = 'a298cf64-af91-4bc4-bd14-126a2d551f93';

2. 使用子查询/CTE先定义别名：如果必须依赖别名过滤，可以先通过子查询生成带别名的结果集，再在外层过滤：

SELECT gateway_id, account_id
FROM (
    SELECT g.id AS gateway_id,
           ga.id AS account_id
    FROM public.gateways AS g
    LEFT JOIN public.gateway_accounts AS ga ON (g.id = ga.gateway_id)
) AS sub_query
WHERE sub_query.gateway_id = 'a298cf64-af91-4bc4-bd14-126a2d551f93';

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