SQL中CROSS JOIN UNNEST含NULL行数据保留问题求助

你可以用两种方式保留policy_array为NULL的行，无需创建中间表：

方法1：改用LEFT JOIN UNNEST #

把CROSS JOIN替换成LEFT JOIN，这样即使policy_array是NULL，原表的行也会被保留，对应的policy字段会显示NULL：

SELECT 
    p.policy,
    account_id,
    rejects,
    overturns,
    appeals,
    submits
FROM relevant_table
LEFT JOIN UNNEST(policy_array) AS p (policy) ON true
WHERE
    ...

方法2：用COALESCE处理NULL数组 #

如果倾向于保留CROSS JOIN写法，可以用COALESCE把NULL的policy_array转换成包含一个NULL元素的数组，让UNNEST生成一行NULL的policy：

SELECT 
    p.policy,
    account_id,
    rejects,
    overturns,
    appeals,
    submits
FROM relevant_table
CROSS JOIN UNNEST(COALESCE(policy_array, ARRAY[NULL::text])) AS p (policy)
WHERE
    ...

注意：这里的NULL::text要替换成policy字段实际的数据类型，比如字段是整数就写成NULL::int。

原查询过滤NULL行的原因 #

CROSS JOIN UNNEST会为数组的每个元素生成一行，但如果数组本身是NULL，UNNEST不会生成任何行，因此原表中policy_array为NULL的行就被自动排除了。上面两种方法都能规避这个问题，且无需额外中间表。

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