Oracle子查询

1、子查询in 、any 、all

select * from emp e where e.emp_dep_id = (select d.dep_id from dep d where d.dep_name='市场部')
--匹配子查询结果的任意一个值
select * from emp e where e.emp_dep_id in (select d.dep_id from dep d where d.dep_name in('市场部','研发部'))
--只要符合子查询的任意一个
select * from emp e where e.emp_dep_id >any (select d.dep_id from dep d where d.dep_name in('市场部','研发部'))
--必须符合子查询的全部
select * from emp e where e.emp_dep_id >all (select d.dep_id from dep d where d.dep_name in('市场部','研发部'))

 2、使用exists 和not exists

select e.* from emp e where exists (select 1 from dep d where d.dep_name='市场部' and e.emp_dep_id=d.dep_id)
select e.* from emp e where not exists (select 1 from dep d where d.dep_name='市场部' and e.emp_dep_id=d.dep_id)