关于Oracle中left join简写写法在多条件时如何使用

[temptester]

select count(1)
  from t_im_moveissuebill a
  left join t_im_moveissuebillentry b
    on a.fid = b.fparentid
  left join t_bot_relation r
    on a.fid = r.fsrcobjectid
  left join t_im_moveinwarehsbillentry ib
    on r.fdestobjectid = ib.fparentid
   and b.fmaterialid = ib.fmaterialid
   and b.fassistpropertyid = ib.fassistpropertyid
 where to_char(A.Fbizdate, 'yyyy-mm-dd') >= '2013-08-01' and
  to_char(A.Fbizdate, 'yyyy-mm-dd') <= '2013-08-15' and exists
  (select 1 from TT_WHEREVALUES W where W.FID = B.FWAREHOUSEID)
    and NVL(A.FAUDITORID, ' ') <> ' '
    and NVL(A.FIsInitBill, 0) = 0

以上的写法我获得的行数结果是9457.
下边的写法获得的行数结果是9331.

select count(1)
  from t_im_moveissuebill         a,
       t_im_moveissuebillentry    b,
       t_bot_relation             r,
       t_im_moveinwarehsbillentry ib
 where a.fid = b.fparentid
   and to_char(A.Fbizdate, 'yyyy-mm-dd') >= '2013-08-01'
   and to_char(A.Fbizdate, 'yyyy-mm-dd') <= '2013-08-15'
   and exists
 (select 1 from TT_WHEREVALUES W where W.FID = B.FWAREHOUSEID)
   and NVL(A.FAUDITORID, ' ') <> ' '
   and NVL(A.FIsInitBill, 0) = 0
   and a.fid = r.fsrcobjectid(+)
   and r.fdestobjectid = ib.fparentid(+)
   and b.fmaterialid = ib.fmaterialid
   and b.fassistpropertyid = ib.fassistpropertyid

那么像上一段的多条件的left join在简写时，应该如何在where后边编写呢？？

[temptester]

这么蛋疼。就没有个人来解答么

[chenbest111]

没有数据结构，没有说明，谁看都蛋疼

[我刚睡醒]

问题好像出在t_im_moveinwarehsbillentry ib表 你第一个sql语句用left关联t_im_moveinwarehsbillentry时根本不起作用，查出的数据以t_im_moveissuebill a加where为主。第二个sql中就关联上了t_im_moveinwarehsbillentry表

[zhaolushuan]

这个表 t_im_moveissuebill 加上你的限定条件是你查出的条数 后面的那个 a.fid = b.fparentid你做了关联 等于加了限定条件 所以会少些

[gingerkang]

(+)= =(+)

[u010412956]

推荐第一种写法 第二种写法肯定不会等价，很多关联条件没有外连接起来

[幕幕]

简写可以写成：where table1.col(+) = table2.col;两条记录结果不一样也许是因为左连接保留了左边表的所有数据，虽然这些数据不符合要求

[无敌小二傻]

引用 楼主 u011759964 的回复:

select count(1)
  from t_im_moveissuebill a
  left join t_im_moveissuebillentry b
    on a.fid = b.fparentid
  left join t_bot_relation r
    on a.fid = r.fsrcobjectid
  left join t_im_moveinwarehsbillentry ib
    on r.fdestobjectid = ib.fparentid
   and b.fmaterialid = ib.fmaterialid
   and b.fassistpropertyid = ib.fassistpropertyid
 where to_char(A.Fbizdate, 'yyyy-mm-dd') >= '2013-08-01' and
  to_char(A.Fbizdate, 'yyyy-mm-dd') <= '2013-08-15' and exists
  (select 1 from TT_WHEREVALUES W where W.FID = B.FWAREHOUSEID)
    and NVL(A.FAUDITORID, ' ') <> ' '
    and NVL(A.FIsInitBill, 0) = 0

以上的写法我获得的行数结果是9457. 下边的写法获得的行数结果是9331.

select count(1)
  from t_im_moveissuebill         a,
       t_im_moveissuebillentry    b,
       t_bot_relation             r,
       t_im_moveinwarehsbillentry ib
 where a.fid = b.fparentid
   and to_char(A.Fbizdate, 'yyyy-mm-dd') >= '2013-08-01'
   and to_char(A.Fbizdate, 'yyyy-mm-dd') <= '2013-08-15'
   and exists
 (select 1 from TT_WHEREVALUES W where W.FID = B.FWAREHOUSEID)
   and NVL(A.FAUDITORID, ' ') <> ' '
   and NVL(A.FIsInitBill, 0) = 0
   and a.fid = r.fsrcobjectid(+)
   and r.fdestobjectid = ib.fparentid(+)
   and b.fmaterialid = ib.fmaterialid
   and b.fassistpropertyid = ib.fassistpropertyid

那么像上一段的多条件的left join在简写时，应该如何在where后边编写呢？？

select count(1) from t_im_moveissuebill a, t_im_moveissuebillentry b, t_bot_relation r, t_im_moveinwarehsbillentry ib where a.fid = b.fparentid(+) and to_char(A.Fbizdate, 'yyyy-mm-dd') >= '2013-08-01' and to_char(A.Fbizdate, 'yyyy-mm-dd') <= '2013-08-15' and exists (select 1 from TT_WHEREVALUES W where W.FID = B.FWAREHOUSEID) and NVL(A.FAUDITORID, ' ') <> ' ' and NVL(A.FIsInitBill, 0) = 0 and a.fid = r.fsrcobjectid(+) and r.fdestobjectid = ib.fparentid(+) and b.fmaterialid = ib.fmaterialid(+) and b.fassistpropertyid = ib.fassistpropertyid(+)