oracle left join替代,这种情况的left join可以用inner join代替吗？

原始的语句是这样的：

select t.*

from (select rid

from (select rn, rid

from (select rownum rn, rid

from (select  rid

from (select distinct p.id,p.rowid as rid

from dcie.dcie_PERSON         p,

dcie.dcie_CONTACT_PERSON cp,

dcie.dcie_SITE_MEMBER    sm

where p.id = cp.person_id(+)

and p.id = sm.person_id(+)

and (p.UPDATE_TS >=

to_date('2011-03-11',

'yyyy-mm-dd') or

p.CREATE_TS >=

to_date('2011-03-11',

'yyyy-mm-dd') OR

cp.UPDATE_TS >=

to_date('2011-03-11',

'yyyy-mm-dd') or

cp.CREATE_TS >=

to_date('2011-03-11',

'yyyy-mm-dd') OR

sm.UPDATE_TS >=to_date('2011-03-11',

'yyyy-mm-dd') or

sm.CREATE_TS >=

to_date('2011-03-11',

'yyyy-mm-dd')

)

)

ORDER BY ID DESC)

where rownum <= 100))

where rn > 10) a,

dcie.dcie_PERSON t

where a.rid = t.rowid

order by id desc;

我觉得就这条语句来说，下面的left join条件

where p.id = cp.person_id(+)

and p.id = sm.person_id(+)

应该可以改成

where p.id = cp.person_id

and p.id = sm.person_id

也就是inner jion

测试语句如下:

with loj as (

select t.*

from (select rid

from (select rn, rid

from (select rownum rn, rid

from (select  rid

from (select distinct p.id,p.rowid as rid

from dcie.dcie_PERSON         p,

dcie.dcie_CONTACT_PERSON cp,

dcie.dcie_SITE_MEMBER    sm

where p.id = cp.person_id(+)

and p.id = sm.person_id(+)

and (p.UPDATE_TS >=

to_date('2011-03-11',

'yyyy-mm-dd') or

p.CREATE_TS >=

to_date('2011-03-11',

'yyyy-mm-dd') OR

cp.UPDATE_TS >=

to_date('2011-03-11',

'yyyy-mm-dd') or

cp.CREATE_TS >=

to_date('2011-03-11',

'yyyy-mm-dd') OR

sm.UPDATE_TS >=to_date('2011-03-11',

'yyyy-mm-dd') or

sm.CREATE_TS >=

to_date('2011-03-11',

'yyyy-mm-dd')

)

)

ORDER BY ID DESC)

where rownum <= 100))

where rn > 10) a,

dcie.dcie_PERSON t

where a.rid = t.rowid

order by id desc

) ,

ij as (

select t.*

from (select rid

from (select rn, rid

from (select rownum rn, rid

from (select  rid

from (select distinct p.id,p.rowid as rid

from dcie.dcie_PERSON         p,

dcie.dcie_CONTACT_PERSON cp,

dcie.dcie_SITE_MEMBER    sm

where p.id = cp.person_id

and p.id = sm.person_id

and (p.UPDATE_TS >=

to_date('2011-03-11',

'yyyy-mm-dd') or

p.CREATE_TS >=

to_date('2011-03-11',

'yyyy-mm-dd') OR

cp.UPDATE_TS >=

to_date('2011-03-11',

'yyyy-mm-dd') or

cp.CREATE_TS >=

to_date('2011-03-11',

'yyyy-mm-dd') OR

sm.UPDATE_TS >=to_date('2011-03-11',

'yyyy-mm-dd') or

sm.CREATE_TS >=

to_date('2011-03-11',

'yyyy-mm-dd')

)

)

ORDER BY ID DESC)

where rownum <= 100))

where rn > 10) a,

dcie.dcie_PERSON t

where a.rid = t.rowid

order by id desc

)

/* ij: Inner Join

loj: Left Outer Join

*/

select 'loj-ij',count(*) from (

select * from loj minus select * from ij

) aaa

union all

select 'ij-loj',count(*) from (

select * from ij minus select * from loj

) aaa

'LOJ-I   COUNT(*)

------ ----------

loj-ij         90

ij-loj         90

[本帖最后由 killkill_shadow 于 2011-4-24 23:20 编辑]