欢迎光临
一个有态度、有温度的分享型博客

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

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址