oracle表连接的方法和类型有哪些

共计 4330 个字符，预计需要花费 11 分钟才能阅读完成。

这篇文章主要讲解了“oracle 表连接的方法和类型有哪些”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着丸趣 TV 小编的思路慢慢深入，一起来研究和学习“oracle 表连接的方法和类型有哪些”吧！

一、表连接就是在多个表之间用连接条件连接在一起，当优化器在解析带有表连接的 sql 时，除了会根据 sql 的写法来决定表连接的类型，还要确定下面三种情况才能指定出执行计划

1、表连接顺序，不管几个表的连接，sql 在执行时，只能进行两表的连接，然后根据连接结果再进行下一个两表的连接，直至所有表都连接完成，连接的顺序有两层含义，一是两表连接时决定好谁是 outer table (驱动表）谁是 inner table( 被驱动表），二是在多表连接的情况下，先连接哪两张表

2、表连接方法，有四种，排序合并连接，嵌套循环连接，哈希连接，笛卡尔连接，优化器在解析 sql 时要决定采用哪种连接

3、访问单表的方法 优化器在表表连接时，还要决定如何去读取单表中的数据，比如用全表扫还是走索引，走索引的话该如何走索引，等等

二、表连接的类型

类型分为内链接和外连接，类型决定了表连接的结果，sql 的写法直接决定了类型

1、内链接: 连接结果只包含那些完全满足连接条件的记录，只要 sql 里没写外连接的关键字，那就是内链接, 内链接的三种写法，其中一是 oracle 专用的

SELECT T1.COL1,T1.COL2,T2.COL3 FROM T1,T2 WHERE T1.COL2=T2.COL2;

SELECT T1.COL1,T1.COL2,T2.COL3   FROM T1 JOIN T2 ON(T1.COL2=T2.COL2);

 SELECT T1.COL1,
COL2,T2.COL3   FROM T1 JOIN T2 USING(COL2);

标准 sql 中还有一种 natural join , 表示只用两个表所有的同名列一起作为连接列，不推荐使用，虽然会省去写连接列，但是会增加 sql 报错的风险

2、外连接，连接结果除了包含那些完全满足连接条件的记录，还包含所有驱动表中不满足连接条件的记录，外连接分三种，左连接（left outer join), 右连接（right outer join），全连接（full outer join)

左连接，左边为驱动表

SELECT T1.COL1,t1.COL2,T2.COL3   FROM T1,t2 where t1.col2=t2.col2(+);

SELECT T1.COL1,t1.COL2,T2.COL3   FROM T1 left outer join T2 on (t1.col2=t2.col2);

SELECT T1.COL1, COL2,T2.COL3   FROM T1 left outer join T2 USING(COL2);

右连接，右边为驱动表

SELECT T1.COL1,t2.COL2,T2.COL3   FROM T1,t2 where t1.col2(+)=t2.col2;

SELECT T1.COL1, COL2,T2.COL3   FROM T1 right outer join T2 USING(COL2);

SELECT T1.COL1,t2.COL2,T2.COL3  from t1 right join t2 on(t1.col2=t2.col2);

全连接，相当于左连接 union 右连接

SELECT T1.COL1,t1.COL2,T2.COL3  from t1 full join t2 on(t1.col2=t2.col2);

3、当连接除了连接条件外，还有其他的条件时

内链接下面俩个语句结果相同

SELECT T1.COL1,t1.COL2,T2.COL3  from t1 join  t2 on(t1.col2=t2.col2 and t1.col1=1);

SELECT T1.COL1,t1.COL2,T2.COL3  from t1 join  t2 on(t1.col2=t2.col2) where t1.col1=1;

外连接就会发生不同，所以在外连接中除了连接限制条件外，其他的限制条件所处的文本位置，会影响最后的结果

SELECT T1.COL1,t1.COL2,T2.COL3  from t1 left outer join  t2 on(t1.col2=t2.col2 and t1.col1=1);

SELECT T1.COL1,t1.COL2,T2.COL3  from t1 left join  t2 on(t1.col2=t2.col2) where t1.col1=1;

上面两条标准 sql 语句在 oracle 中用（+）来表示时，分别可以写成

SELECT T1.COL1,t1.COL2,T2.COL3   FROM T1,t2 where t1.col2=t2.col2(+) and t1.col1(+)=1;

SELECT T1.COL1,t1.COL2,T2.COL3   FROM T1,t2 where t1.col2=t2.col2(+) and t1.col1=1;

t1.col2(+)= 1 即可表示这个条件也在连接限制条件内。

上述第一条语句的执行计划会使用哈希外连接（hash join outer), 而第二条语句会使用哈希连接（hash join), 即它实际上时用等价的内链接来执行的

内链接适用的 natural join 同样可以用于外连接

三、表连接的方法

1、排序合并连接（sort merge join)

两个表各自排序后合并得到结果集

执行效率不如哈希连接，但是比哈希连接适用更大的范围，因为哈希连接通常只用于等值连接条件，排序合并连接可以使用不同的连接条件，比如 = = 等等。

并不适合在 OLAP，当然如果可以避免排序，也可以用在 OLAP，比如在两个表各自的连接列上存在索引

严格意义上排序合并连接并不存在驱动表的概念

2、嵌套循环连接（nested loops join)

两个表连接时依靠两层嵌套循环（外层循环和内层循环）来得到结果集

步骤

a、优化器根据规则决定驱动表和被驱动表，驱动表用于外层，被驱动表用于内层

b、根据谓语条件访问驱动表，得到结果集 1

c、遍历结果集 1 同时遍历被驱动表，即先取出结果集 1 中的一条记录，然后根据这条记录遍历被驱动表，找出相匹配的记录，然后再取出结果集 1 中的第二条记录，继续遍历被驱动表，直到结果集 1 所有记录取完，即结果集 1 中有多少条记录，就会遍历循环多少次被驱动表，就会有多少次内层循环

嵌套循环连接的关键点在于结果集 1 的数据量，数据量少效率就会高，同时这种连接有一个其他连接没有的优点：可以实现快速响应。即可以快速返回已经连接过，且满足连接条件的记录，而不用等所有的连接全部完成

oracle 11g 中加入了向量 I /O（vector I/O) 提高嵌套循环的性能

3、哈希连接（hash join)

两个表连接时通过哈希运算来得到结果集

_hash_join_enabled 参数 默认为 true，启用 hash_join

alter session set _hash_join_enabled =true;

alter session set _hash_join_enabled =false;

use_hash hint 优先级比这个参数高

Si Bj   Sn Bn(代表磁盘中的 hash partition)

hash 的优缺点和适用场景

a、hash 不一定排序，或者说大部分都不会排序

b、驱动表的连接列可选择性（selectivity) 尽可能好, 因为这会影响 hash bucket 中的记录数

c、只适用于 CBO，且只适用于等值连接

d、适合小表和大表做连接且结果集较大的情况，小表连接列可选择性非常好的情况下，哈希连接的执行时间相当于大表做全表扫描的时间

e、两个表连接时，做完 hash 后，hash table 能完全在内存（PGA）中的话，哈希连接的执行效率会非常高

4、笛卡尔连接（cross join)

两个表在做连接时没有任何连接条件的表连接方法，实际上是一种特殊的合并连接，并不排序（MERGE JOIN CARTESIAN），T1 结果集为 m,T2 结果集为 n, 则笛卡尔连接后的记录数为 M *N

 select t1.col1,t2.col3 from t1,t2;

标准 sql : select t1.col1,t2.col3 from t1 cross join t2;

笛卡尔连接一般是不好的，往往是因为漏写了连接条件或者用了 ordered hint，而 sql 文本上相邻的俩个表有没有直接的关联条件造成的，还有可能是统计信息不准确造成。除非是刻意的，比如为了避免多次的大表全表扫描才会使用这种连接。

四、反连接（anti join)

结果集 1，结果集 2，满足 t1.col2=t2.col2 的都会被去除，只返回不满足连接条件的值

select * from t1 where col2 not in(select col2 from t2);

select * from t1 where col2  all(select col2 from t2);

select * from t1 where not exists(select 1 from t2 where col2=t1.col2);

当 t1,t2 没有 null 值时，上面三条语句结果相同

当有 null 时，结果会不一样

 not in 和   all 对 null 敏感，当他们后面的子查询或常量集合有 null，则整个 sql 的执行结果就是 null

not exists 对 null 不敏感，不影响执行结果

五、半连接（semi join)

t1,t2 连接时，驱动表 t1, 被驱动表 t2, 即使 t2 中满足连接条件 t1.col2=t2.col2 有多条记录，也只会返回第一条记录，即半连接时特殊的内连接，实际具有去重的作用。当子查询展开时，oracle 通常会把 where 后的条件 =any,exist,in 等查询转换为对应的半连接。

select * from t1 where col2 in(select col2 from t2);

 select * from t1 where col2= any(select col2 from t2);

select * from t1 where exists(select col2 from t2 where col2=t1.col2);   

六、星形连接（star join)

通常用于数据仓库，既不是连接类型，也不是连接方法，他是一个事实表（fact table) 与多个维度表（dimension table) 之间的连接，基本上事实表的外键列对应各维度表的主键列，事实表是张大表，后面章节会详细描述这个连接

感谢各位的阅读，以上就是“oracle 表连接的方法和类型有哪些”的内容了，经过本文的学习后，相信大家对 oracle 表连接的方法和类型有哪些这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是丸趣 TV，丸趣 TV 小编将为大家推送更多相关知识点的文章，欢迎关注！