in和exists

刚刚查询到，exists替换in的情况是用于两表关联查询的情况下，IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况
例如下列语句：Select * from tableA Where id in (Select ID From tableB) 可以被替换为
Select * from tableA Where exists(Select * From tableB Where tableB.ID=tableA.ID)
我们在使用in如删除操作中的单表条件时，无法用exists替换，例如以下假 的删除sql

UPDATE BAS_PRIORITY SET delete_flag = '0' WHERE pk_id IN
<foreach collection="list" item="item" index="index" open="(" separator="," close=")" >
'${item}'
</foreach>
UPDATE BAS_PRIORITY SET delete_flag = '0' WHERE pk_id IN ( '4' , '3' )
如果有多表查询时使用in，请更使用联接查询或者exists替代，这样会提高执行效率

exists的经典例子，医生所管病人中，一个病人6个月以内至少有一次(检验单，门诊，住院，影像)记录的病人

select count(1)
from PATIENTINFO p,(select distinct patientinfoid
from Tb_ris_report a
where (a.jysj between to_char((select add_months(sysdate, -6) from dual),
'yyyy-mm-dd hh24:mi:ss') and
to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss'))
union
select distinct patientinfoid
from Tb_lis_report b
where (b.jyrq between to_char((select add_months(sysdate, -6) from dual),
'yyyy-mm-dd hh24:mi:ss') and
to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss'))
union
select distinct patientinfoid
from TB_MZJZJL c
where (c.jzksrq between
to_char((select add_months(sysdate, -6) from dual), 'yyyymmdd') and
to_char(sysdate, 'yyyymmdd'))
union
select distinct patientinfoid
from TB_CYXJ d
where (d.rysj between
to_char((select add_months(sysdate, -6) from dual), 'yyyymmdd') and
to_char(sysdate, 'yyyymmdd'))
) m
where (p.userid = 1081 or p.yys = 1081)
and p.iscanceled != 0
and p.patientinfoid = m.patientinfoid