如何合并含部分可空列的两张表？BigQuery类型兼容问题求解

问题场景 #

当执行UNION ALL合并两个表时，若其中一个表的字段值全为NULL，BigQuery会默认将该字段类型推断为INT64，导致与另一个表的字段类型（如STRING）不兼容，触发报错。

示例SQL：

WITH
  a AS (
  SELECT
    "ax" AS x,
    "bla" AS foo),

  b AS (
  SELECT
    "bx" AS x,
    NULL AS foo)

SELECT * FROM a
UNION ALL
SELECT * FROM b

报错信息：

Column 2 in UNION ALL has incompatible types: STRING, INT64 at [14:1]

手动逐个将NULL转换为对应类型（如CAST(NULL AS STRING)）在复杂表结构（含多层嵌套结构体）下效率极低，需要无需逐个字段处理的自动适配方案。

解决方案 #

方法1：基于目标表结构构造空STRUCT继承类型 #

通过SELECT AS STRUCT * FROM 目标表 LIMIT 0获取目标表的完整字段结构，再合并包含NULL的行，所有NULL字段会自动匹配目标表的类型。

示例代码：

WITH
  a AS (
  SELECT
    "ax" AS x,
    "bla" AS foo,
    STRUCT(1 AS id, "test" AS name) AS nested_struct),

  b AS (
  -- 获取表a的空结构，继承所有字段类型
  SELECT x, foo, nested_struct FROM (SELECT AS STRUCT * FROM a LIMIT 0)
  WHERE FALSE
  -- 合并包含NULL的行，类型自动匹配
  UNION ALL
  SELECT
    "bx" AS x,
    NULL AS foo,
    NULL AS nested_struct)

SELECT * FROM a
UNION ALL
SELECT * FROM b

方法2：子查询引用目标表字段类型 #

针对单个字段，通过(SELECT 字段名 FROM 目标表 LIMIT 0)获取对应字段的类型，直接赋值给NULL字段，无需显式CAST。

示例代码：

WITH
  a AS (
  SELECT
    "ax" AS x,
    "bla" AS foo),

  b AS (
  SELECT
    "bx" AS x,
    -- 自动继承表a中foo字段的STRING类型
    (SELECT foo FROM a LIMIT 0) AS foo)

SELECT * FROM a
UNION ALL
SELECT * FROM b

方法3：预创建同结构表（适用于非CTE场景） #

如果是持久化表而非临时CTE，可先创建与目标表结构完全一致的表，再插入NULL数据：

-- 创建与表a结构一致的表b
CREATE TABLE b LIKE a;
-- 插入NULL数据
INSERT INTO b (x, foo) VALUES ("bx", NULL);
-- 执行UNION ALL
SELECT * FROM a UNION ALL SELECT * FROM b;

总结 #

以上方案无需逐个字段手动转换类型，尤其适合包含多层嵌套结构体的复杂表结构，能让NULL字段自动适配UNION ALL所需的目标类型，避免类型不兼容报错。

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