如何使用UNION ALL运算符获取忽略NULL值的合并SQL查询结果

原SQL代码 #

select TRANSACTIONID, INFORMATION as "C_cntct", NULL as "C_nam"
from RETAILTRANSACTIONINFOCODETRANS as t
where INFOCODEID = 1009 and TRANSDATE = '2022-07-20'

UNION

select TRANSACTIONID, NULL, INFORMATION
from RETAILTRANSACTIONINFOCODETRANS as t
where INFOCODEID = 1010 and TRANSDATE = '2022-07-20'
group by TRANSACTIONID, INFORMATION
order by TRANSACTIONID, INFORMATION desc

当前查询结果 #

期望结果 #

解决方案 #

方法1：聚合函数分组

利用MAX()函数自动忽略NULL值的特性，将原UNION查询作为子查询，按TRANSACTIONID分组聚合，就能把同一交易ID下的非NULL值合并到一行：

SELECT 
    TRANSACTIONID,
    MAX(C_cntct) AS C_cntct,
    MAX(C_nam) AS C_nam
FROM (
    select TRANSACTIONID, INFORMATION as "C_cntct", NULL as "C_nam"
    from RETAILTRANSACTIONINFOCODETRANS as t
    where INFOCODEID = 1009 and TRANSDATE = '2022-07-20'

    UNION

    select TRANSACTIONID, NULL, INFORMATION
    from RETAILTRANSACTIONINFOCODETRANS as t
    where INFOCODEID = 1010 and TRANSDATE = '2022-07-20'
) AS temp
GROUP BY TRANSACTIONID
ORDER BY TRANSACTIONID;

方法2：自连接查询

直接通过TRANSACTIONID关联两个条件的数据集，一步到位得到合并结果：

SELECT 
    a.TRANSACTIONID,
    a.INFORMATION AS C_cntct,
    b.INFORMATION AS C_nam
FROM RETAILTRANSACTIONINFOCODETRANS AS a
JOIN RETAILTRANSACTIONINFOCODETRANS AS b 
    ON a.TRANSACTIONID = b.TRANSACTIONID
WHERE a.INFOCODEID = 1009 
  AND b.INFOCODEID = 1010 
  AND a.TRANSDATE = '2022-07-20'
  AND b.TRANSDATE = '2022-07-20'
ORDER BY a.TRANSACTIONID;

注意：如果存在某个交易ID只满足其中一个条件（比如只有联系方式没有姓名，或反之），自连接会过滤掉这类记录；而聚合函数方法会保留该记录，对应缺失字段显示NULL。可根据实际业务需求选择合适的方法。

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