oracle入门之 Select查询语句（三）

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代替，

如下图：

select distinct s.id,s.name from s_emp f,s_emp s where f.mid(+)=s.id and f.mid is 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发起连接，把左外连接和右外连接的集合合并，并去除重复部分