LeetCode 1965.丢失信息的雇员

数据准备

drop table Employees;

Create table If Not Exists Employees (employee_id int, name varchar(30));
Create table If Not Exists Salaries (employee_id int, salary int);
Truncate table Employees;
insert into Employees (employee_id, name) values ('2', 'Crew');
insert into Employees (employee_id, name) values ('4', 'Haven');
insert into Employees (employee_id, name) values ('5', 'Kristian');
Truncate table Salaries;
insert into Salaries (employee_id, salary) values ('5', '76071');
insert into Salaries (employee_id, salary) values ('1', '22517');
insert into Salaries (employee_id, salary) values ('4', '63539');

需求

写出一个查询语句，找到所有 丢失信息 的雇员id。当满足下面一个条件时，就被认为是雇员的信息丢失：
雇员的 姓名 丢失了，或者
雇员的 薪水信息 丢失了，或者
返回这些雇员的id employee_id ， 从小到大排序 。

输入

输出

-- 拼接左外连接,判断右表为空的数据,取左表id
with t1 as (
    select s.employee_id
    from Salaries s left join Employees e
    on s.employee_id=e.employee_id
    where e.employee_id is null
    union all
    select e.employee_id
    from Employees e left join Salaries s
    on s.employee_id=e.employee_id
    where s.employee_id is null
)
select *
from t1
order by employee_id
;