-- 先计算在职的员工的id，作为筛选条件
-- 再把所有的表链接上，where in（全部条件）
select b.dept_no ,a.emp_no,d.salary
from employees a
left join dept_emp b on a.emp_no=b.emp_no
left join dept_manager c on b.dept_no=c.dept_no
left join salaries d on a.emp_no=d.emp_no
AND b.to_date='9999-01-01'
where a.emp_no in
(select t1.emp_no from employees t1
left join dept_manager t2 on t1.emp_no = t2.emp_no
where t2.emp_no is null)