Oracle表连接精解

www_xylove

下面比较全面，深入讲解了oracle关于表的连接的方式

附件： oracle表连接.docx (35.69 KB, 下载次数: 116)

2013-08-17 23:00 上传

点击文件名下载附件

Oracle表连接
Oracle语法&SQL1999语法：
1.笛卡尔乘积(CROSS JOIN)
   第一张表的所有行会与第二张表的所有行进行连接，即n*m方式进行连接
   oracle语法：
   select count(*) from hr.employees a ,hr.departments b;
    2889行
   雇员表的107行记录与部门表的27行记录相乘积（107*27），得到2889条记录
   SQL1999语法：
  select count(*) from hr.employees a cross join hr.departments b
    2889行

2.等价连接
   select a.department_id, b.department_id, a.employee_id, b.department_name
  from hr.employees a, hr.departments b
where a.department_id = b.department_id
   and a.department_id = 90
a.DEPARTMENT_ID        b.DEPARTMENT_ID        EMPLOYEE_ID        DEPARTMENT_NAME
90        90        100        Executive
90        90        101        Executive
90        90        102        Executive

把在a表和b表，部门相同，且部门号为90的员工匹配出来。
SQL1999语法：
在sql1999语法中的natural jion连接中，与oracle语法中的等值连接是有区别的.
先举例说明：
natural jion连接的条件是：两表连接中的列名要有相同的、相同的列名的值要相同的、相同的列名的数据类型要相同的。

sql-1:
select a.employee_id, a.manager_id, a.department_id
  from hr.employees a
where a.employee_id in (114, 115, 116, 117, 118, 119)
雇员表雇员114,115,116,117,118,119的相关结果集如下：
EMPLOYEE_ID        MANAGER_ID        DEPARTMENT_ID
114                          100                   30
115                  114                   30
116                       114            30
117                          114                   30
118                  114                   30
119                          114                   30

sql-2:
等值连接：
select a.employee_id, a.manager_id, a.department_id
  from hr.employees a, hr.departments b
where a.department_id = b.department_id
   and a.department_id = 30
EMPLOYEE_ID        MANAGER_ID        DEPARTMENT_ID
114                             100          30
115                            114          30
116                             114          30
117                     114          30
118                             114          30
119                             114          30

sql-3:
netural join:
select employee_id, manager_id, department_id
  from hr.employees a natural
  join hr.departments b
where department_id = 30
EMPLOYEE_ID        MANAGER_ID        DEPARTMENT_ID
115                            114          30
116                             114          30
117                     114          30
118                             114          30
119                             114          30

大家注意看，在sql-1中，我列出了雇员114~119的一些情况，在sql-2中，进行的是等值连接，而在sql-3中，进行的是NATURAL JOIN连接.
sql-2与sql-3的结果集的区别在哪里？
sql-2的等值连接中有雇员114的记录，而sql-3的NATURAL JOIN连接中没有雇员114的记录，为什么？
natural jion连接的条件是：
(1) 两表连接的列名要有相同的
(2) 两表连接的列名的数据类型要相同的
(3) 两表连接的列名的值要相同的，原因就在这里。
原因就是雇员表中部门30的雇员114的manager_id的值是100，而部门表中30号部门中没有manager_id=100的记录，所以，不符合条件(3)，故而netural join连接中没有匹配雇员为为114的记录了，然而，等值连接则没有这样的条件，故而能匹配雇员为114的记录。
  这就是NATURAL JOIN与等值连接的区别。
其实NATURAL JOIN自然连接是两表中的相同的列的自然连接，换句话说，就是不需要人工干预，比方说，A表与B表有3列名相同，那么oracle NATURAL JOIN自然连接就连接相同的三列，自然连接的这相同的三列的列值是AND的关系，三列的条件必须同时满足才匹配，如果其中一列值不匹配，那么该列的列值就被抛弃。如果进行人工干预，比方说，我只需要使用其中一列作为连接列，那么这就是不是自然连接了，可以是USING子句，oracle等价连接来完成。
再举例一个：
看一个需要人工干预的等值连接：
select b.department_id "b-manager_id",
       a.department_id "a-department_id",
       b.manager_id    "b-department_id",
       a.manager_id    "a-manager_id"
  from hr.employees a, hr.departments b
where a.department_id = b.department_id
   and a.department_id = 30
b-department_id        a-department_id        b-manager_id        a-manager_id
30        30        114        100
30        30        114        114
30        30        114        114
30        30        114        114
30        30        114        114
30        30        114        114
看上述表格的第一行，a表列department_id,manager_id与b表department_id
,manager_id两列相同，但是这里我使用人工控制的方式来进行连接，就是说我选择了其中一个相同列deparament_id来进行连接，只有两表department_id匹配，就返回记录，而不管manager_id匹配不匹配.
自然连接则不同，下面举例：
select department_id "b-department_id",
       department_id "a-department_id",
       manager_id    "b-manager_id",
       manager_id    "a-manager_id"
  from hr.employees a natural join  hr.departments b
   where department_id = 30
b-department_id        a-department_id        b-manager_id        a-manager_id
30        30        114        114
30        30        114        114
30        30        114        114
30        30        114        114
30        30        114        114
看上图，自然连接就是两表所有相同的列进行连接，其实看sql语句也看出来了，上面的sql并没有连接条件，这也表示了是用两表中所有的相同的列进行连接，这是自然连接的第一个点，再有就是，比较两张表格的结果集，有所不同，b-manager_id=114与a-manager_id=100的结果集不符合自然连接的要求，故而自然连接的结果集中并没有显示，因为必须满足b-manager_id= a-manager_id的要求，其实，上述自然连接的sql可以等价如下语句：
select b.department_id "b-department_id",
         a.department_id "a-department_id",
         b.manager_id    "b-manager_id",
         a.manager_id    "a-manager_id"
    from hr.employees a, hr.departments b
   where a.department_id = 30
     and a.department_id = b.department_id
     and a.manager_id = b.manager_id
b-department_id        a-department_id        b-manager_id        a-manager_id
30        30        114        114
30        30        114        114
30        30        114        114
30        30        114        114
30        30        114        114
注意的是，自然连接的select 列表中，不允许有别名来限定表名，where 条件中也不允许有别名来限定表名。
上面讲的是两表连接。
自然连接中的多表（2张表以上）连接也是一样的。
select department_id "b-department_id",
       department_id "a-department_id",
       manager_id    "b-manager_id",
       manager_id    "a-manager_id",
       job_id   "c-job_id"
  from hr.employees a natural join  hr.departments b natural join hr.jobs c
   where department_id = 30
b-department_id        a-department_id        b-manager_id        a-manager_id        c-job_id
30        30        114        114        PU_CLERK
30        30        114        114        PU_CLERK
30        30        114        114        PU_CLERK
30        30        114        114        PU_CLERK
30        30        114        114        PU_CLERK

bowbower

太拽了。。。。。。。。。。。。。。。。。。。。。。。

   

zhangyuqing123

你好，看到你的帖子，受益很多，能否加QQ一起讨论下啊？我的QQ 2497321200

www_xylove

3.外连接
    Oracle语法：
   3.1 左外连接
select a.employee_id, a.department_id, b.department_id, b.department_name
  from hr.employees a, hr.departments b
where a.department_id = b.department_id(+)
   and a.employee_id in (100, 101, 17
order by a.employee_id
EMPLOYEE_ID        DEPARTMENT_ID        DEPARTMENT_ID        DEPARTMENT_NAME
100        90        90        Executive
101        90        90        Executive
178                                 空值        空值        空值
雇员178号没有部门号，这个如果连接条件是a.department_id = b.department_id,则不会匹配出雇员178，因为雇员表没有雇员178的的部门号，部门表也没有雇员178的部门号，空值与空值的匹配仍然是空值.但是如果是部门表b.department_id(+)这样与雇员表a.department_id进行连接，那么就会把雇员178给匹配出来.这就是oracle的左外连接.
    SQL1999语法：
select a.employee_id, a.department_id, b.department_id, b.department_name
  from hr.employees a left outer join hr.departments b
    on a.department_id = b.department_id
where a.employee_id in (100, 101, 17
order by a.employee_id
得出一样的结果集
  3.2 右外连接
select a.employee_id, a.department_id, b.department_id, b.department_name
  from hr.employees a, hr.departments b
where a.department_id(+) = b.department_id
and b.department_id in (110,120)
EMPLOYEE_ID        DEPARTMENT_ID        DEPARTMENT_ID        DEPARTMENT_NAME
205        110        110        Accounting
206        110        110        Accounting
空值        空值        120        Treasury
部门号120没有雇员，如果这个连接条件为a.department_id = b.department_id,将不会匹配部门号120的记录，因为雇员表的没有部门为120的部门，为空值，部门表有120号部门，空值与部门120进行匹配，得到的结果仍然是空值。但是如果雇员表想知道部门表里面还有哪些部门是没有雇员的，怎么办？那么这样可以解决这个问题,雇员表department_id(+)与部门表department_id进行连接，则可以将部门表没有雇员的部门也匹配出来。这就是Oracle的右外连接.
SQL1999语法：
select a.employee_id, a.department_id, b.department_id, b.department_name
  from hr.employees a right outer join hr.departments b
  on a.department_id = b.department_id
where b.department_id in (110,120)
得出一样的结果集
  3.2 全外连接
     oracle语法中并没有全外连接的写法，在sql1999语法中，有这个写法：
     使用 full outer join关键字来连接
      (略)

4.自连接
oracle语法中的自连接，是在一张表中进行连接，雇员表中的员工与管理员的关系，可以在一张雇员表中找出来，比如员工的管理者是谁。
举例：
比如90号部门的雇员情况如下：
select w.employee_id,
        w.first_name,
        w.last_name,
        w.manager_id,
        w.department_id
   from hr.employees w
  where w.department_id = 90
EMPLOYEE_ID        FIRST_NAME        LAST_NAME        MANAGER_ID        DEPARTMENT_ID
100        Steven        King                90
101        Neena        Kochhar        100        90
102        Lex        De Haan        100        90
员工101,102的的管理者为100，即Steven King.如果通过两张表的连接体现在一行上面.使用oracle的自连接可以实现这一点.
select w.employee_id as "员工编号",
        w.first_name || ' '||
        w.last_name as "员工姓名",
        m.employee_id as "管理者编号",
        m.first_name ||' ' ||
        m.last_name as "部门管理者",
        m.manager_id as  "部门管理者编号"
   from hr.employees w, hr.employees m
  where w.manager_id = m.employee_id
    and w.department_id = 90
员工编号        员工姓名        管理者编号        部门管理者        部门管理者编号
101        Neena Kochhar        100        Steven       
102        Lex De Haan        100        Steven       
使用自连接，实现了一行记录即有员工，也有管理者的记录，其实就是把雇员ID为100的一行记录分别与雇员ID为101,102的两行记录进行了合并而已。
这就是oracle的自连接。

5.using子句进行表连接
sql1999语法：
其实using 子句是相对于natural join来说的，其实就是等值连接
举例：
select department_id "b-department_id",
       department_id "a-department_id",
       manager_id    "b-manager_id",
       manager_id    "a-manager_id"
  from hr.employees a  join  hr.departments b using (department_id)
   where department_id = 30
b-department_id        a-department_id        b-manager_id        a-manager_id
30        30        114        100
30        30        114        114
30        30        114        114
30        30        114        114
30        30        114        114
30        30        114        114
相当于等值连接
注意的是，select 列表中，不允许有别名来限定表名，where 条件中也不允许有别名来限定表名。
也可以这样写：
select department_id "b-department_id",
       department_id "a-department_id",
       manager_id    "b-manager_id",
       manager_id    "a-manager_id"
  from hr.employees a  join  hr.departments b using (department_id,manager_id)
   where department_id = 30
b-department_id        a-department_id        b-manager_id        a-manager_id
30        30        114        114
30        30        114        114
30        30        114        114
30        30        114        114
30        30        114        114
相等于自然连接。
注意的是，select 列表中，不允许有别名来限定表名，where 条件中也不允许有别名来限定表名。
6.on子句

sql1999语法：
其实on子句只不过是等值连接另外一种写法罢了。
举例说明：
select b.department_id "b-department_id",
       a.department_id "a-department_id",
       b.manager_id    "b-manager_id",
       a.manager_id    "a-manager_id"
  from hr.employees a
  join hr.departments b
    on (a.department_id = b.department_id)
where a.department_id = 30
相当于：
select b.department_id "b-department_id",
       a.department_id "a-department_id",
       b.manager_id    "b-manager_id",
       a.manager_id    "a-manager_id"
  from hr.employees a, hr.departments b
where a.department_id = 30
   and b.department_id = a.department_id
得到相同的结果集：
b-department_id        a-department_id        b-manager_id        a-manager_id
30        30        114        100
30        30        114        114
30        30        114        114
30        30        114        114
30        30        114        114
30        30        114        114
注意的是，在on子句中，select 列表中，允许有别名来限定表名，where 条件中允许有别名来限定表名。

end.