请教大家,与下面left join等价的where语句:
select X.sfzmhm
from X left join A
on X.sfzmhm = A.sfzmhm
left join B
on X.sfzmhm = B.sfzmhm and B.id>=1
left join C
on C.sfzmhm = X.sfzmhm
left join D
on D.sfzmhm = X.sfzmhm and D.hm=B.id
left join E
on A.mc = E.bh
left join F
on F.sn=C.sn and F.lx=B.id
用(+)代替:
select X.sfzmhm
from X left join A
on X.sfzmhm = A.sfzmhm(+)
left join B
on X.sfzmhm = B.sfzmhm(+) and B.id(+)>=1
left join C
on C.sfzmhm = X.sfzmhm(+)
left join D
on D.sfzmhm = X.sfzmhm(+) and D.hm=B.id(+)
left join E
on A.mc = E.bh
left join F
on F.sn=C.sn and F.lx=B.id
------解决方案--------------------
+1,①多张表时建议用子查询,SQL结构会清晰很多。
②用(+)代替left outer join时要在每个关联字段都加上(+)标志否则当有NULL值时很容易产生错误数据。