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

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

1

2FROM join_table1 join_type join_table2

[ON (join_condition)]

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

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

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42--雇员表

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如下：

1

2

3

4select 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如下：

1

2

3

4

5--自然连接

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如下：

1

2

3

4

5--左外连接

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中，左外连接还支持另一种写法，即加号连接，如下：

1

2

3select em.employee_id, em.first_name, dep.department_name

from employees em, departments dep

where em.department_id = dep.department_id(+);

2)右外连接

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

右外连接的sql如下：

1

2

3

4

5--右外连接

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中，右外连接还支持另一种写法，如下：

1

2

3select em.employee_id, em.first_name, dep.department_name

from employees em, departments dep

where em.department_id(+) = dep.department_id;

3)完全外连接

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

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

1

2

3

4

5--全外连接

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。