Oracle表连接

表连接

SQL准备：

--创建A表
create table TestA(
id number not null primary key,
name varchar2(10)
);
--创建B表
create table TestB(
idno number not null primary key,
name varchar2(20),
sex varchar2(2)
);
--插入A表数据
insert into TestA values(1,'花花');
insert into TestA values(2,'草草');
insert into TestA values(3,'绿绿');
insert into TestA values(4,'红红');
insert into TestA values(5,'白白');
insert into TestA values(6,'黑黑');
--插入B表数据
insert into TestB values(1,'小花','女');
insert into TestB values(3,'小绿','男');
insert into TestB values(4,'小红','女');
insert into TestB values(7,'小黄','男');
insert into TestB values(9,'小紫','女');

1、内连接

1.1、等值连接

　　外连接：即使找不到满足条件的记录，另一张表也会输出。

　　内连接：查询满足条件的结果集，是相对于外连接来说的（条件的话由很多种可以等于、不等于、大于、小于等等）。

　　　　　　等值连接：其实是在内连接的基础之上条件为等于的连接，故等值连接是内连接的子集，同理不等值连接也是如此。

--表连接，你要先通过 a.id=b.idno（桥梁）把表连接上然后在加是值条件
select * from TestA a,TestB b where a.id=b.idno and a.id=1;
--等值连接是内连接的子集
select * from TestA inner join TestB on TestA.id=TestB.idno where TestA.id=1;

1.2、不等值连接

　　 除了等于之外的比较运算符比如<>、>、<、>=、<=、LIKE、IN、BETWEEN…AND。

--两表连接上后，查询id不等于1的记录
select * from TestA a,TestB b where a.id=b.idno and a.id<>1;
--inner join 的方式
select * from TestA inner join TestB on TestA.id=TestB.idno where id<>1;

1.3、自然连接

 　　自然连接是在两张表中寻找那些数据类型和列名都相同的字段，然后自动地将他们连接起来，并返回所有符合条件按的结果。

另外：

1.如果做自然连接的两个表的有多个字段都满足有相同名称个类型，那么他们会被作为自然连接的条件。

2.如果自然连接的两个表仅是字段名称相同，但数据类型不同，那么将会返回一个错误。

3.由于oracle中可以进行这种非常简单的natural join，我们在设计表时，应该尽量在不同表中具有相同含义的字段使用相同的名字和数据类型。以方便以后使用natural join

--新建用于测试自然连接的表C
create table TestC(
id number not null primary key,
cname varchar2(20),
csex varchar2(2)
);

--插入C表数据
insert into TestC values(1,'小花','女');
insert into TestC values(3,'小绿','男');
insert into TestC values(4,'小红','女');
insert into TestC values(7,'小黄','男');
insert into TestC values(9,'小紫','女');

--自然连接，自动连接名称类型相同的列
select * from TestA natural join TestC;
--等同于内连接的下面的写法
select * from TestA join TestC on TestA.id=TestC.id;
select * from TestA inner join TestC on TestA.id=TestC.id;

2、外连接

2.1左外连接（LEFT JOIN 或者 LEFT OUTER JOIN）

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

--TestA(左表)为基础表全部显示，右表(TestB)为匹配表无记录则显示为空
select * from TestA left join TestB on TestA.id=TestB.idno order by id ASC;
select * from TestA left outer join TestB on TestA.id=TestB.idno order by id ASC;

2.2右外连接（RIGHT JOIN 或者 RIGHT OUTER JOIN）

　　同LEFT JOIN的结果刚好相反,是以右表(TestB)为基础的。它的结果集是TestB表所有记录，再加上TestA和TestB匹配后的数据。 TestA表记录不足的地方均为NULL。

--TestB(右表)为基础表全部显示，左表(TestA)为匹配表无记录则显示为空
select * from TestA right join TestB on TestA.id=TestB.idno order by id ASC;
select * from TestA right outer join TestB on TestA.id=TestB.idno order by id ASC;

2.3全外连接（FULL JOIN 或者 FULL OUTER JOIN）

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

--全外连接，两个表的所有数据进行匹配，没有的显示为空
select * from TestA full join TestB on TestA.id=TestB.idno;
select * from TestA full outer join TestB on TestA.id=TestB.idno;

补充：外连结的另外一种写法

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

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

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

--加号写在右边即右边为匹配表，左边为基础表即左连接
select * from TestA,TestB where TestA.id=TestB.idno(+);
--加号写在左边即左边为匹配表，右边为基础表即右连接
select * from TestA,TestB where TestA.id(+)=TestB.idno;