如何为PostgreSQL标量子查询指定非空默认值？

你遇到的场景是标量子查询无返回行时会返回NULL，想要替换成'UNSET'，其实COALESCE是可以解决这个问题的，之前的误解可能是没找对用法。下面提供几种可行的方案：

方案一：用COALESCE直接包裹标量子查询 #

这是最直接的方法，因为标量子查询无返回行时会被PostgreSQL转换为NULL，COALESCE可以捕获这个NULL并替换为指定值：

SELECT 
  col1,
  col2,
  COALESCE(
    (SELECT my_value FROM my_second_table WHERE my_first_table.value = ...),
    'UNSET'
  ) AS potentially_null_column
FROM my_first_table;

方案二：使用LEFT JOIN + COALESCE #

如果觉得子查询写法不够直观，可以改用左连接的方式，确保主表的每一行都能保留，无匹配时关联字段为NULL，再用COALESCE替换：

SELECT 
  ft.col1,
  ft.col2,
  COALESCE(st.my_value, 'UNSET') AS potentially_null_column
FROM my_first_table ft
LEFT JOIN my_second_table st ON ft.value = st.value;

注意：如果子查询原本可能返回多行，这种方法会产生重复行，需要结合DISTINCT或聚合函数处理，适合子查询仅返回0或1行的标量场景。

方案三：子查询中使用聚合函数确保返回行 #

如果担心子查询无返回行导致的“无结果”问题，可以在子查询里用聚合函数（比如MAX、MIN），这类函数即使没有匹配行也会返回NULL，而非无结果，再配合COALESCE：

SELECT 
  col1,
  col2,
  COALESCE(
    (SELECT MAX(my_value) FROM my_second_table WHERE my_first_table.value = ...),
    'UNSET'
  ) AS potentially_null_column
FROM my_first_table;

这种方法的好处是子查询始终返回一行，避免了标量子查询无返回行的场景，但如果子查询可能返回多行，聚合函数会合并结果，需要根据业务需求选择合适的聚合方式。

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