除了使用逗号(,)对表进行连接外,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。