【SQL 学习】表连接--natural join 的一个bug

自然连接（NATURAL JOIN）是一种特殊的等价连接，它将表中具有相同名称的列自动进行记录匹配。自然连接不必指定任何同等连接条件。这篇文章讲的一个关于natural join 的bug！（由 dingjun123 提示！）

SQL> conn store/yang
已连接。
SQL> create table a as select * from all_objects;
表已创建。
SQL> set timing on
SQL> create table b as select * from all_objects;
表已创建。

已用时间:  00: 00: 20.36
SQL> set autot on
SQL> set linesize 100
SQL> select count(*) from a natural join b;
  COUNT(*)                                                                                         
----------                                                                                         
         0         ---错误的结果！                                                                                
已用时间:  00: 00: 00.04

执行计划
----------------------------------------------------------
Plan hash value: 1397777030 
                                                                                                   
------------------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------               
|   0 | SELECT STATEMENT    |      |     1 |   316 |       |  1500   (1)| 00:00:18 |
|   1 |  SORT AGGREGATE     |      |     1 |   316 |       |            |          |
|*  2 |   HASH JOIN         |      |     1 |   316 |  8184K|  1500   (1)| 00:00:18 |
|   3 |    TABLE ACCESS FULL| A    | 49280 |  7603K|       |   281   (1)| 00:00:04 |
|   4 |    TABLE ACCESS FULL| B    | 66983 |    10M|       |   282   (1)| 00:00:04 |
------------------------------------------------------------------------------------
                                                                                                   
Predicate Information (identified by operation id):                                                
---------------------------------------------------                                                
                                                                                                   
   2 - access("A"."EDITION_NAME"="B"."EDITION_NAME" AND                                            
              "A"."NAMESPACE"="B"."NAMESPACE" AND "A"."SECONDARY"="B"."SECONDARY" AND
              "A"."GENERATED"="B"."GENERATED" AND "A"."TEMPORARY"="B"."TEMPORARY" AND
              "A"."STATUS"="B"."STATUS" AND "A"."TIMESTAMP"="B"."TIMESTAMP" AND
              "A"."LAST_DDL_TIME"="B"."LAST_DDL_TIME" AND "A"."CREATED"="B"."CREATED" AND
              "A"."OBJECT_TYPE"="B"."OBJECT_TYPE" AND
              "A"."DATA_OBJECT_ID"="B"."DATA_OBJECT_ID" AND
              "A"."OBJECT_ID"="B"."OBJECT_ID" AND "A"."SUBOBJECT_NAME"="B"."SUBOBJECT_NAME
              " AND "A"."OBJECT_NAME"="B"."OBJECT_NAME" AND "A"."OWNER"="B"."OWNER")
                                                                                                   
Note      
-----
   - dynamic sampling used for this statement                                                      

统计信息
------------------------------------------------------
       1801  recursive calls
          0  db block gets
       1407  consistent gets
          0  physical reads
          0  redo size
        418  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         17  sorts (memory)    
          0  sorts (disk)   
          1  rows processed

SQL> set autot off
SQL> truncate table a;
表被截断。
已用时间:  00: 00: 00.43
SQL> truncate table b;
表被截断。
已用时间:  00: 00: 00.04
SQL> drop table a;
表已删除。
已用时间:  00: 00: 00.57
SQL> drop table b;
表已删除。
已用时间:  00: 00: 00.06
SQL> create table a (id number ,name varchar2(10)) ;
表已创建。
已用时间:  00: 00: 00.10
SQL> insert into a values (1,'aa');
已创建 1 行。
已用时间:  00: 00: 00.00
SQL> insert into a values (2,'bb');
已创建 1 行。
已用时间:  00: 00: 00.00
SQL> create table b as select * from a ;
表已创建。
已用时间:  00: 00: 00.29
SQL> set autot on
SQL> select count(*) from a natural join b;

  COUNT(*)                                                                                         
----------                                                                                         
         2      ----结果是正确的 

 已用时间:  00: 00: 00.03

执行计划
----------------------------------------------------------                                         
Plan hash value: 1397777030                                                                        
                                                                                                   
----------------------------------------------------------------------------                       
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------                       
|   0 | SELECT STATEMENT    |      |     1 |    40 |     7  (15)| 00:00:01 |
|   1 |  SORT AGGREGATE     |      |     1 |    40 |            |          |
|*  2 |   HASH JOIN         |      |     1 |    40 |     7  (15)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| A    |     2 |    40 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| B    |     2 |    40 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------                       
Predicate Information (identified by operation id):
--------------------------------------------------
                                                                                                   
   2 - access("A"."NAME"="B"."NAME" AND "A"."ID"="B"."ID")
                                                                                                   
Note                         
-----            
   - dynamic sampling used for this statement

统计信息
----------------------------------------------------------
        103  recursive calls
          0  db block gets
         34  consistent gets
          5  physical reads
          0  redo size 
        419  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> truncate table a;
表被截断。
已用时间:  00: 00: 00.25
SQL> truncate table b;
表被截断。
已用时间:  00: 00: 00.01
SQL> drop table a;
表已删除。
已用时间:  00: 00: 00.28
SQL> drop table b;
表已删除。
已用时间:  00: 00: 00.06

SQL> create table b as select * from all_objects where rownum < 1000;
表已创建。
已用时间:  00: 00: 00.87
SQL> drop table a;
表已删除。
已用时间:  00: 00: 00.35
SQL> create table a  as select * from b; a和b 两个表是一样的。
表已创建。
已用时间:  00: 00: 00.25
SQL> select * from a
  2  minus
  3  select * from b;
未选定行
已用时间:  00: 00: 00.03

SQL> select count(*) from a;
  COUNT(*)                                                                                         
----------                                                                                         
       999                                                                                         
已用时间:  00: 00: 00.01
SQL> select count(*) from b;

  COUNT(*)                                                                                         
----------                                                                                         
       999                                                                                         
已用时间:  00: 00: 00.01

SQL> set autot on
SQL> select count(*) from a natural join b;

  COUNT(*)                                                                                         
----------                                                                                         
         0   ---应该返回999行，但是结果是0 显然这是一个bug                                                                                      
已用时间:  00: 00: 00.01
执行计划
----------------------------------------------------------
Plan hash value: 1397777030                                                                        
                                                                                                   
----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |   316 |    13   (8)| 00:00:01 |
|   1 |  SORT AGGREGATE     |      |     1 |   316 |            |          |
|*  2 |   HASH JOIN         |      |     1 |   316 |    13   (8)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| A    |   999 |   154K|     6   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| B    |   999 |   154K|     6   (0)| 00:00:01 |
----------------------------------------------------------------------------
                                                                                                   
Predicate Information (identified by operation id):
---------------------------------------------------
                                                                                                   
   2 - access("A"."EDITION_NAME"="B"."EDITION_NAME" AND
              "A"."NAMESPACE"="B"."NAMESPACE" AND "A"."SECONDARY"="B"."SECONDARY" AND
              "A"."GENERATED"="B"."GENERATED" AND "A"."TEMPORARY"="B"."TEMPORARY" AND
              "A"."STATUS"="B"."STATUS" AND "A"."TIMESTAMP"="B"."TIMESTAMP" AND
              "A"."LAST_DDL_TIME"="B"."LAST_DDL_TIME" AND "A"."CREATED"="B"."CREATED"
              AND "A"."OBJECT_TYPE"="B"."OBJECT_TYPE" AND
              "A"."DATA_OBJECT_ID"="B"."DATA_OBJECT_ID" AND
              "A"."OBJECT_ID"="B"."OBJECT_ID" AND 
              "A"."SUBOBJECT_NAME"="B"."SUBOBJECT_NAME" AND
              "A"."OBJECT_NAME"="B"."OBJECT_NAME" AND "A"."OWNER"="B"."OWNER")
Note                                                                                               
-----     
   - dynamic sampling used for this statement

统计信息
----------------------------------------------------------
        582  recursive calls
          0  db block gets
        168  consistent gets
          0  physical reads
          0  redo size
        418  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          6  sorts (memory)       
          0  sorts (disk)
          1  rows processed
SQL> conn scott/yang
已连接。
SQL> select ename ,dname from emp natural join dept;
ENAME      DNAME     
---------- -----------
CLARK      ACCOUNTING
KING       ACCOUNTING
MILLER     ACCOUNTING
JONES      RESEARCH
FORD       RESEARCH
ADAMS      RESEARCH
SMITH      RESEARCH                                                                                
SCOTT      RESEARCH
WARD       SALES
TURNER     SALES
ALLEN      SALES
JAMES      SALES
BLAKE      SALES
MARTIN     SALES
-------结果是正确的！

已选择14行。
执行计划
----------------------------------------------------------                                         
Plan hash value: 844388907                                                                         
                                                                                                   
----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------           
|   0 | SELECT STATEMENT             |         |    14 |   308 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN                  |         |    14 |   308 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    52 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |         |    14 |   126 |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL         | EMP     |    14 |   126 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------           
                                                                                                   
Predicate Information (identified by operation id):  
---------------------------------------------------
                                                                                                   
   4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
       filter("EMP"."DEPTNO"="DEPT"."DEPTNO")
统计信息
---------------------------------------------------
          0  recursive calls
          0  db block gets
         11  consistent gets
          0  physical reads 
          0  redo size  
        674  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client                                                     
          1  sorts (memory)
          0  sorts (disk)
         14  rows processed
---贴出执行计划，只是想看看 natural join 会走什么样的计划。
重点是这个对于all_objects 构造出的表使用natural join 查询时 没有给出正确的结果。。

时间： 2024-04-26 06:06:33

【SQL 学习】表连接--natural join 的一个bug的相关文章

【SQL】表连接七种方式

---交叉连接,即是笛卡儿乘积 是一种将一个表的所有数据与另一个表中的所有的数据进行组合的操作.SQL> select e.ename,d.dname   2  from emp e,dept d; Execution Plan ---------------------------------------------------------- Plan hash value: 3429684969 ------------------------------------------------

SQL的表连接

每天给自己扫盲,让自己变得越博学. 继续学习<程序员的SQL金典>,这回我们来看看表连接相关的内容.表连接的相关知识在实际的项目开发当中,使用非常广. 所谓表连接,就是通过关联多张表,从而检索出需要的数据.实际的项目,存在多张表的关联关系.不可能在一张表里面就能检索出所有数据.如果没有表连接的话,那么我们就需要非常多的操作.比如需要从A表找出限制性的条件来从B表中检索数据.不但需要分多表来操作,而且效率也不高.比如书中的例子: SELECT FId FROM T_Customer WHERE

SQL多表连接查询实例分析（详细图文）_MsSql

新建两张表: 表1:student  截图如下: 表2:course  截图如下: (此时这样建表只是为了演示连接SQL语句,当然实际开发中我们不会这样建表,实际开发中这两个表会有自己不同的主键.) 一.外连接外连接可分为:左连接.右连接.完全外连接. 1.左连接  left join 或 left outer join SQL语句:select * from student left join course on student.ID=course.ID执行结果: 左外连接包含left joi

sql 多表连接查询_MsSql

新建两张表: 表1:student  截图如下: 表2:course  截图如下: (此时这样建表只是为了演示连接SQL语句,当然实际开发中我们不会这样建表,实际开发中这两个表会有自己不同的主键.) 一.外连接外连接可分为:左连接.右连接.完全外连接. 1.左连接  left join 或 left outer join SQL语句:select * from student left join course on student.ID=course.ID执行结果: 左外连接包含left joi

SQL多表连接查询实例分析（详细图文）

新建两张表: 表1:student  截图如下: 表2:course  截图如下: (此时这样建表只是为了演示连接SQL语句,当然实际开发中我们不会这样建表,实际开发中这两个表会有自己不同的主键.) 一.外连接 外连接可分为:左连接.右连接.完全外连接. 1.左连接  left join 或 left outer join SQL语句:select * from student left join course on student.ID=course.ID 执行结果: 左外连接包含left j

sql 多表连接查询

新建两张表: 表1:student  截图如下: 表2:course  截图如下: (此时这样建表只是为了演示连接SQL语句,当然实际开发中我们不会这样建表,实际开发中这两个表会有自己不同的主键.) 一.外连接 外连接可分为:左连接.右连接.完全外连接. 1.左连接  left join 或 left outer join SQL语句:select * from student left join course on student.ID=course.ID 执行结果: 左外连接包含left j

sql表连接查询使用方法(sql多表连接查询)_MsSql

实际的项目,存在多张表的关联关系.不可能在一张表里面就能检索出所有数据.如果没有表连接的话,那么我们就需要非常多的操作.比如需要从A表找出限制性的条件来从B表中检索数据.不但需要分多表来操作,而且效率也不高.比如书中的例子: 复制代码 代码如下: SELECT FIdFROM T_CustomerWHERE FName='MIKE' 这个SQL语句返回2,也就是姓名为MIKE 的客户的FId值为2,这样就可以到T_Order中检索FCustomerId等于2 的记录: 复制代码 代码如下: SE

sql表连接查询使用方法(sql多表连接查询)

实际的项目,存在多张表的关联关系.不可能在一张表里面就能检索出所有数据.如果没有表连接的话,那么我们就需要非常多的操作.比如需要从A表找出限制性的条件来从B表中检索数据.不但需要分多表来操作,而且效率也不高.比如书中的例子:复制代码 代码如下:SELECT FIdFROM T_CustomerWHERE FName='MIKE' 这个SQL语句返回2,也就是姓名为MIKE 的客户的FId值为2,这样就可以到T_Order中检索FCustomerId等于2 的记录: 复制代码 代码如下:SELEC

【SQL】表连接 --半连接

半连接: 当一张表在另一张表找到匹配的记录之后,半连接(semi-jion)返回第一张表中的记录.与条件连接相反,即使在右节点中找到几条匹配的记录,左节点的表也只会返回一条记录.另外,右节点的表一条记录也不会返回.半连接通常使用IN  或 EXISTS 作为连接条件.下面是一个例子: SQL> set linesize 999 SQL>  select d.deptno,d.dname,d.loc   2  from scott.dept d   3  where d.deptno IN  (