exists

当id in(另一张表结果集时)

(高效)

select * from EMP(基础表)
where EMPNO>0      /*where 后面也可以直接跟exist*/
and EXISTS
(select 'X'  from DEPT where DEPT.DEPTNO=EMP.DEPTNO and LOC='MELB')

(低效)
select * from EMP(基础表)
where EMPNO>0 and DEPTNO IN (select DEPTNO form DEPT where LOC='MELB')

当state in('01','02')时非要使用exists

正常写法 SELECT g.* from t_group_order g where g.state in('04','05')
exists写法 

SELECT g.* from t_group_order g 
 where EXISTS (
            select dlist.state from (

                    (SELECT '03' as state
                            from DUAL
                        UNION
                        SELECT '04' as state from DUAL) 

      ) dlist WHERE dlist.state = g.state 
)

not exist

select DISTINCT name from Student  t where  not EXISTS(
   select 'x' from Student v where v.name=t.name and v.fenshu<80
)
具体场景详见如下连接