left join的一个奇怪问题

Question

语句1：select count(*)
from v_gzjbwl_fund_amabzzpay a
left join v_gzjbwl_fund_amabzzbankroll b on (a.member_code = b.member_code and a.fund_billnum = b.fund_billnum
and (b.dd_type_settlement <> '承兑汇票' or b.dd_type_settlement <> '商业承兑汇票')
and b.fund_billnum is not null);语句2：
select count(*)
from v_gzjbwl_fund_amabzzpay a
left join v_gzjbwl_fund_amabzzbankroll b on (
 (b.dd_type_settlement <> '承兑汇票' or b.dd_type_settlement <> '商业承兑汇票') and
a.member_code = b.member_code and a.fund_billnum = b.fund_billnumand b.fund_billnum is not null);
结果分别为：
906040
822不知道为什么

Answer 1

应该是一样的，只是where 条件顺序

Answer 2

是否因为是where后面的条件and
a.member_code = b.member_code and a.fund_billnum = b.fund_billnum的作用

Answer 3

a.member_code = b.member_code and a.fund_billnum = b.fund_billnum
语句2这个加上括号应该就一样了。具体原因不懂。

Answer 4

msn:[email protected],我可以现场演示

Answer 5

用spool命令让大家看下你的执行过程就可以了,例:SQL> select count(1) from emp
  2  left join dept on (emp.deptno=dept.deptno and dept.deptno=10);  COUNT(1)                                                                      
----------                                                                      
        17                                                                      SQL> select count(1) from emp
  2  left join dept on (dept.deptno=10 and emp.deptno=dept.deptno);  COUNT(1)                                                                      
----------                                                                      
        17    

Answer 6

SQL> select count(*)
  2  from v_gzjbwl_fund_amabzzpay a
  3  left join v_gzjbwl_fund_amabzzbankroll b on (
  4  a.member_code = b.member_code and a.fund_billnum = b.fund_billnum
  5  and (b.dd_type_settlement <> '承兑汇票' or b.dd_type_settlement <> '商业承兑汇票')
  6  and b.fund_billnum is not null);
 
  COUNT(*)
----------
    906040
SQL> select count(*)
  2  from v_gzjbwl_fund_amabzzpay a
  3  left join v_gzjbwl_fund_amabzzbankroll b on (
  4   (b.dd_type_settlement <> '承兑汇票' or b.dd_type_settlement <> '商业承兑汇票') and
  5  a.member_code = b.member_code and a.fund_billnum = b.fund_billnum
  6  and b.fund_billnum is not null);
 
  COUNT(*)
----------
       822

Answer 7

我去帮你发帖,看下mssql的高手如何解释,呵呵

Answer 8

http://topic.csdn.net/u/20120807/14/0d9e9941-45cf-4ddd-b11b-e8b7a2c576ea.html
留意下他们的回帖,估计没结果,呵呵

Answer 9

declare @T table([a] int,[b] varchar(1))
insert @T
select 1,'a' union all
select 2,'a' union all
select 3,'a'declare @t1 table([a] int,[b] int)
insert @t1
select 2,1 union all
select 3,2 union all
select 4,3select * from @T a left join @t1 b on a.a=b.a and b.b>1select * from @T a left join @t1 b on a.a=b.a --结果都是3条记录，因为是left join ，在SQL SERVER中右表的条件在on后面是不影响数据行数的。
--在where 后面就会影响结果的行数了。
/*
下面是4个条件：条件3 和条件4都是针对b表的。
1： a.member_code = b.member_code 
2： a.fund_billnum = b.fund_billnum
3： (b.dd_type_settlement <> '承兑汇票' or b.dd_type_settlement <> '商业承兑汇票')
4：  b.fund_billnum is not null*/
--你的ORACLE是什么版本的？
--看看这个的结果是多少行？
select count(*)
from v_gzjbwl_fund_amabzzpay a left join v_gzjbwl_fund_amabzzbankroll b 
on a.member_code = b.member_code and a.fund_billnum = b.fund_billnum

Answer 10

不懂oracle  不妄自评论两外小妞你的是视图还是表

Answer 11

我遇到过一次，left join 与 （+）用法 查询的结果不一样。

Answer 12

感谢各位的回答，真不好意思，昨天台风没上班。
我再给下结果：
SQL> --语句1：select count(*)
SQL> select count(*) from v_gzjbwl_fund_amabzzpay a
  2  left join v_gzjbwl_fund_amabzzbankroll b on (a.member_code = b.member_code and a.fund_billnum = b.fund_billnum
  3  and (b.dd_type_settlement <> '承兑汇票' or b.dd_type_settlement <> '商业承兑汇票')
  4  and b.fund_billnum is not null);
 
  COUNT(*)
----------
    907733
SQL> --语句2：
SQL> select count(*)
  2  from v_gzjbwl_fund_amabzzpay a
  3  left join v_gzjbwl_fund_amabzzbankroll b on (
  4   (b.dd_type_settlement <> '承兑汇票' or b.dd_type_settlement <> '商业承兑汇票') and
  5  a.member_code = b.member_code and a.fund_billnum = b.fund_billnum
  6  and b.fund_billnum is not null);
 
  COUNT(*)
----------
       823
SQL> select count(*)
  2  from v_gzjbwl_fund_amabzzpay a left join v_gzjbwl_fund_amabzzbankroll b
  3  on a.member_code = b.member_code and a.fund_billnum = b.fund_billnum;
 
  COUNT(*)
----------
       823其中 v_gzjbwl_fund_amabzzpay ，v_gzjbwl_fund_amabzzbankroll 都是视图 fund_billnum有为空的记录，dd_type_settlement这个不为空

Answer 13

将条件再换下，结果变成了28.。
SQL> select count(*)
  2  from v_gzjbwl_fund_amabzzpay a
  3  left join v_gzjbwl_fund_amabzzbankroll b on (
  4  a.member_code = b.member_code and a.fund_billnum = b.fund_billnum
  5  and b.fund_billnum is not null
  6  and (b.dd_type_settlement <> '承兑汇票' or b.dd_type_settlement <> '商业承兑汇票'));
 
  COUNT(*)
----------
        28

Answer 14

用minus 查询得到的记录集为空
SQL> 
SQL> select a.*
  2  from v_gzjbwl_fund_amabzzpay a
  3  left join v_gzjbwl_fund_amabzzbankroll b on (a.member_code = b.member_code and a.fund_billnum = b.fund_billnum
  4  and (b.dd_type_settlement <> '承兑汇票' or b.dd_type_settlement <> '商业承兑汇票')
  5  and b.fund_billnum is not null)
  6    minus
  7     select a.*
  8  from v_gzjbwl_fund_amabzzpay a
  9  left join v_gzjbwl_fund_amabzzbankroll b on (
 10   (b.dd_type_settlement <> '承兑汇票' or b.dd_type_settlement <> '商业承兑汇票')
 11  and a.member_code = b.member_code and a.fund_billnum = b.fund_billnum
 12  and b.fund_billnum is not null);
 
FUND_ID                                  FUND_OPCODE                    FUND_OPBATCH                             BILLTYPE_CODE                  FUND_DATE   FUND_OKDATE DD_TYPE_SETTLEMENT             DATAS_BALCORP DATAS_BALCORPNAME                                                                ACCEPTANCE_DATE ACCEPTANCE_MATURITY        ACCEPTANCE_BILLMONEY          ACCEPTANCE_BAILPER ACCEPTANCE_DEPT    ISSU_FLAG PAY_XMONEY PAY_YMOENY PAY_SMOENY  PAY_MOENY RECEIVE_XMONEY RECEIVE_YMOENY RECEIVE_SMOENY RECEIVE_MOENY DEPT_CODE ORG_CODE MEMBER_CODE FUND_REMARK                                                                      FUND_BILLNUM                   BANKROLL_DATE
---------------------------------------- ------------------------------ ---------------------------------------- ------------------------------ ----------- ----------- ------------------------------ ------------- -------------------------------------------------------------------------------- --------------- ------------------- --------------------------- --------------------------- --------------- ------------ ---------- ---------- ---------- ---------- -------------- -------------- -------------- ------------- --------- -------- ----------- -------------------------------------------------------------------------------- ------------------------------ -------------
 
SQL> 

Answer 15

 SQL> 
SQL> select count(*)
  2  from v_gzjbwl_fund_amabzzpay a
  3  left join v_gzjbwl_fund_amabzzbankroll b on a.member_code = b.member_code and a.fund_billnum = b.fund_billnum
  4  and (b.dd_type_settlement <> '承兑汇票' or b.dd_type_settlement <> '商业承兑汇票')
  5  and b.fund_billnum is not null   ;
 
  COUNT(*)
----------
    907733
SQL>   --minus
SQL>    select  count(*)
  2  from v_gzjbwl_fund_amabzzpay a
  3  left join v_gzjbwl_fund_amabzzbankroll b on
  4   (b.dd_type_settlement <> '承兑汇票' or b.dd_type_settlement <> '商业承兑汇票')
  5  and a.member_code = b.member_code and a.fund_billnum = b.fund_billnum
  6  and b.fund_billnum is not null;
 
  COUNT(*)
----------
       823
 
SQL> 结果一样

Answer 16

把count（*） 换成 count（distinct a.*） 试下   看下问题是不是在B表

Answer 17

--贴出楼主的执行计划，大家分析分析，从目前看是由于内层是视图，oracle采取某种优化原则，出问题了。Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 
Connected as gzjbkh
 
SQL> 
SQL> select * from table(dbms_xplan.display_cursor('1z8y0c7zawd90'));
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  1z8y0c7zawd90, child number 0
-------------------------------------
--语句1：select count(*) select /*sta 1*/ count(*) from v_gzjbwl_fund_amabzzpay a l
v_gzjbwl_fund_amabzzbankroll b on (a.member_code = b.member_code and a.fund_bill
b.fund_billnum and (b.dd_type_settlement <> '承兑汇票' or b.dd_type_settlement <> '商
b.fund_billnum is not null)
Plan hash value: 2069526587
--------------------------------------------------------------------------------
| Id  | Operation                    | Name                 | Rows  | Bytes | Co
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                      |       |       |
|   1 |  SORT AGGREGATE              |                      |     1 |    74 |
|   2 |   NESTED LOOPS OUTER         |                      |   613 | 45362 |
|*  3 |    HASH JOIN RIGHT OUTER     |                      |   613 | 45362 |
|   4 |     TABLE ACCESS FULL        | FUND_ACCEPTANCE      |   567 | 10773 |
|*  5 |     HASH JOIN                |                      |   613 | 33715 |
|*  6 |      VIEW                    | index$_join$_009     |   544 | 15776 |
|*  7 |       HASH JOIN              |                      |       |       |
|   8 |        INLIST ITERATOR       |                      |       |       |
|*  9 |         INDEX RANGE SCAN     | IX_FUND_BANKROLL_1   |   544 | 15776 |
|  10 |        INDEX FAST FULL SCAN  | IX_FUND_BANKROLL     |   544 | 15776 |
|  11 |      TABLE ACCESS FULL       | FUND_BANKROLL_DETAIL |  2480 | 64480 |
|  12 |    VIEW                      |                      |     1 |       |
|* 13 |     FILTER                   |                      |       |       |
|* 14 |      HASH JOIN OUTER         |                      |     1 |    81 |
|* 15 |       HASH JOIN              |                      |     1 |    62 |
|* 16 |        VIEW                  | index$_join$_004     |   366 | 10614 |
|* 17 |         HASH JOIN            |                      |       |       |
|  18 |          INLIST ITERATOR     |                      |       |       |
|* 19 |           INDEX RANGE SCAN   | IX_FUND_BANKROLL_1   |   366 | 10614 |
|  20 |          INDEX FAST FULL SCAN| IX_FUND_BANKROLL     |   366 | 10614 |
|* 21 |        TABLE ACCESS FULL     | FUND_BANKROLL_DETAIL |     1 |    33 |
|  22 |       TABLE ACCESS FULL      | FUND_ACCEPTANCE      |   567 | 10773 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("B"."MEMBER_CODE"="C"."MEMBER_CODE" AND
              "B"."BANKROLL_DETAIL_BILLNUM"="C"."ACCEPTANCE_BILLNUM")
   5 - access("A"."BANKROLL_BILLCODE"="B"."BANKROLL_BILLCODE" AND
              "A"."MEMBER_CODE"="B"."MEMBER_CODE")
   6 - filter(("A"."BILLTYPE_CODE"='F004' OR "A"."BILLTYPE_CODE"='F005'))
   7 - access(ROWID=ROWID)
   9 - access(("A"."BILLTYPE_CODE"='F004' OR "A"."BILLTYPE_CODE"='F005'))
  13 - filter("B"."BANKROLL_DETAIL_BILLNUM" IS NOT NULL)
  14 - access("B"."MEMBER_CODE"="C"."MEMBER_CODE" AND
              "B"."BANKROLL_DETAIL_BILLNUM"="C"."ACCEPTANCE_BILLNUM")
  15 - access("A"."BANKROLL_BILLCODE"="B"."BANKROLL_BILLCODE" AND
              "A"."MEMBER_CODE"="B"."MEMBER_CODE")
  16 - filter("B"."BANKROLL_DETAIL_BILLNUM" IS NOT NULL)
  17 - access(ROWID=ROWID)
  19 - access(("A"."BILLTYPE_CODE"='F002' OR "A"."BILLTYPE_CODE"='F003'))
  21 - filter("B"."BANKROLL_DETAIL_BILLNUM" IS NOT NULL)
 
57 rows selected
SQL> select * from table(dbms_xplan.display_cursor('bjkfpps0pdrcw'));
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  bjkfpps0pdrcw, child number 0
-------------------------------------
--语句2： select /*sta 2*/ count(*) from v_gzjbwl_fund_amabzzpay a left join
v_gzjbwl_fund_amabzzbankroll b on (  (b.dd_type_settlement <> '承兑汇票' or b.dd_typ
'商业承兑汇票') and a.member_code = b.member_code and a.fund_billnum = b.fund_billnum
b.fund_billnum is not null)
Plan hash value: 1377215601
--------------------------------------------------------------------------------
| Id  | Operation                        | Name                 | Rows  | Bytes
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                      |       |
|   1 |  SORT AGGREGATE                  |                      |     1 |    74
|   2 |   NESTED LOOPS OUTER             |                      |   613 | 45362
|*  3 |    HASH JOIN RIGHT OUTER         |                      |   613 | 45362
|   4 |     TABLE ACCESS FULL            | FUND_ACCEPTANCE      |   567 | 10773
|*  5 |     HASH JOIN                    |                      |   613 | 33715
|*  6 |      VIEW                        | index$_join$_009     |   544 | 15776
|*  7 |       HASH JOIN                  |                      |       |
|   8 |        INLIST ITERATOR           |                      |       |
|*  9 |         INDEX RANGE SCAN         | IX_FUND_BANKROLL_1   |   544 | 15776
|  10 |        INDEX FAST FULL SCAN      | IX_FUND_BANKROLL     |   544 | 15776
|  11 |      TABLE ACCESS FULL           | FUND_BANKROLL_DETAIL |  2480 | 64480
|  12 |    VIEW                          |                      |     1 |
|* 13 |     HASH JOIN OUTER              |                      |     1 |    81
|  14 |      NESTED LOOPS                |                      |     1 |    62
|* 15 |       TABLE ACCESS FULL          | FUND_BANKROLL_DETAIL |     1 |    33
|* 16 |       TABLE ACCESS BY INDEX ROWID| FUND_BANKROLL        |     1 |    29
|* 17 |        INDEX UNIQUE SCAN         | IX_FUND_BANKROLL     |     1 |
|  18 |      TABLE ACCESS FULL           | FUND_ACCEPTANCE      |   567 | 10773
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("B"."MEMBER_CODE"="C"."MEMBER_CODE" AND
              "B"."BANKROLL_DETAIL_BILLNUM"="C"."ACCEPTANCE_BILLNUM")
   5 - access("A"."BANKROLL_BILLCODE"="B"."BANKROLL_BILLCODE" AND
              "A"."MEMBER_CODE"="B"."MEMBER_CODE")
   6 - filter(("A"."BILLTYPE_CODE"='F004' OR "A"."BILLTYPE_CODE"='F005'))
   7 - access(ROWID=ROWID)
   9 - access(("A"."BILLTYPE_CODE"='F004' OR "A"."BILLTYPE_CODE"='F005'))
  13 - access("B"."MEMBER_CODE"="C"."MEMBER_CODE" AND
              "B"."BANKROLL_DETAIL_BILLNUM"="C"."ACCEPTANCE_BILLNUM")
  15 - filter(("B"."BANKROLL_DETAIL_BILLNUM" IS NOT NULL AND
              "B"."BANKROLL_DETAIL_BILLNUM"="B"."BANKROLL_DETAIL_BILLNUM" AND
              ("B"."DD_TYPE_SETTLEMENT"<>'商业承兑汇票' OR "B"."DD_TYPE_SETTLEMENT"<>'
  16 - filter(("A"."BILLTYPE_CODE"='F002' OR "A"."BILLTYPE_CODE"='F003'))
  17 - access("A"."MEMBER_CODE"="B"."MEMBER_CODE" AND
              "A"."BANKROLL_BILLCODE"="B"."BANKROLL_BILLCODE")
       filter("A"."MEMBER_CODE"="A"."MEMBER_CODE")
 
53 rows selected
 

Answer 18

按上面的显示，把这句去掉，你的left join顺序无论是什么都应是正确的了(b.dd_type_settlement <> '承兑汇票' or b.dd_type_settlement <> '商业承兑汇票')

Answer 19

你试试把上面两个视图的结果用表来代替，即另建两个表create table a as select * from 视图1;
create table b as select * from 视图2;然后，上面的语句用这两个新表替换之前的视图，
看下之前你执行的语句结果是否还会不一样

Answer 20

这个我给楼主试验过了，只要把视图换成物理表，问题就没有了。
所以说是oracle根据什么规则生成的执行计划有问题了。

Answer 21

BenChiM888建过表的，查出来的结果是一样的

Answer 22

首先left join是把左边的表完整的显示出来，不管条件如何，也就是说正常情况下数据是一样的
但有这种可能，count(*)你这里计算的是b表的数据，你可以这样测试一下看看哪个数据对得上：select count(b.member_code),count(b.fund_billnum),count(b.dd_type_settlement)
from v_gzjbwl_fund_amabzzpay a
left join v_gzjbwl_fund_amabzzbankroll b on (
  (b.dd_type_settlement <> '承兑汇票' or b.dd_type_settlement <> '商业承兑汇票') and
a.member_code = b.member_code and a.fund_billnum = b.fund_billnum
and b.fund_billnum is not null);

Answer 23

SQL> select count(b.member_code),count(b.fund_billnum),count(b.dd_type_settlement)
  2  from v_gzjbwl_fund_amabzzpay a
  3  left join v_gzjbwl_fund_amabzzbankroll b on (
  4    (b.dd_type_settlement <> '承兑汇票' or b.dd_type_settlement <> '商业承兑汇票') and
  5  a.member_code = b.member_code and a.fund_billnum = b.fund_billnum
  6  and b.fund_billnum is not null);
 
COUNT(B.MEMBER_CODE) COUNT(B.FUND_BILLNUM) COUNT(B.DD_TYPE_SETTLEMENT)
-------------------- --------------------- ---------------------------
                  49                    49                          49
 
SQL> 

Answer 24

count(*)并不是统计哪个表的数据，而是统计的你所有可选出的数据的条数，*代表的是所有相关表的所有列
和这个没关系的例（故意關聯錯了字段）：SQL> select count(emp.deptno) "14倏",
  2   count(emp.comm) "為null排除",
  3   count(*) "14倏",count(dept.deptno) "關聯錯了，所以0倏"
  4  from emp
  5  left join dept on emp.empno=dept.deptno;      14倏 為null排除       14倏 關聯錯了，所以0倏                              
---------- ---------- ---------- -----------------                              
        14          4         14                 0  

Answer 25

解决方法  (b.dd_type_settlement <> '承兑汇票' or b.dd_type_settlement <> '商业承兑汇票')
改成nvl(b.dd_type_settlement,'') not in ('承兑汇票','商业承兑汇票') 

Answer 26

条件错误：
nvl(b.dd_type_settlement,'') not in ('承兑汇票','商业承兑汇票')  
等价于
(b.dd_type_settlement <> '承兑汇票' and b.dd_type_settlement <> '商业承兑汇票')呵呵 悲剧了。

Answer 27

按理说:
(b.dd_type_settlement <> '承兑汇票' or b.dd_type_settlement <> '商业承兑汇票')应该是没任何作用的
b.dd_type_settlement为承兑汇票时,则后者成立,不为承兑汇票时则前者成立,因此你这个写法应该和
(1=1)一样
b.dd_type_settlement类似上面

Answer 28

(b.dd_type_settlement <> '承兑汇票' or b.dd_type_settlement <> '商业承兑汇票')
以上这句话有显示的逻辑错误。数据会重叠的。