如何在PostgreSQL的INNER JOIN与UNION ALL查询中获取含NULL值的结果

1. 让标签为空时返回带NULL的行 #

原查询中标签列表为空时第一个SELECT返回0行，我们可以通过判断标签列表是否为空，在空值场景下生成一条全NULL的记录，非空时执行原查询逻辑：

-- 假设参数为tag_list text[]
SELECT
  m.timestamp,
  m.value,
  l.tag
FROM metrics m
INNER JOIN labels l ON m.id = l.metric_id
WHERE l.tag = ANY(tag_list)
-- 标签非空时执行上面的查询，空时返回NULL行
UNION ALL
SELECT
  NULL::timestamp,
  NULL::numeric, -- 根据你的value字段实际类型调整
  NULL::text
WHERE tag_list = '{}'::text[];

当tag_list为空时，第一个SELECT返回0行，第二个SELECT返回一条全NULL的记录；当tag_list非空时，第二个SELECT不返回任何行，只保留第一个查询的结果，完美区分两种场景。

2. 根据标签列表是否为空，仅返回对应部分的结果 #

如果不想保留UNION ALL的结构，而是动态选择查询分支，有两种可行方案：

纯SQL方案（无需存储过程） #

通过条件过滤让PostgreSQL只执行符合场景的子查询：

SELECT * FROM (
  -- 标签非空时的查询逻辑
  SELECT
    m.timestamp,
    m.value,
    l.tag
  FROM metrics m
  INNER JOIN labels l ON m.id = l.metric_id
  WHERE l.tag = ANY(tag_list)
) AS non_empty_query
WHERE tag_list != '{}'::text[]

UNION ALL

SELECT * FROM (
  -- 标签为空时返回NULL行
  SELECT
    NULL::timestamp,
    NULL::numeric,
    NULL::text
) AS empty_query
WHERE tag_list = '{}'::text[];

PostgreSQL会根据tag_list的实际值自动跳过不符合条件的子查询，避免无用计算。

存储过程方案（逻辑更灵活） #

如果需要更复杂的分支逻辑，可以写一个PL/pgSQL存储过程：

CREATE OR REPLACE FUNCTION get_time_series(tag_list text[])
RETURNS TABLE(timestamp timestamp, value numeric, tag text) AS $$
BEGIN
  IF tag_list = '{}'::text[] THEN
    RETURN QUERY SELECT NULL::timestamp, NULL::numeric, NULL::text;
  ELSE
    RETURN QUERY
      SELECT m.timestamp, m.value, l.tag
      FROM metrics m
      INNER JOIN labels l ON m.id = l.metric_id
      WHERE l.tag = ANY(tag_list);
  END IF;
END;
$$ LANGUAGE plpgsql;

-- 调用示例
SELECT * FROM get_time_series('{}'::text[]);
SELECT * FROM get_time_series('{"cpu_usage", "memory_usage"}'::text[]);

3. 额外优化建议 #

• 如果labels表中存在重复标签记录，先去重再关联可以提升查询效率：

  SELECT
    m.timestamp,
    m.value,
    l.tag
  FROM metrics m
  INNER JOIN (SELECT DISTINCT metric_id, tag FROM labels WHERE tag = ANY(tag_list)) l
    ON m.id = l.metric_id;
  

• 为labels.tag和metrics.id建立索引，加速关联和过滤操作。

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