exists和not exists的应用
1.exists和not exists的本质是判断存在或不存在一个元组,所以判断是否存在的值都是 select *
2.用到exists或是not exists都用到了跨表查询,所以一定要附上连接条件
示例:
#15. 查询部门的部门号,其中不包括job_id是"ST_CLERK"的部门号 #正确写法 select distinct department_id from employees e2 where not exists ( select * from employees e where e.department_id = e2.department_id and job_id = 'ST_CLERK' ); #错误写法 select distinct department_id from departments d where not exists ( select * from employees e where e.department_id = d.department_id and job_id = 'ST_CLERK' );
从中我们可以得出,exists要保证两个连接的表是同一个表类型
示例2:
1 #16. 选择所有没有管理者的员工的last_name 2 select last_name 3 from employees e 4 where exists ( 5 select * 6 from employees e2 7 where e.employee_id = e2.employee_id and e2.manager_id is null 8 );