UNION联合查询中第二个子查询无返回值的原因排查

先看你编写的原始SQL语句：

SELECT DISTINCT [COS_JobTitle] AS 'Name'
FROM (
    SELECT [COS_BpsID] AS [COS_BpsID], [COS_JobTitle] AS [COS_JobTitle]
    FROM [CacheOrganizationStructure]
    WHERE [COS_AccountType] = 1

    UNION
        
    SELECT WFD_AttChoose3 AS WFD_AttChoose3, NULL
    FROM WFElements
    WHERE WFD_ID = 53584
) AS nnn

ORDER BY 'Name'

问题根源：列位置匹配与外层查询取值错误 #

• UNION的列匹配逻辑：UNION是按列的位置而非列名来合并两个子查询的结果。第一个子查询返回两列：第1列是COS_BpsID，第2列是COS_JobTitle；第二个子查询返回的两列是：第1列是WFD_AttChoose3，第2列是NULL。合并后的临时表nnn中，第1列是两个子查询第1列的合并值，第2列则是第一个子查询的COS_JobTitle和第二个子查询的NULL的集合。
• 外层查询的取值偏差：你外层查询指定选取[COS_JobTitle]（即临时表的第2列），但第二个子查询在第2列的位置填充的是NULL，所以自然无法获取到WFD_AttChoose3的值——因为它存在于临时表的第1列，你根本没选择这一列。

为什么去掉第二列后能正常工作？ #

当你移除两个子查询的第二列后，第一个子查询仅返回COS_BpsID，第二个子查询仅返回WFD_AttChoose3。此时UNION合并后的临时表只有一列，包含两个子查询的所有数据。外层查询选取这一列，自然能拿到所有预期结果。

修正方案（保留两列结构的同时获取目标值） #

如果需要保留原有的两列结构，需调整子查询的列位置，让目标字段处于同一列，并通过COALESCE取非空值：

SELECT DISTINCT COALESCE([JobTitle], [ExtraValue]) AS 'Name'
FROM (
    SELECT [COS_BpsID] AS [ID], [COS_JobTitle] AS [JobTitle], NULL AS [ExtraValue]
    FROM [CacheOrganizationStructure]
    WHERE [COS_AccountType] = 1

    UNION
        
    SELECT NULL AS [ID], NULL AS [JobTitle], WFD_AttChoose3 AS [ExtraValue]
    FROM WFElements
    WHERE WFD_ID = 53584
) AS nnn

ORDER BY 'Name'

或者更简洁的方式，将两个子查询的目标字段统一放到同一列位置：

SELECT DISTINCT [TargetValue] AS 'Name'
FROM (
    SELECT [COS_JobTitle] AS [TargetValue]
    FROM [CacheOrganizationStructure]
    WHERE [COS_AccountType] = 1

    UNION
        
    SELECT WFD_AttChoose3 AS [TargetValue]
    FROM WFElements
    WHERE WFD_ID = 53584
) AS nnn

ORDER BY 'Name'

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