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

这篇文章主要讲解了“oracle表连接的方法和类型有哪些”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“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表连接的方法和类型有哪些这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是亿速云，小编将为大家推送更多相关知识点的文章，欢迎关注！