sql1、 select sum(a.fee)
FROM mid.mid_d_acct_charge a, mid.mid_d_user_flag b
WHERE a.acct_day = '20091018'
AND b.acct_day = '20091018'
AND a.user_no = b.user_no(+);
sql2、 select sum(a.fee)
FROM (select user_no,fee from mid.mid_d_acct_charge t where t.acct_day = '20091018') a,
(select user_no from mid.mid_d_user_flag t where t.acct_day = '20091018') b
WHERE a.user_no = b.user_no(+)以上两个sql语句执行结果不一样,sql2的返回值比sql1的返回值要大,在a表和b表上都有user_no的索引,请问这会是什么原因?
FROM mid.mid_d_acct_charge a, mid.mid_d_user_flag b
WHERE a.acct_day = '20091018'
AND b.acct_day = '20091018'
AND a.user_no = b.user_no(+);
sql2、 select sum(a.fee)
FROM (select user_no,fee from mid.mid_d_acct_charge t where t.acct_day = '20091018') a,
(select user_no from mid.mid_d_user_flag t where t.acct_day = '20091018') b
WHERE a.user_no = b.user_no(+)以上两个sql语句执行结果不一样,sql2的返回值比sql1的返回值要大,在a表和b表上都有user_no的索引,请问这会是什么原因?
FROM mid.mid_d_acct_charge a, mid.mid_d_user_flag b
WHERE a.acct_day = '20091018'
AND b.acct_day = '20091018'
AND a.user_no = b.user_no(+);
改为
sql1、 select sum(a.fee)
FROM mid.mid_d_acct_charge a, mid.mid_d_user_flag b
WHERE a.acct_day = '20091018'
AND b.acct_day = '20091018'(+)
AND a.user_no = b.user_no(+);
这个就和sql2一样了
FROM mid.mid_d_acct_charge a, mid.mid_d_user_flag b
WHERE a.acct_day = '20091018'
AND b.acct_day = '20091018'
AND a.user_no = b.user_no(+);
这个语句和下面这个语句的执行结果是一样的,这是什么原理呢?
sql1、 select sum(a.fee)
FROM mid.mid_d_acct_charge a, mid.mid_d_user_flag b
WHERE a.acct_day = '20091018'
AND b.acct_day = '20091018'
AND a.user_no = b.user_no;
FROM mid.mid_d_acct_charge a, mid.mid_d_user_flag b
WHERE a.acct_day = '20091018'
AND b.acct_day = '20091018'
AND a.user_no = b.user_no(+);
相当于
select sum(a.fee)
FROM mid.mid_d_acct_charge a left join mid.mid_d_user_flag b
on a.user_no = b.user_no
where a.acct_day = '20091018'
AND b.acct_day = '20091018'
要想得到和sql2相同的结果
改成
select sum(a.fee)
FROM mid.mid_d_acct_charge a left join mid.mid_d_user_flag b
on a.user_no = b.user_no
and a.acct_day = '20091018'
AND b.acct_day = '20091018'
select user_no from mid.mid_d_acct_charge a where a.acct_day = '20091018'
minus
select user_no from mid.mid_d_user_flag b where b.acct_day = '20091018'
select user_no from mid.mid_d_acct_charge a
where a.acct_day = '20091018'
and not exists(select 1 from mid.mid_d_user_flag where acct_day = '20091018'
and user_no=a.user_no)
select user_no from mid.mid_d_acct_charge a
where a.acct_day = '20091018'
and not exists(select 1 from mid.mid_d_user_flag where acct_day = '20091018'
and user_no=a.user_no)
table1
user_no acct_day fee
1 20091018 1
2 20091018 1table2
user_no acct_day
1 20091018第一句查询结果为1,因为满足条件的只有1条记录,在sum之前,join之后的结果为
a.user_no a.acct_day a.fee b.user_no b.acct_day
1 20091018 1 1 20091018
2 20091018 1 (null) (null) -- 该记录不符合b.acct_day条件
而第二句为2,因为2条记录都满足条件,oracle首先做子查询,得到结果
table1
user_no fee
1 1
2 1table2
user_no
1子查询join的结果为
a.user_no a.fee b.user_no
1 1 1
2 1 (null) -- 现在没有在acct_day上的条件了,所以记录被获取