select *
from SCONTAINERINFO con
where con.spci_packing_list_id in (select con.spci_packing_list_id
from SCONTAINERINFO con, SCONTAINERINFO con1, simportmanifest imp
where con1.spci_imp_bl_id = imp.siim_imp_bl_id
and con.spci_imp_bl_id = imp.siim_imp_bl_id
and con.spci_cnt_no = con1.spci_cnt_no
and con.spci_packing_list_id <> con1.spci_packing_list_id
and con.spci_lcl_flag = 'Y'
and con1.spci_lcl_flag = 'Y'
and con1.spci_is_danger_flag = 'Y'
and imp.siim_valid_flag = 'Y'
and imp.siim_sc_before_after_flag <> 0
and imp.siim_voyage_id = '6396');的执行速度比
select * from SCONTAINERINFO CON
WHERE EXISTS
(SELECT 0
FROM SCONTAINERINFO CON1, SIMPORTMANIFEST IMP
WHERE CON1.SPCI_IMP_BL_ID = IMP.SIIM_IMP_BL_ID
AND CON.SPCI_IMP_BL_ID = IMP.SIIM_IMP_BL_ID
AND CON.SPCI_CNT_NO = CON1.SPCI_CNT_NO
AND CON.SPCI_PACKING_LIST_ID <> CON1.SPCI_PACKING_LIST_ID
AND CON.SPCI_LCL_FLAG = 'Y'
AND CON1.SPCI_LCL_FLAG = 'Y'
AND CON1.SPCI_IS_DANGER_FLAG = 'Y'
AND IMP.SIIM_VALID_FLAG = 'Y'
AND IMP.SIIM_SC_BEFORE_AFTER_FLAG <> 0
AND IMP.SIIM_VOYAGE_ID = '6396');各位多给点意见,看看怎么解释这个现象
from SCONTAINERINFO con
where con.spci_packing_list_id in (select con.spci_packing_list_id
from SCONTAINERINFO con1, simportmanifest imp
where con1.spci_imp_bl_id = imp.siim_imp_bl_id
and con.spci_imp_bl_id = imp.siim_imp_bl_id
and con.spci_cnt_no = con1.spci_cnt_no
and con.spci_packing_list_id <> con1.spci_packing_list_id
and con.spci_lcl_flag = 'Y'
and con1.spci_lcl_flag = 'Y'
and con1.spci_is_danger_flag = 'Y'
and imp.siim_valid_flag = 'Y'
and imp.siim_sc_before_after_flag <> 0
and imp.siim_voyage_id = '6396');
执行这个语句的速度又是怎样的?查询出来的结果集多少记录?
这样说不大合适吧,用哪个更快取决于你的子查询
如果子查询得出的结果集记录较少,主查询中的表较大且又有索引时应该用in,反之如果外层的主查询记录较少,子查询中的表大,又有索引时使用exists。
其实我们区分in和exists主要是造成了驱动顺序的改变(这是性能变化的关键),如果是exists,那么以外层表为驱动表,先被访问,如果是IN,那么先执行子查询,所以我们会以驱动表的快速返回为目标,那么就会考虑到索引及结果集的关系了
另外IN时不对NULL进行处理