"from_item is not null"/"from_item is null"语义解析及查询结果疑问

问题场景 #

执行以下查询时，仅返回所有列均非空的一行；将WHERE子句改为where i is null时，返回空结果集，这与预期的互补结果不符：

select *
from jsonb_to_recordset('[{"a":1},{"a":2,"b":42}]') i(a int, b int)
where i is not null

核心语义规则 #

PostgreSQL对行表达式（即示例中的i，代表整条记录）的IS NULL/IS NOT NULL判断遵循严格规则：

• 行 IS NOT NULL：当且仅当该行的所有列都不为NULL时返回true；只要有一列是NULL，整个表达式就返回false。
• 行 IS NULL：当且仅当该行的所有列都为NULL时返回true；只要有一列不为NULL，整个表达式就返回false。

示例分析 #

针对你的查询：

1. 第一个JSON对象{"a":1}解析为行(1, NULL)，因b列未赋值为NULL，不满足“所有列非空”，被where i is not null过滤。
2. 第二个JSON对象{"a":2,"b":42}解析为行(2, 42)，所有列均非空，符合条件被保留。
3. 使用where i is null时，需要两行的所有列都是NULL，但两行都至少有a列不为NULL，因此返回空结果集。

外连接场景的通用解决方案 #

若需在外连接中检查是否存在匹配行（即使连接表的所有列都可为空），无需枚举列的通用方法是使用EXISTS子查询——它仅判断是否存在匹配行，不关心行的具体内容（包括全NULL的行）。

示例（左连接场景） #

-- 查询主表中存在匹配行的记录
SELECT m.*
FROM main_table m
WHERE EXISTS (
    SELECT 1 
    FROM other_table o 
    WHERE o.main_id = m.id -- 替换为你的实际连接条件
);

-- 在左连接结果中标记是否有匹配
SELECT 
    m.*,
    CASE WHEN EXISTS (
        SELECT 1 
        FROM other_table o 
        WHERE o.main_id = m.id
    ) THEN true ELSE false END AS has_match
FROM main_table m
LEFT JOIN other_table o ON m.id = o.main_id;

这种方法完全基于连接条件判断匹配存在性，不受连接表列的可空性限制，是最通用的解决方案。

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