5、多表查询
多表查询涉及内连接和外连接两部分,首先,假设有两张表:雇员表s_emp 部门表 s_dept
id name(员工名字) mid(上级领导的id) deptid(部门的id) id(部门id) name(部门名字)
1 tom null null 1 系统软件部
2 jerry 1 1 2 应用软件部
3 hyman 1 2
4 lily 2 1
5 jim 2 1
1、内连接
符合条件就保留,不符合条件就过滤掉a、等值连接
就是用等号连接两张表如从s_emp 和s_dept中查询显示所有部门的所有成员的id、name以及部门id、name
select e.id,e.name,d.id,d.name from s_emp e,s_dept d where e.deptid=d.id
结果
ID NAME ID NAME
--------- -------------------- ---------- --------------------
2 jerry 1 系统软件部
3 hyman 2 应用软件部
4 lyli 1 系统软件部
5 jim 1 系统软件部
需要说明的是这里的e、d分别是表s_emp和s_dept的别名,因为我们要显示两张表的id和name字段,以别名进行区分。
b、非等值连接
就是用>、<等非等值符合连接两张表c、自连接
只有一张表,在一张表中进行查询如查询s_emp中所有的领导(当有员工的mid是你的id时,你就是领导),可以把该表在内存中复制成两张表,再用多表查询的方法进行查询:
雇员表s_emp f:
id name(员工名字) mid(上级领导的id) deptid(部门的id)
1 tom null null
2 jerry 1 1
3 hyman 1 2
4 lily 2 1
5 jim 2 1
雇员表s_emp s:
id name(员工名字) mid(上级领导的id) deptid(部门的id)
1 tom null null
2 jerry 1 1
3 hyman 1 2
4 lily 2 1
5 jim 2 1
select distinct s.id,s.name from s_emp f,s_emp s where f.mid=s.id;
结果:
ID NAME
---------- --------------------
1 tom
2 jerry
这里要说明一下可以看到在s_emp中lily、 jim 2人分别对应领导jerry, jerry、hyman两人分别对应领导tom,这就使得我们在查询时会出现2个tom以及2个jerry,这就需要用distinct进行排重
d、sql99中标准内连接
sql99中还规定了另外一种标准的内连接方式:from 表1 inner join 表2 on 连接条件以查找领导为例,构建sql语句如下;
select distinct s.id,s.name from s_emp f inner join s_emp s on f.mid=s.id;
2、外连接
内连接的结果集加上匹配不上的记录外连接分为oracle标准和sql99标准两个部分,分别进行介绍:
a、oracle标准外连接(+):
这里以查找s_emp中谁是普通员工为例首先,oracle在内存中构建两张表s_emp f(左表)和s_emp s(右表),在进行内连接的匹配时,右表中的id匹配左表中的mid,当发现右表中的有部分id是在左表mid中找不到的,就用NULL代替,
如下图:
这里的(+)表示把对面的所有数据匹配出来,匹配不到的匹配NULL,在这里就是把右表中所有id进行匹配。这样能匹配上的就是领导,而左表对应的mid是NULL的就是普通员工。
b、sql99中规定的外连接方式
(1)右外连接表1 right outer join 表2 on 连接条件 where 筛选条件,由表1发起连接,把表1所有内容匹配出来
如,还是求谁是普通员工:
select distinct s.id,s.name from s_emp f right outer join s_emp s on f.mid=s.id where f.mid is NULL;
(2)左外连接
表1 left outer join 表2 on 连接条件 where 筛选条件,由表1发起连接,把表1所有内容匹配出来
(3)全外连接
表1 full outer join 表2 on 连接条件 where 筛选条件,由表1发起连接,把左外连接和右外连接的集合合并,并去除重复部分