[数据库] Oracle表连接精解 [复制链接]

www_xylove

版主

论坛徽章:: 7

电梯直达

1楼 [收藏(0)] [报告]

发表于 2013-08-17 23:01 |只看该作者 |倒序浏览

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

附件：

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

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

Oracle

文库|博客

www_xylove

版主

论坛徽章:: 7

2楼 [报告]

发表于 2013-08-17 23:02 |只看该作者

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.