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)