无关联关系下如何查询两张不同表的合并记录并标识表来源

核心要求 #

• 两张表无任何关联关系，需要合并取出两张表的全部记录
• 区分数据来源，name字段需拼接表名前缀，格式为t1-原名称/t2-原名称
• 返回结果中，来自表A的记录pid字段为null，来自表B的记录uid字段为null

测试表结构 #

TABLE A
uid  name    email
1   test1   a@a.com
2   test2   b@a.com
3   test3   c@a.com
4   test4   d@a.com

TABLE B
pid  name    email
1   test1   123@a.com
2   test2   456@a.com
3   test3   789@a.com
4   test4   900@a.com

预期结果 #

uid    pid      name        email
1     null    t1-test1     a@a.com
2     null    t1-test2     b@a.com
3     null    t1-test3     c@a.com
4     null    t1-test4     d@a.com
null    1     t2-test1     123@a.com
null    2     t2-test2     456@a.com
null    3     t2-test3     789@a.com
null    4     t2-test4     900@a.com

原有写法问题 #

之前用逗号分隔两张表的写法属于交叉连接，会生成两张表的笛卡尔积，返回4*4=16条两表记录两两组合的重复数据，即使加distinct也无法得到预期的纵向合并结果：

SELECT  distinct table1.id, table1.name, table.email, table2.id, table1.name, table.email
FROM table1, table2

正确写法 #

使用UNION ALL对两个单表查询的结果做纵向拼接即可，这是无关联表合并行的标准方案：

SELECT
  uid,
  NULL AS pid,
  CONCAT('t1-', name) AS name,
  email
FROM tableA

UNION ALL

SELECT
  NULL AS uid,
  pid,
  CONCAT('t2-', name) AS name,
  email
FROM tableB;

注意事项 #

• UNION ALL上下两部分的查询必须保证列数一致、对应位置的列类型兼容，因此需要给每张表不存在的字段补NULL占位，对齐列结构
• 用CONCAT()函数完成表名前缀和原name字段的拼接，满足来源标记需求
• 不要用UNION替代UNION ALL：UNION会对合并后的结果做全局去重，这个场景下不存在重复记录，去重操作会带来不必要的性能开销

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