使用两次 left join

select t.last_name,t.first_name,y.dept_name from
(select e.last_name,e.first_name ,d.dept_no from employees e
 left join dept_emp d on  e.emp_no=d.emp_no and d.to_date='9999-01-01') t
left join departments y on y.dept_no=t.dept_no