Oracle语法：内连接、外连接和全连接

发表于 更新于 分类于 ， 本文字数： 752 阅读时长 ≈ 3 分钟

Oracle中的连接可分为，内连接（inner join）、外连接（outer join）、全连接（full join），不光是Oracle，其他很多的数据库也都有这3种连接查询方式。

演示数据准备

创建一个部门表（DEPT）和一个员工表（EMPLOYEE）。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
create table DEPT
(
  DEPTID   NUMBER,
  DEPTNAME VARCHAR2(200)
);


create table EMPLOYEE
(
  EID    NUMBER,
  DEPTID NUMBER,
  ENAME  VARCHAR2(200)
);

两个表增加如下几条测试数据。

内连接（inner join）

内连接是最常用的查询方式，比如 select * from A inner join B on A.field1=B.field2，这个查询语句也可以写成select * from A,B where A.field1=B.field2这种方式，内连接的查询结果是两个表的交集部分。

1
select * from dept inner join employee on dept.deptid=employee.deptid;

外连接（outer join）

外连接（outer join）可分为左外连接left outer join和右外连接right outer join，其中outter可以省略，直接写成left join和right join。其中left join是结果集中保留全部前表的数据，right join是结果集中保留全部后表的数据。

左外连接（left join）

1
2
3
4
5
select * from dept left outer join employee on dept.deptid=employee.deptid;

--或者也可以写成如下形式
select * from dept, employee on dept.deptid=employee.deptid(+);

右外连接（right join）

1
2
select * from dept right outer join employee on dept.deptid=employee.deptid;

全连接（full join）

全连接的查询结果是两个表的并集，并且去除重复的部分。当 A 表 中有 10 条数据，B 表中有 3 条数据，其中通过关联条件关联有 1 条关联成功。那么，我们通过full join 进行关联将出现 10 + 3 - 1 条数据。 （A,B表数据的和减去了关联成功的重复记录就是最后的结果集，其最终效果结是将两张表的数据完全结合到了一起。）

1
select * from dept full join  employee on dept.deptid=employee.deptid;

full join using(…)

full join using(…) 中 using 里的字段其实就是关联条件中 on 的字段，只不过是用两个表中都要有 using 里的列名。并且结果列中，不需要指定是A表的列还是B表的列，如果结果集中A、B表的该列的结果都不为空，则取任意一个表的列，如果只有一个表中的列的结果不为空，则会取这个非空值。

下面给一个示例来进一步体会，先准备测试数据。

1
2
3
4
5
6
7
8
9
10
11
SELECT * FROM t_Data_Export_Temp;
delete from t_Data_Export_Temp;
create table t_Data_Export_Temp2 as select * from t_Data_Export_Temp;
insert into t_Data_Export_Temp(yf,khh) values (202401,'111');
insert into t_Data_Export_Temp(yf,khh) values (202401,'222');
insert into t_Data_Export_Temp(yf,khh) values (202402,'333');
insert into t_Data_Export_Temp2(yf,khxm) values (202407,'aaa');
insert into t_Data_Export_Temp2(yf,khxm) values (202408,'bbb');
insert into t_Data_Export_Temp2(yf,khxm) values (null,'xxx');
insert into t_Data_Export_Temp2(yf,khxm) values (202401,'yyy');

对 YF 字段使用 using 来匹配。

1
2
3
4
SELECT YF,A.KHH,B.KHXM
FROM t_Data_Export_Temp A 
FULL JOIN t_Data_Export_Temp2 B
USING (YF);

上面这个写法的等价写法为：

1
2
3
4
SELECT NVL(A.YF,B.YF) YF,A.KHH,B.KHXM
FROM t_Data_Export_Temp A 
FULL JOIN t_Data_Export_Temp2 B
ON A.YF=B.YF;

如果使用另外一个两表都为空的列来关联呢？

1
2
3
4
SELECT FLAG,A.YF,A.KHH,B.KHXM
FROM t_Data_Export_Temp A 
FULL JOIN t_Data_Export_Temp2 B
USING (FLAG);

结果还是一个并集：

------ 本文完 ------