oracle intrans,【急救求解！】in改写成inner join数据不一致

原SQL

select count(*)

from (select r.TRANS_ID          AS transId,

r.TRANS_TYPE        AS transType,

r.FLOW_ID           AS flowId,

r.CREATE_TIME       AS createTime,

r.TRANS_STATE       AS transState,

r.CALLBACK_MSG_TYPE AS callbackMsgType,

r.CALLBACK_PARAM    AS callbackParam,

r.FOCE_EXE_FLAG     AS foceExeFlag,

r.TRANS_EXE_RESULT  AS transExeResult,

r.TRANS_ERROR_INFO  AS transErrorInfo,

0                   AS isHis

from id.RDC_PROC_TRANS r

where r.trans_id IN (select o1.trans_id

from id.RDC_PROC_TRANS_OPERATE o1

where o1.flow_id = 1379

UNION ALL

select h1.trans_id

from id.RDC_PROC_TRANS_OPERATE_HIS h1

where h1.flow_id = 1379)

UNION ALL

select h.TRANS_ID          AS transId,

h.TRANS_TYPE        AS transType,

h.FLOW_ID           AS flowId,

h.CREATE_TIME       AS createTime,

h.TRANS_STATE       AS transState,

h.CALLBACK_MSG_TYPE AS callbackMsgType,

h.CALLBACK_PARAM    AS callbackParam,

h.FOCE_EXE_FLAG     AS foceExeFlag,

h.TRANS_EXE_RESULT  AS transExeResult,

h.TRANS_ERROR_INFO  AS transErrorInfo,

1                   AS isHis

from id.RDC_PROC_TRANS_HIS h

where h.trans_id IN (select o2.trans_id

from id.RDC_PROC_TRANS_OPERATE o2

where o2.flow_id = 1379

UNION ALL

select h2.trans_id

from id.RDC_PROC_TRANS_OPERATE_HIS h2

where h2.flow_id = 1379)) as aaa

记录数是： 1196

优化的SQL

select count(*) from (

select r.TRANS_ID          AS transId,

r.TRANS_TYPE        AS transType,

r.FLOW_ID           AS flowId,

r.CREATE_TIME       AS createTime,

r.TRANS_STATE       AS transState,

r.CALLBACK_MSG_TYPE AS callbackMsgType,

r.CALLBACK_PARAM    AS callbackParam,

r.FOCE_EXE_FLAG     AS foceExeFlag,

r.TRANS_EXE_RESULT  AS transExeResult,

r.TRANS_ERROR_INFO  AS transErrorInfo,

0                   AS isHis

from id.rdc_proc_trans r

inner join (select o1.trans_id

from id.rdc_proc_trans_operate o1

where o1.flow_id = 1379

union all

select h1.trans_id

from id.rdc_proc_trans_operate_his h1

where h1.flow_id = 1379) as a

on r.trans_id = a.trans_id

union all

select h.TRANS_ID          AS transId,

h.TRANS_TYPE        AS transType,

h.FLOW_ID           AS flowId,

h.CREATE_TIME       AS createTime,

h.TRANS_STATE       AS transState,

h.CALLBACK_MSG_TYPE AS callbackMsgType,

h.CALLBACK_PARAM    AS callbackParam,

h.FOCE_EXE_FLAG     AS foceExeFlag,

h.TRANS_EXE_RESULT  AS transExeResult,

h.TRANS_ERROR_INFO  AS transErrorInfo,

1                   AS isHis

from id.rdc_proc_trans_his h

inner join (select o2.trans_id

from id.rdc_proc_trans_operate o2

where o2.flow_id = 1379

union all

select h2.trans_id

from id.rdc_proc_trans_operate_his h2

where h2.flow_id = 1379) as b

on h.trans_id = b.trans_id

order by createTime desc ) as aaaa

记录数是：4591

奇怪了，我也就是把in优化成inner join的写法，应该是没问题啊，怎么记录数还不一样了

我看了下，就是蓝色部分的记录数不一样

原SQL的蓝色标记部分得到的记录数是：  1197  非蓝色标记的记录数是： 0

优化SQL的蓝色标记部分得到的记录数是： 4595   非蓝色标记的记录数是：0

问题就出在蓝色标记部分了，可是我不觉得改写了写法上有问题啊

有谁知道咋回事