能否用JOIN替代UNION ALL？改写负向测试SQL的需求

方案1：用OR合并关联与过滤条件（结果自动去重） #

如果业务场景允许合并后自动去重（和UNION效果一致），可以把两个查询的关联逻辑和过滤条件用OR组合成单条查询：

SELECT A.*
FROM A
JOIN B
  ON (A.COL1 = B.COL1 AND B.COL3 IS NULL)
  OR (A.COL2 = B.COL4 AND B.COL5 IS NULL);

逻辑说明 #

这条查询会匹配两种情况的记录：

• A表行与B表行通过COL1关联，且对应B行的COL3为空
• A表行与B表行通过COL2关联，且对应B行的COL5为空

注意：如果某条A表行同时满足两个条件，该方案只会返回一次，而原UNION ALL会返回两次。

方案2：用CROSS JOIN复刻UNION ALL的重复行为 #

如果需要完全保留原UNION ALL的结果（包括满足双条件的重复行），可以用CROSS JOIN结合类型标记来实现：

SELECT A.*
FROM A
CROSS JOIN (
  SELECT 1 AS join_type UNION ALL SELECT 2 AS join_type
) AS types
LEFT JOIN B
  ON (types.join_type = 1 AND A.COL1 = B.COL1 AND B.COL3 IS NULL)
  OR (types.join_type = 2 AND A.COL2 = B.COL4 AND B.COL5 IS NULL)
WHERE B.COL1 IS NOT NULL OR B.COL4 IS NOT NULL;

逻辑说明 #

1. 先构造一个包含两个标记值的临时表types，用来区分要执行的两种关联逻辑
2. 通过CROSS JOIN让A表的每一行都与两个标记组合，相当于把A表行复制一次
3. 根据标记值匹配对应的关联条件和过滤规则
4. 最后过滤掉未匹配到有效B行的组合，得到和原UNION ALL完全一致的结果

备选方案：用EXISTS子查询实现去重版本 #

如果更关注查询性能，也可以用EXISTS子查询实现去重效果：

SELECT A.*
FROM A
WHERE EXISTS (
  SELECT 1 FROM B
  WHERE A.COL1 = B.COL1 AND B.COL3 IS NULL
) OR EXISTS (
  SELECT 1 FROM B
  WHERE A.COL2 = B.COL4 AND B.COL5 IS NULL
);

这个版本和方案1的去重效果相同，但在多数数据库中，EXISTS会在找到第一条匹配记录后停止查找，可能比方案1的JOIN+OR更高效。

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