有一个SQL语句,原来用的NOT IN操作,但是执行时间太长,花费了约7分钟
因为整个业务中使用了两次类似的操作,两次操作就约花费时间14分钟,加上其他操作共17分钟左右
需要做一些改进,我换成NOT EXISTS 后速度明显变快了,约3分钟左右
但最后对结果进行确认,数据略有差别,原来用NOT IN查询能出来的结果,用NOT EXISTS查询就没有出来
也就是说NOT EXISTS比NOT IN少了一些数据, 请问
NOT IN 和 NOT EXISTS 具体的区别在哪里? 什么原因导致NOT IN能出的数据在NOT EXISTS就不出?
SELECT XXXXX
from \
(select * from $1 \
where \
kessan_kbn=3 and (BR_CD,KOKYAKU_NO) in \
(select A.BR_CD,A.KOKYAKU_NO from $1 A,$1 B \
where A.kessan_kbn =2 and B.kessan_kbn =3 and A.BR_CD = B.BR_CD and A.KOKYAKU_NO = B.KOKYAKU_NO )) A, \
(select * from $1 \
where kessan_kbn=2 and (BR_CD,KOKYAKU_NO) in \
(select A.BR_CD,A.KOKYAKU_NO from $1 A,$1 B \
where A.kessan_kbn =2 and B.kessan_kbn =3 and A.BR_CD = B.BR_CD and A.KOKYAKU_NO = B.KOKYAKU_NO )) B \