多表关联查询(Oracle)_oracle a表与b表关联,b表记录多,显示b表数量

作者：知新_RL | 2024-04-15 01:52:50

踩

oracle a表与b表关联,b表记录多,显示b表数量

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

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);

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/知新_RL/article/detail/425225