Oracle中的join连接(内连接、自然连接、外连接、自连接以及+号作用)

除了使用逗号(,)对表进行连接外，oracle还支持使用join关键字进行连接，使用join连接的语法格式如下：

FROM join_table1 join_type  join_table2
[ON (join_condition)]

其中，join_table1和join_table2指出参与连接操作的表名；JOIN_TYPE指出连接类型，常用的连接包括内连接、自然连接、外连接和自连接；ON指出了连接条件。

为了更好的说明各种连接的区别，这里准备了两张表和一些数据，具体脚本如下：

--雇员表
create table employees (
  employee_id number(6),
  first_name varchar2(8),
  last_name varchar2(8),
  department_id number(6),
  job_id number(6)
);
insert into employees
  (employee_id, first_name, last_name, department_id, job_id)
values
  (1, 'wenbo', 'wang', 1, 1);
insert into employees
  (employee_id, first_name, last_name, department_id, job_id)
values
  (2, 'meng', 'wang', 2, 2);
insert into employees
  (employee_id, first_name, last_name, department_id, job_id)
values
  (3, 'feng', 'wang', 1, 2);
insert into employees
  (employee_id, first_name, last_name, department_id, job_id)
values
  (4, 'ming', 'liu', 4, 2);
--部门表
create table departments(
   department_id number(6),
   department_name varchar2(20)
);

insert into departments
  (department_id, department_name)
values
  (1, '研发部');
insert into departments
  (department_id, department_name)
values
  (2, '人事部');
insert into departments
  (department_id, department_name)
values
  (7, '财务部');

1.内连接

内连接是一种常用的多表查询，一般用关键字INNER JOIN。其中，INNER关键字可以省略。

简单的说，内连接就是使用(INNER)JOIN关键字指定用于连接的两个表，并使用ON关键字指定连接表的连接条件(即，相关比较操作)。

使用内连接的sql如下：

select em.employee_id, em.first_name, dep.department_name
  from employees em
 inner join departments dep
 on em.department_id = dep.department_id;

执行结果如下图：

提示：使用内连接也可以实现两个以上表的查询。

2.自然连接

自然连接和内连接的功能相似，在使用自然连接查询多个表时，oracle会将第一个表中的那些列与第二个表中具有相同名称的列进行连接。在自然连接中，用户不需要明确指定进行连接的列。

自然连接在实际的应用中很少，因为它有个限制条件，即连接的各个表之间必须具有相同名称的列，而这在实际应用中可能和应用的实际意义发生矛盾。

使用自然连接的sql如下：

--自然连接
select em.employee_id, em.first_name, dep.department_name
  from employees em natural
  join departments dep;

执行结果如下图：

可以看到，在当前数据的情况下，自然连接和内连接的执行结果是相同的，因为默认使用相同名称的列进行连接，即department_id。

3.外连接

在使用内连接进行多表查询时，返回的查询结果仅包含符合查询条件(WHERE搜索条件或HAVING条件)和连接条件的行。内连接消除了与另外一个表中的任何行不匹配的行，而外连接扩展了内连接的结果集(除了返回一部分或全部不匹配的行，这取决于外连接的种类)。

外连接分为左外连接(LEFT OUTER JOIN或LEFT JOIN)、右外连接(RIGHT OUTER JOUN或RIGHT JOIN)和全外连接(FULL OUTER JOIN 或FULL JOIN)三种。与内连接不同的是，外连接不只列出与连接条件相匹配的行，还列出左表(左外连接)、右表(右外连接)或两个表(全外连接)中所有符合搜索条件的数据行。

1）左外连接

进行左外连接时，查询结果集不仅包含根据连接条件相匹配的行，还包含了左表中所有满足条件(比如满足后面跟的where限制条件的数据)的行，而不论连接条件是否与右表匹配。

左外连接的sql如下：

--左外连接
select em.employee_id, em.first_name, dep.department_name
  from employees em
  left join departments dep
    on em.department_id = dep.department_id;

执行结果如下：

当然，在oracle中，左外连接还支持另一种写法，即加号连接，如下：

select em.employee_id, em.first_name, dep.department_name
  from employees em, departments dep
 where em.department_id = dep.department_id(+);

2)右外连接

进行右外连接时，查询结果集不仅会返回右表中所有满足连接条件的行，还返回了右表中所有满足限制条件的行(比如where限制条件)，而不论左表中的各行。

右外连接的sql如下：

--右外连接
select em.employee_id, em.first_name, dep.department_name
  from employees em
  right join departments dep
    on em.department_id = dep.department_id;

执行右外连接的结果如下：

在oracle中，右外连接还支持另一种写法，如下：

select em.employee_id, em.first_name, dep.department_name
  from employees em, departments dep
 where em.department_id(+) = dep.department_id;

3)完全外连接

还有一种外连接类型为完全外连接，完全外连接相当于同时执行一个左外连接和一个右外连接。完全外连接会返回所有满足连接条件的行。在执行完全外连接时，系统开销很大，因为oracle实际上会执行一个完整的左外连接和右外连接查询，然后再将结果集合并，并消除重复的记录行。

使用完全外连接的sql如下：

--全外连接
select em.employee_id, em.first_name, dep.department_name
  from employees em
  full join departments dep
    on em.department_id = dep.department_id;

执行结果如下图：

4.自连接

有时候，用户可能会拥有自引用式外键。自引用式外键意味着表中的一个列可以是该表主键的一个外键。自连接是在FROM子句中两次指定了同一个表，为了在其他子句中区分，分别为表指定了表别名。

PS：上述脚本放置在了百度云盘，可以用作参考–>join-test.sql。