MYSQL使用UNION ALL合并LEFT JOIN并保留独立列的实现问题

问题原因 #

UNION/UNION ALL要求前后两个查询的列数量、对应的数据类型完全一致，你当前的两个查询都只返回两列，合并后自然只会保留两列，无法拆分出独立的videorented和videorentals列。

修正后的查询语句 #

你可以直接使用下面的写法，输出结果和你给出的示例完全匹配：

-- 取所有历史租赁记录
SELECT c.accountID, pr.videoID AS videorented, NULL AS videorentals
FROM customer c
INNER JOIN previousrental pr ON c.accountID = pr.accountID
UNION ALL
-- 取所有当前租赁记录
SELECT c.accountID, NULL AS videorented, r.videoID AS videorentals
FROM customer c
INNER JOIN rental r ON c.accountID = r.accountID
UNION ALL
-- 补充没有任何租赁记录的客户
SELECT c.accountID, NULL AS videorented, NULL AS videorentals
FROM customer c
WHERE NOT EXISTS (
    SELECT 1 FROM previousrental pr WHERE pr.accountID = c.accountID
) AND NOT EXISTS (
    SELECT 1 FROM rental r WHERE r.accountID = c.accountID
);

逻辑说明 #

1. 两个租赁查询分支都统一输出3列，满足UNION的列对齐要求，历史租赁分支只填充videorented列，当前租赁分支只填充videorentals列，另一列固定为NULL
2. 租赁查询分支使用INNER JOIN替代LEFT JOIN，避免产生无对应视频ID的无效行
3. 最后单独补充无任何租赁记录的客户，保证所有客户都出现在结果中，同时不会产生多余的重复行
4. 用UNION ALL替代UNION，不需要额外去重，查询效率更高

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