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             );

 

posted @ 2022-03-23 19:13  jue1e0  阅读(151)  评论(0)    收藏  举报