两个sql语句执行结果不一样？？？请教高手！！！！！！！

[smile_wu]

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的索引，请问这会是什么原因？

[超叔csdn]

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(+);
改为
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一样了

[smile_wu]

这样语法有问题啊

[shiyiwan]

AND b.acct_day(+) = '20091018'

[smile_wu]

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(+);
这个语句和下面这个语句的执行结果是一样的，这是什么原理呢？
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;

[hmily001]

这是因为b表的每一个user_no都在a表中有user_no与之对应！所以查询的结果是一样的

[duqiangcise]

标记一下

[小灰狼W]

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(+);
相当于
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'

[smile_wu]

我按下面语句查a表中有user_no不在b表中的
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'

[小灰狼W]

[Quote=引用 8 楼 smile_wu 的回复:]
我按下面语句查a表中有user_no不在b表中的
      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'
[/Quote]
也可以这么写
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)

[smile_wu]

a表及b表都是大表，请问这么写是不是效率高些？
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)

[小灰狼W]

应该是的
可以看下执行计划

[KingSunSha]

第一个查询中，虽然user_no使用了左连接（允许b表中不存在的user_no），但是b.acct_day = '20091018'条件否定了左联接，因为如果b表中user_no不存在的话，acct_day也自然为null，不可能满足b.acct_day上的条件。

举例
table1
user_no acct_day fee
1 20091018 1
2 20091018 1

table2
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 1

table2
user_no
1

子查询join的结果为
a.user_no a.fee b.user_no
1 1 1
2 1 (null) -- 现在没有在acct_day上的条件了，所以记录被获取