Oracle Database之数据表连接

Oracle Database之表连接

本篇文章将介绍有关表的连接条件和连接类型，这些知识是构建高级查询的必要条件。

1. 连接条件(Join condition)可分为两类：

等值连接(equijoin)：在连接中使用等于操作符(=)。

不等值连接(non-equijoin)：在连接中使用除等号（=）之外的其它操作符，例如<, >, between...等。

1.1 等值连接

--数据准备
create table dept
(
  dept_id number,
  dept_name varchar2(100)
);
insert into dept values(1,'Oracle Database Team');
insert into dept values(2,'Java Development Team');
insert into dept values(3,'.NET Development Team');
insert into dept values(4,'IT Facility');
insert into dept values(5,'HR');
insert into dept values(6,'iOS Developement Team');

create table emp
(
  emp_id number,
  emp_name varchar2(100),
  job varchar2(100),
  status varchar2(20) default 'Active',
  salary number(22,2),
  bonus number(22,2),
  dept_id number
);
insert into emp values(1,'david tian','database','Active',round(dbms_random.value(1000,20000)),round(dbms_random.value(1000,5000)),1);
insert into emp values(2,'black xie','database','Active',round(dbms_random.value(1000,20000)),round(dbms_random.value(1000,5000)),1);
insert into emp values(3,'josen zhang','.net','Active',round(dbms_random.value(1000,20000)),round(dbms_random.value(1000,5000)),3);
insert into emp values(4,'kaishen yang','java','Active',round(dbms_random.value(1000,20000)),round(dbms_random.value(1000,5000)),2);
insert into emp values(5,'fab yin','java','Active',round(dbms_random.value(1000,20000)),round(dbms_random.value(1000,5000)),2);
insert into emp values(6,'arvin he','java','Active',round(dbms_random.value(1000,20000)),round(dbms_random.value(1000,5000)),2);
insert into emp values(7,'alvin weng','IT','Active',round(dbms_random.value(1000,20000)),round(dbms_random.value(1000,5000)),4);
insert into emp values(8,'charles du','IT','Active',round(dbms_random.value(1000,20000)),round(dbms_random.value(1000,5000)),4);
insert into emp values(9,'ben liu','.net','Active',round(dbms_random.value(1000,20000)),round(dbms_random.value(1000,5000)),3);
insert into emp values(10,'chris zhang','.net','Active',round(dbms_random.value(1000,20000)),round(dbms_random.value(1000,5000)),3);
insert into emp values(11,'yoyo chen','HR','Active',round(dbms_random.value(1000,20000)),round(dbms_random.value(1000,5000)),5);
insert into emp values(12,'kitty wang','advisor','Active',round(dbms_random.value(1000,20000)),round(dbms_random.value(1000,5000)),null);

示例1.1：利用等值连接查询每个部门下的员工姓名、工作类型、状态、工资、奖金：

select d.dept_name,e.emp_name,e.job,e.status,e.salary,e.bonus
from emp e, dept d
where e.dept_id=d.dept_id
order by d.dept_name asc;

注：从结果可以看出，员工号为12的员工不属于任何部门，所以等值连接不会显示改行。

1.2  不等值连接

不等值连接在连接中使用除等于操作符之外的操作符，包括不等于(<>)、小于(<)、大于(>)、小于等于(<=)、大于等于(>=)、LIKE和BETWEEN。

示例1.2：使用不等值连接查询员工的工资和工资的等级：

--数据准备
create table salary_grades
(
salary_grade_id number,
low_salary number(22,2),
high_salary number(22,2)
);

insert into salary_grades values(1,1,2500);
insert into salary_grades values(2,2501,5000);
insert into salary_grades values(3,5001,7500);
insert into salary_grades values(4,7501,10000);
insert into salary_grades values(5,10001,15000);
insert into salary_grades values(6,15001,20000);

create table emp
(
emp_id number,
first_name varchar2(50),
last_name varchar2(50),
job varchar2(50),
salary number(22,2)
);

insert into emp values(1,'tian','david','database',round(dbms_random.value(1,20000)));
insert into emp values(2,'xie','black','database',round(dbms_random.value(1,20000)));
insert into emp values(3,'zhang','josen','.net',round(dbms_random.value(1,20000)));
insert into emp values(4,'yang','kaishen','java',round(dbms_random.value(1,20000)));
insert into emp values(5,'yin','fab','nodejs',round(dbms_random.value(1,20000)));
insert into emp values(6,'he','arvin','nodejs',round(dbms_random.value(1,20000)));
insert into emp values(7,'wu','derek','system',round(dbms_random.value(1,20000)));
insert into emp values(8,'weng','alvin','it',round(dbms_random.value(1,20000)));
insert into emp values(9,'he','klaus','system',round(dbms_random.value(1,20000)));
insert into emp values(10,'chen','light','java',round(dbms_random.value(1,20000)));

select e.last_name||' '||e.first_name, e.job, e.salary, sg.salary_grade_id
from emp e, salary_grades sg
where e.salary between sg.low_salary and sg.high_salary
order by salary_grade_id;

注：由于emp表中的salary由随机函数生成（有关随机数的用法，参考： Oracle DBMS_RANDOM包生成随机数）， 故查询结果会有所不同；

2. 连接类型：

2.1 内连接(inner join):只有当连接中的列包含满足连接条件的值时才返回一行。换句话说，如果某一行的连接条件中的一列是空值，那么这行就不会返回。

2.2 外连接(outer join):即使连接条件中的一列包含空值也返回一行，外连接可分为三类，通常我们可以省略outer这个关键字。其中左外连接和右外连接时都会以一张表作为基表，该表符合条件的内容会全部显示，然后加上两张表匹配的内容。如果基表的数据在另一张表没有记录，那么在相关的结果集中列显示为空值（NULL）：

2.2.1 左外连接(left outer join)：左外连接是以左表的记录为基础的，左表所有满足条件的列值显示，右表匹配的显示相应的列的值，不匹配的显示NULL。

--写法1:
select e.*,d.*
from emp e left outer join dept d
on e.dept_id=d.dept_id;

--写法2：
select e.*, d.*
from emp e, dept d
where e.dept_id=d.dept_id(+);

2.2.2 右外连接(right outer join)：和左外连接刚好相反，右外连接是以右表为基础，显示右表中满足条件的所有数据，左表匹配的列 显示相应的值，不匹配的现实NULL。

--方法1：
select e.*,d.*
from emp e right outer join dept d
on e.dept_id=d.dept_id;

--方法2：
select e.*,d.*
from emp e, dept d
where e.dept_id(+)=d.dept_id;

2.2.3 全外连接(full outer join):  左表和右表都不做限制，所有记录都显示，两个表不足的地方用NULL补充。全外连接不支持(+)这种写法。

select e.*, d.*
from emp e full outer join dept d
on e.dept_id=d.dept_id;

注意：

对于外连接，也可以用"(+)"来表示，关于"(+)"使用，请注意以下几点注意事项：

• (+)操作符只能出现在WHERE子句中，并且不能与OUTER　JOIN语法同时使用；
• 当使用(+) 操作符执行外连接时，如果WHERE子句中包含多个条件，则必须在所有连接条件中都包含(+)操作符；
• (+)操作符只适用于列，而不能用在表达式上；
• (+)操作符不能和OR或IN操作符一起使用；
• (+)操作符用于实现左外连接或右外连接，不能用于实现全外连接；

3. 自连接(self join):

自连接(self join)是SQL语句中经常要用的连接连接方式，使用自连接可以将自身表的一个镜像当作另一个表来对待，从而能够得到一些特殊的数据。

--数据准备：
alter table emp add mgr number;
update emp set mgr=trunc(dbms_random.value(1,3));

--示例：查询EMP表中员工姓名、员工经理姓名：

select worker.emp_name, manager.emp_name as manager
from emp worker, emp manager
where worker.mgr=manager.emp_id
order by worker.emp_name;

--------------------------------------

本文系原创，转载请表明出处！