一个同事再调试SQL的过程中遇到一个Oracle Not In使用的基本问题,附上SQL:
SELECT vci.cntr_n, voyage.vv_c, co.cntr_n FROM V_CNTR_INSTRUCTION vci ,VOYAGE voyage , cntr_operation co,BERTH_ALLOCATION ba WHERE vci.voyage_out_n = '077' AND voyage.ABBR_VOYAGE_OUT_N = vci.voyage_out_n AND vci.load_vessel_m ='O NEWZEALAND' AND voyage.ABBR_VESSEL_M = vci.load_vessel_m AND ba.VV_C = voyage.VV_C AND co.CNTR_N = vci.CNTR_N AND ba.BERTH_SEQ_N = 1 AND co.LOAD_DT IS NOT NULL AND co.CNTR_N NOT IN (SELECT job.CNTR_N FROM JOB_SEQ job WHERE job.VV_C = voyage.VV_C AND job.OPERATION_C in ('E','R','C','S')) |
他的目的很简单,就是查出来CNTR_N,并且值不在子查询中。结果子查询中确实没有CNTR_N的值,就是查询不出来。
调试过程中发现如果用CNTR_N NOT IN ('') 就不会显示任何值,但是子查询会返回来一些值,不是因为返回值为空影响的。
接着上百度搜索一下NOT IN的用法,发现一句提示:子查询【select distinct mgr from emp】结果中存在【NULL】值,导致查询结果失效,于是检查子查询的返回结果,发现确实有CNTR_N为NULL的情况。
问题解决,需要更改子查询的SQL,使返回值不能返回NULL值,更改如下:
SELECT vci.cntr_n, voyage.vv_c, co.cntr_n FROM V_CNTR_INSTRUCTION vci ,VOYAGE voyage , cntr_operation co,BERTH_ALLOCATION ba WHERE vci.voyage_out_n = '077' AND voyage.ABBR_VOYAGE_OUT_N = vci.voyage_out_n AND vci.load_vessel_m ='O NEWZEALAND' AND voyage.ABBR_VESSEL_M = vci.load_vessel_m AND ba.VV_C = voyage.VV_C AND co.CNTR_N = vci.CNTR_N AND ba.BERTH_SEQ_N = 1 AND co.LOAD_DT IS NOT NULL AND co.CNTR_N NOT IN (SELECT job.CNTR_N FROM JOB_SEQ job WHERE job.VV_C = voyage.VV_C AND job.OPERATION_C in ('E','R','C','S') AND job.CNTR_N IS NOT NULL) |
转载于:https://blog.51cto.com/sangao/1001803