能否用UNION替代RIGHT OUTER JOIN实现Gender与StatVal全组合计数

背景说明 #

现有一段通过RIGHT OUTER JOIN实现的SQL查询，能够生成TestTable中Gender与非空StatVal的所有可能组合，并返回每个组合的对应计数（不存在的组合计数为0）。现在需要确认是否可以用UNION替换RIGHT OUTER JOIN来实现相同需求。

原RIGHT OUTER JOIN实现的查询 #

SELECT (CASE
         WHEN [A].[Gender] IS NULL THEN
          [B].[Gender]
         ELSE
          [A].[Gender]
       END) AS [Gender],
       (CASE
         WHEN [A].[StatVal] IS NULL THEN
          [B].[StatVal]
         ELSE
          [A].[StatVal]
       END) AS [StatVal],
       (CASE
         WHEN [A].[COUNT] IS NULL THEN
          0
         ELSE
          [A].[COUNT]
       END) AS [COUNT]
  FROM (SELECT [Gender], [StatVal], COUNT(*) AS [COUNT]
          FROM [TestTable]
         GROUP BY [Gender], [StatVal]) AS [A]
 RIGHT OUTER JOIN (SELECT [G].[Gender], [T].[StatVal]
                     FROM (SELECT DISTINCT [Gender] FROM [TestTable]) AS [G],
                          (SELECT DISTINCT [StatVal] FROM [TestTable]) AS [T]) AS [B]
    ON [A].[Gender] = [B].[Gender]
   AND [A].[StatVal] = [B].[StatVal]
 WHERE [B].[StatVal] <> ''
   AND [B].[Gender] <> ''

原查询输出结果 #

TestTable表结构及数据 #

解决方案：用UNION实现相同需求 #

完全可以用UNION替代RIGHT OUTER JOIN来实现这个需求，核心思路是把结果拆成两部分合并：

1. 第一部分：获取TestTable中实际存在的非空StatVal与Gender组合，并计算真实计数
2. 第二部分：找出所有不存在的非空StatVal与Gender组合，给这些组合赋值计数0
   最后将两部分结果合并，再按Gender和StatVal排序即可。

对应的SQL代码如下：

-- 第一部分：实际存在的非空组合及真实计数
SELECT 
    [Gender],
    [StatVal],
    COUNT(*) AS [COUNT]
FROM [TestTable]
WHERE [StatVal] <> '' AND [Gender] <> ''
GROUP BY [Gender], [StatVal]

UNION ALL

-- 第二部分：不存在的非空组合，计数设为0
SELECT 
    [G].[Gender],
    [T].[StatVal],
    0 AS [COUNT]
FROM 
    (SELECT DISTINCT [Gender] FROM [TestTable] WHERE [Gender] <> '') AS [G],
    (SELECT DISTINCT [StatVal] FROM [TestTable] WHERE [StatVal] <> '') AS [T]
WHERE NOT EXISTS (
    SELECT 1 
    FROM [TestTable]
    WHERE [TestTable].[Gender] = [G].[Gender] 
      AND [TestTable].[StatVal] = [T].[StatVal]
)

-- 按Gender和StatVal排序，和原查询结果顺序一致
ORDER BY [Gender], [StatVal]

代码说明 #

• 第一部分直接筛选出非空的Gender和StatVal组合，分组计算真实计数，逻辑和原查询里的子查询A一致
• 第二部分先生成所有非空Gender与非空StatVal的笛卡尔积，再用NOT EXISTS筛选出原表中不存在的组合，给这些组合赋值计数0
• 使用UNION ALL替代UNION，因为两部分结果不会有重复，效率更高；若需去重可换成UNION，但此处无必要
• 最后加ORDER BY保证输出顺序与原查询一致

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