多表关联查询(Oracle)

 有时一个查询结果需要从两个或两个以上表中提取字段数据，此时需要使用多表关联查询。

 1）笛卡尔积关联

  create table a(

   id number(7),

   name varchar2(20));

  create table b(

   id number(7),

   name varchar2(20));

  insert into a(id,name) values(1,'a1');

  insert into a(id,name) values(2,'a2');

  insert into a(id,name) values(3,'a3');

  insert into b(id,name) values(1,'b1');

  insert into b(id,name) values(2,'b2');

 select * from a,b;

 笛卡尔积特点：

   --*代表from后面表中所有列

   --返回结果数量是各个表记录的乘积

   --结果是a每条记录与b每条记录结合形成

 *2)等值连接

  参与等值条件的两个字段值，相等时才作为结果返回。

 select a.id,a.name,b.name //3.提取显示的字段

 from a,b //1.形成笛卡尔积结果

 where a.id=b.id; //2.返回id相等的记录

  ---使用[INNER] JOIN...ON...语法-----

 select a.id,a.name,b.name

 from a join b on(a.id=b.id);

 提示：建议采用JOIN...ON语法,INNER JOIN和JOIN作用等价。内连接,等值连接是一个意思。

  ----使用JOIN...USING语法(了解)------

 select id,a.name,b.name

 from a join b using(id);

JOIN...USING使用注意事项：

 --关联的两个表中需要有相同的字段.(名字和类型相同)

 --关联的字段在使用时不能加别名

  //查询员工名称，工资,所在部门编号,部门名称

  select e.ename,e.sal,e.deptno,d.dname

  from emp e join dept d

     on(e.deptno=d.deptno);

3)外连接

  等值连接，需要两个表的关联字段等值才将结果返回。如果需要将某一个表记录全部返回，即使

  另一个表找不到对等字段记录，此时可以使用外连接。

  *a.左外连接

  ----使用LEFT OUTER JOIN...ON...语法------

  select e.ename,e.sal,e.deptno,d.dname

  from emp e left outer join dept d

     on(e.deptno=d.deptno);

 A left outer join B on(...)

 以A表记录显示为主,B表记录为补充.当A表记录在B表找不到对等记录时,B以NULL方式补充。

  b.右外连接

 select e.ename,e.sal,e.deptno,d.dname

  from emp e right outer join dept d

     on(e.deptno=d.deptno);

 上面语句是以dept表显示为主,emp为补充.如果emp没有对等记录,字段值以NULL补充。

select * from a

 right outer join b on(a.id=b.id);

等价于

select * from b

 left outer join a on(a.id=b.id)

----在JOIN...ON之前的外连接写法--------

//(+)所在表为补充表,另一方是主表

select * from a,b

where a.id(+)=b.id; //jb为主,ja为补充

  c.全外连接

   全外连接=左外连接+右外链接-(重复记录)

  select * from a

    full outer join b on(a.id=b.id);

 //查询部门编号,部门名称,部门员工人数

  select d.deptno,

         d.dname,

         count(e.ename) num

  from DEPT d left outer join EMP e

    on(d.deptno=e.deptno)

  group by d.deptno,d.dname

  order by d.deptno;

EMPNO ENAME DEPTNO DNAME

...    ...    10   ...

NULL   NULL   40   ...

NULL   NULL   50   ...

//按部门分组统计,count(*)和count(ename)的区别

count(*) = 1

count(ename) = 0

//查询部门在NEW YORK和CHICAGO的员工编号和员工名称

select e.empno,e.ename

from DEPT d join EMP e on(d.deptno=e.deptno)

where d.loc in ('NEW YORK','CHICAGO');

4)自连接

  关联双方的表是同一个表。

  //查询员工编号,员工名,上级编号,上级名称

  select e.empno,e.ename,e.mgr,e1.ename

  from EMP e left outer join

       EMP e1 on(e.mgr=e1.empno);

  //查询员工编号,员工名,所在部门名,上级编号,上级名称

  select e.empno,e.ename,d.dname,e.mgr,e1.ename

  from EMP e

     left outer join EMP e1 on(e.mgr=e1.empno)

     left outer join DEPT d on(d.deptno=e.deptno);