解法一 where子查询和join 连接
1、查询部门经理的emp_no
select emp_no from dept_manager2、在员工关系表中查询不是部门经理的员工编号,用where筛选
select emp_no,dept_no from dept_emp where emp_no not in (select emp_no from dept_manager) and to_date='9999-01-01'3、2表与薪水表连接,查询结果
select a.dept_no,a.emp_no,b.salary from (select emp_no,dept_no from dept_emp where emp_no not in (select emp_no from dept_manager) and to_date='9999-01-01') as a join salaries as b on a.emp_no=b.emp_no解法二 left join 连接
将薪水表和员工关系表内连接,再与部门表左连接,筛选部门表中员工编号为空的员工信息,即为非部门经理的信息
select b.dept_no,a.emp_no,a.salary from salaries as a join dept_emp as b on a.emp_no=b.emp_no left join dept_manager as c on b.dept_no=c.dept_no and b.emp_no=c.emp_no where c.emp_no is null;