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

最新推荐文章于 2022-06-03 21:58:20 发布
最新推荐文章于 2022-06-03 21:58:20 发布
阅读量426 收藏
点赞数
原文链接：https://yq.aliyun.com/articles/29724
版权

自然连接（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 查询时 没有给出正确的结果。。

csdn产品小助手
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
一个php连接sql-server的类.zip
07-11
分享一个php连接sql-server的类，喜欢的朋友可以参考下载。
SQL中 JOIN 的两种连接类型：内连接（自然连接、自连接、交叉连接）、外连接（左外连接、右外连接、全外连接）
heshihu2019的博客
11-29 1698
3、内连接要求相等的分量，不一定是公共属性，自然连接要求相等的分量必须是公共属性；自然外连接分为自然左外连接和自然右外连接.匹配条件也是由系统自动指定.在MySQL中，可通过求左外连接与右外连接的合集来实现全外连接。自然内连接其实就是内连接,这里的匹配条件是由系统自动指定.2、内连接不把重复的属性除去，自然连接要把重复的属性除去；4、内连接不把重复的属性除去，自然连接要把重复的属性除去。–自然内连接 natural inner join。1、自然连接一定是内连接，内连接不一定是自然连接；
MySQL系列之Natural Join用法
Nicky's blog
11-10 2万+
Natural join即自然连接，natural join等同于inner join或inner using，其作用是将两个表中具有相同名称的列进行匹配
sql中的各类join连接的区别（natural join/inner join/left join/right join/full join）
Work hard
09-03 9512
设有表A和B，它们都具有一系列的列属性（col_att） 1、自然连接 natural join select &amp;lt;选择列&amp;gt; from 表A natural join 表B 将表A和表B进行自然连接：在A和B所有共同属性上做等值连接，然后再投影去掉重复属性。 例如： select * from book natural join reader 2、内连接 inner ...
SQL99中的natural join 和 using
初心不忘、始终方得
06-03 1154
natural join 其实就是inner join 并自动补全了连接条件而已
SQL学习八-LEFT JOIN && RIGHT JOIN
最新发布
12-24
SQL学习八-LEFT JOIN && RIGHT JOIN
SQL学习七-JOIN中的SQL
12-24
SQL学习七-JOIN中的SQL
简单学习SQL的各种连接Join
01-19
SQL JOIN 子句用于把来自两个或多个表的行结合起来，基于这些表之间的共同字段。 最常见的 JOIN 类型：SQL INNER JOIN（简单的 JOIN）、SQL LEFT JOIN、SQL RIGHT JOIN、SQL FULL JOIN，其中前一种是内连接，后三种...
非常详细的SQL--JOIN之完全用法
09-11
非常详细的SQL--JOIN之完全用法
sql中inner join和natural join的区别
热门推荐
kelly的专栏
06-27 3万+
inner join是内连接,显示符合连接条件的记录语法如下:select select_list from table1 inner join tabl2 on table1.column1=table2.column1natural join是对两张表中字段名和数据类型都相同的字段进行等值连接，并返回符合条件的结果 。natural join是自然连接,自动对两个表按照同名的列进行内连接语法如
sql server执行natural join出现语法错误
qq_41734424的博客
05-24 2807
·sql server不支持natural join ·直接使用join或者inner join仍然出错，后面必须加on
9.3.2 自然连接（NATURAL JOIN）
我的博客
06-01 4934
9.3.2  自然连接（NATURAL JOIN） 自然连接（NATURAL JOIN）是一种特殊的等价连接，它将表中具有相同名称的列自动进行记录匹配。自然连接不必指定任何同等连接条件。图9.9给出了典型的自然连接示意图。    图9.9  自然连接 自然连接自动判断相同名称的列，而后形成匹配。缺点是，虽然可以指定查询结果
实例解释inner join 和 natural join 的区别
destiny_python的博客
08-13 1万+
查找所有已经分配部门的员工的last_name和first_name和部门号 用inner join解答： inner join是内连接,显示符合连接条件的记录 select e.last_name, e.first_name, d.dept_no from employees e inner join dept_emp d on e.emp_no = d.emp_no;用natural j
sql lfet join 一个空表
08-08
LEFT JOIN是一种连接操作，它从左表中选择所有的记录，并根据条件与右表进行匹配。如果右表中没有匹配的记录，左表中的记录仍然会被选择，并且右表的输出字段将为null。 如果要使用LEFT JOIN连接一个空表，可以使用以下查询语句： SELECT A.* FROM A LEFT JOIN B ON A.id = B.id WHERE B.id IS NULL 以上查询语句中，A是左表，B是空表。使用LEFT JOIN连接A和B，根据id字段进行匹配。如果没有在B中找到与A中的记录匹配的记录，则输出A中的记录。在这种情况下，由于B是空表，所以A中的所有记录都会被输出。 请注意，LEFT JOIN连接一个空表时，右表的输出字段将为null，因为没有匹配的记录。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值