使用UNION联合category与sub_category表查询列名不符问题

需要从category表和sub_category表中查询数据，但使用UNION联合查询后，只能获取category表的列名，无法同时展示category和sub_category的列名。

当前查询结果 #

[
  { "category_id": 3, "category": "GARDEN" },
  { "category_id": 6, "category": "ART" },
  { "category_id": 4, "category": "CONSOLE" },
  { "category_id": 2, "category": "ELECTRONICS" },
  { "category_id": 4, "category": "ELECTRONICS" },
  { "category_id": 2, "category": "CONSOLE" },
  { "category_id": 5, "category": "CHAIR" },
  { "category_id": 6, "category": "COLORS" },
  { "category_id": 5, "category": "GARDEN" },
  { "category_id": 1, "category": "CONSOLE" },
  { "category_id": 3, "category": "CUT" },
  { "category_id": 1, "category": "ELECTRONICS" }
]

当前SQL代码 #

SELECT
    c.id AS category_id,
    c.category
FROM 
    category c

UNION

SELECT 
    sc.id AS sub_category_id,
    sc.sub_category
FROM 
    sub_category sc

预期查询结果 #

[
  { "category_id": 3, "category": "GARDEN" },
  { "category_id": 6, "category": "ART" },
  { "category_id": 4, "category": "CONSOLE" },
  { "category_id": 2, "category": "ELECTRONICS" },
  { "category_id": 4, "category": "ELECTRONICS" },
  { "category_id": 2, "category": "CONSOLE" },
  { "category_id": 5, "category": "CHAIR" },
  { "category_id": 6, "category": "COLORS" },
  { "sub_category_id": 5, "category": "GARDEN" },
  { "sub_category_id": 1, "category": "CONSOLE" },
  { "sub_category_id": 3, "category": "CUT" },
  { "sub_category_id": 1, "category": "ELECTRONICS" }
]

UNION要求联合的两个查询结果列数、数据类型必须一致，列名以第一个查询为准，直接定义不同列名会被覆盖。要实现预期结构，需让两个查询返回相同列集合，用NULL填充不存在的字段，再在应用层根据字段是否为NULL生成对应JSON结构：

修改后的SQL代码：

SELECT
    c.id AS category_id,
    NULL AS sub_category_id,
    c.category
FROM 
    category c
UNION
SELECT 
    NULL AS category_id,
    sc.id AS sub_category_id,
    sc.sub_category AS category
FROM 
    sub_category sc

说明 #

• 两个查询都返回category_id、sub_category_id、category三列，对应表没有的字段用NULL填充
• 来自category表的行sub_category_id为NULL，来自sub_category表的行category_id为NULL
• 应用层处理结果时，判断每行的category_id是否非空：
  • 非空则生成{category_id: 值, category: 值}的结构
  • 为空则生成{sub_category_id: 值, category: 值}的结构

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