Oracle内连接、左外连接、右外连接、全外连接以及(+)号用法

Oracle内连接、左外连接、右外连接、全外连接以及(+)号用法

1、数据准备阶段   
　　创建数据库表,并插入数据,用于学习和测试.

drop table t_001;
drop table t_002;

CREATE TABLE t_001 (
    ID NUMBER NOT NULL,
    NAME VARCHAR2(8) NOT NULL,
    constraint t_001_id_pk primary key(id)
);
CREATE TABLE t_002 (
    ID NUMBER NOT NULL,
    NAME VARCHAR2(8) NOT NULL,
    constraint t_002_id_pk primary key(id)
);

INSERT INTO t_001 VALUES (1, 'CC');
INSERT INTO t_001 VALUES (2, 'HL');
INSERT INTO t_001 VALUES (3, 'WJ');
INSERT INTO t_001 VALUES (5, 'ZY');
INSERT INTO t_001 VALUES (7, 'XJ');

INSERT INTO t_002 VALUES (1, 'ZHB');
INSERT INTO t_002 VALUES (2, 'XDH');
INSERT INTO t_002 VALUES (3, 'WLZ');
INSERT INTO t_002 VALUES (4, 'HGL');
INSERT INTO t_002 VALUES (6, 'YSQ');

先进行简单的单表查询.

2、内连接（join/inner join）   

select * from t_001 t1 inner join t_002 t2 on t1.id=t2.id;

左表和右表都要做限制，仅显示满足on后面条件的数据。

select * from t_001 t1 join t_002 t2 on t1.id=t2.id;

select * from t_001 t1,t_002 t2 where t1.id=t2.id;

3、左外连接（left outer join/ left join）    

select * from t_001 t1 left join t_002 t2 on t1.id=t2.id;

 

left join是以左表的记录为基础的,示例中t_001可以看成左表,t_002可以看成右表,它的结果集是t_001表中的全部数据，再加上t_001表和t_002表匹配后的数据。换句话说,左表(t_001)的记录将会全部表示出来,而右表(t_002)只会显示符合搜索条件的记录。t_002表记录不足的地方均为null。

select * from t_001 t1,t_002 t2 where t1.id=t2.id(+);

用（+）来实现， 这个+号可以这样来理解： + 表示补充，即哪个表有加号，这个表就是匹配表。如果加号写在右表，左表就是全部显示，所以是左连接。

 

4、右外连接（right outer join/ right join）   

select * from t_001 t1 right join t_002 t2 on t1.id=t2.id;

和left join的结果刚好相反,是以右表(t_002)为基础的。它的结果集是t_002表所有记录，再加上t_001和t_002匹配后的数据。 t_001表记录不足的地方均为null。

select * from t_001 t1,t_002 t2 where t1.id(+)=t2.id;

 

　用（+）来实现， 这个+号可以这样来理解： + 表示补充，即哪个表有加号，这个表就是匹配表。如果加号写在左表，右表就是全部显示，所以是右连接。

 

5、全外连接（full outer join/ full join）   

select * from t_001 t1 full join t_002 t2 on t1.id=t2.id;

左表和右表都不做限制，所有的记录都显示，两表不足的地方均为null。

全外连接不支持（+）写法。

6、总结   

Oracle  连接(inner/outer join)包括以下：

• 内连接(两边的表都加限制)--inner join
• 左外连接(左边的表不加限制)--left [outer] join
• 右外连接(右边的表不加限制)--right [outer] join
• 全外连接(左右两表都不加限制)--full [outer] join

对应sql： 通常外联接省略outer关键字， 写成：left/right/full join.

　　　　　内连接也可省略关键字inner,直接写成join.

　　在左连接和右连接时都会以一张001表为基础表，该表的内容会全部显示，然后加上001表和002表匹配的内容。 如果001表的数据在002表中没有记录。 那么在相关联的结果集行中列显示为空值（null）。

内连接,可以使用"(+)",但是必须省略。即两张表均为"主表"，都不是匹配表。

而对于外连接， 也可以使用“(+) ”来表示。 关于外联接使用（+）的一些注意事项：

1. （+）操作符只能出现在where子句中，并且不能与outer join语法同时使用。
2.  当使用（+）操作符执行外连接时，如果在where子句中包含有多个条件，则必须在所有条件中都包含（+）操作符。
3. （+）操作符只适用于列，而不能用在表达式上。
4. （+）操作符不能与or和in操作符一起使用。
5. （+）操作符只能用于实现左外连接和右外连接，而不能用于实现完全外连接。