如何用UNION实现类JOIN表合并并筛选缺失字段的员工ID

数据表 #

Employees表 #

+-------------+----------+
| employee_id | name     |
+-------------+----------+
| 2           | Crew     |
| 4           | Haven    |
| 5           | Kristian |
+-------------+----------+

Salaries表 #

+-------------+--------+
| employee_id | salary |
+-------------+--------+
| 5           | 76071  |
| 1           | 22517  |
| 4           | 63539  |
+-------------+--------+

需求 #

1. 使用UNION操作实现类似全外连接的输出格式，得到每个employee_id对应的name和salary（无对应值则为NULL）
2. 从结果中筛选出name或salary为空的employee_id

现有尝试及问题 #

查询1 #

select * from 
(select employee_id, name, null as salary from employees 
union all 
select employee_id, null as name, salary from salaries) 
as emp 
where name is null or salary is null

结果（存在重复employee_id的多条记录，未合并）：

{"headers": ["employee_id", "name", "salary"], "values": [[2, "Crew", null], [4, "Haven", null], [5, "Kristian", null], [5, null, 76071], [1, null, 22517], [4, null, 63539]]}

查询2 #

select * from (select employee_id, name from employees union all select employee_id, salary from salaries) as emp

结果（字段混淆，无法区分name和salary）：

{"headers": ["employee_id", "name"], "values": [[2, "Crew"], [4, "Haven"], [5, "Kristian"], [5, "76071"], [1, "22517"], [4, "63539"]]}

正确实现方案 #

通过UNION ALL拼接+分组聚合的方式，先合并两个表的记录，再按employee_id分组合并非空值，最后筛选符合条件的记录：

SELECT 
    employee_id,
    MAX(name) AS name,
    MAX(salary) AS salary
FROM (
    -- 拼接两个表的记录，补全缺失字段为NULL
    SELECT employee_id, name, NULL AS salary FROM Employees
    UNION ALL
    SELECT employee_id, NULL AS name, salary FROM Salaries
) AS combined_records
-- 按employee_id分组，聚合合并非空值
GROUP BY employee_id
-- 筛选name或salary为空的记录
HAVING MAX(name) IS NULL OR MAX(salary) IS NULL;

执行结果 #

+-------------+-------+--------+
| employee_id | name  | salary |
+-------------+-------+--------+
| 2           | Crew  | NULL   |
| 1           | NULL  | 22517  |
+-------------+-------+--------+

逻辑说明 #

1. 内层UNION ALL：将两个表的记录拼接，为每个表补全缺失的字段（Employees补salary为NULL，Salaries补name为NULL），保留所有原始记录
2. 外层GROUP BY employee_id：按员工ID分组，用MAX()函数聚合（MAX()会忽略NULL值，自动取同一员工下的非空name/salary），实现类似JOIN的合并效果
3. HAVING子句：筛选出name或salary为空的分组，即符合需求的员工

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