Oracle查询入门（五）连表查询

定义：
表连接查询 : 当要查询的数据来自于多个数据源

现在主要有92语法和99语法
–92语法 select 数据 from 数据源1,数据源2… where 行过滤条件 group by 分组字段1,分组字段2… having 组过滤信息 order by 排序字段1,… desc|asc;

执行流程 : from --> where --> group by --> having --> select --> order by

92语法中表 连接条件定义 在where后面

如果要使用的字段来自于多个数据源中都存在,需要指明限定词|出处

一、92语法

1.1笛卡尔积

--笛卡尔积 : 交叉连接
--查询所有员工的信息以及员工所在部门信息
--数据 : 员工信息*  部门信息*
--来源 : 员工表emp ,部门表 dept
select * from emp,dept;
select emp.*,dept.deptno,dept.dname from emp,dept;
--别名
select e.*,d.deptno,d.dname from emp e,dept d;

1.2等值连接

判断两个数据源中的某个字段值相等或者不相等

–表连接条件 : 过滤通过连表产生的不满足要求的表连接数据
–等值连接 : 判断两个数据源中的某个字段值相等或者不相等
–非等值连接 : 判断区间,判断范围的条件
–92语法中表 连接条件定义 在where后面
–如果要使用的字段来自于多个数据源中都存在,需要指明限定词|出处

--员工名称及部门名称
select ename, dname, e.deptno from emp e, dept d where e.deptno = d.deptno;
--找出30部门的员工名称及部门名称:先关联后过滤
select ename, dname, e.deptno
from emp e, dept d
where e.deptno = d.deptno
and e.deptno = 30;
--记录很多时 :先过滤后关联
-- 数据来源： emp (select * from emp where deptno=30) e , dept(select * from dept
where deptno=30) d
select * from emp where deptno = 30;
select * from dept where deptno = 30;
-- 查询的字段：ename, dname, e.deptno
-- 条件：e.deptno=d.deptno , deptno=30
select ename, dname, e.deptno
from (select * from emp where deptno = 30) e,
(select * from dept where deptno = 30) d
where e.deptno = d.deptno;

1.3非等值连接

非等值连接 : 判断区间,判断范围的条件

--查询员工姓名，工资及等级
--900 属于哪个等级
select grade
from salgrade
where 900 > losal
and 900 < hisal;
select grade from salgrade where 900 between losal and hisal;
--查询员工姓名，工资及等级
-- 数据源： emp e, salgrade s
-- 字段： ename, grade, sal
-- sal between losal and hisal
select ename, grade, sal
from salgrade s, emp e
where sal between losal and hisal;

1.4自连接

特殊的等值连接，一张表作为两个数据源来使用

--找出 存在上级的员工姓名 及上级名称
-- 数据来源： emp e, emp m
-- 字段： e.ename, m.ename
-- 条件： e.mgr=m.empno
select e.ename, m.ename from emp e, emp m where e.mgr = m.empno;

1.5外连接

内连接 : 满足连接条件查询到不满足过滤掉
外连接 : 某一个数据源中的数据不满足连接条件的时候也能显示

左外连接 | 左连接 : from后面主表在左边
右外连接 | 右连接 : from后面主表在右边

主表 : 主表中的数据无论是否满足连接条件都能显示

92语法 : 在连接条件位置,主表的对面添加(+)

select * from emp e1,emp e2 where e1.mgr=e2.empno(+);
select * from emp e1,emp e2 where e1.mgr(+)=e2.empno;

--找出 所有的员工姓名 及上级名称
--找出 所有部门的员工数 及部门名称
select dname, nu
from dept d, (select count(1) nu, deptno from emp group by deptno) e
where d.deptno(+) = e.deptno;

看逗号, 主表在,的左边就叫左外连接主表在,的右边叫右连接

二、99语法

相比较于92语法，99语法更新，功能更完善

• 叉连接 cross join —>笛卡尔积
• 自然连接(主外键、同名列) natural join -->等值连接
• join using连接(同名列) -->等值连接
• [inner]join on 连接 -->等值连接 非等值 自连接 (解决一切) 关系列必须区分
• left|right [outer] join on|using -->外连接
• full join on|using -->全连接 满足直接匹配，不满足 相互补充null ，确保 所有表的记录 都至少出 现一次

2.1交叉连接

select * from emp cross join dept

2.2自然连接

自动做等值连接

select * from emp natural join dept;
--在指定列过程中同名列归共同所有(*除外)
select deptno,e.ename,d.dname from emp e natural join dept d;

2.3using连接

指定同名字段做等值连接

select deptno,e.ename,d.dname from emp e join dept d using(deptno);

2.4on连接

-- natrual 等值
select ename, dname from emp natural join dept where deptno = 30;
--using
select ename, dname from emp join dept using (deptno) where deptno = 30;
--on
select ename, dname
from emp
join dept
on emp.deptno = dept.deptno
where emp.deptno = 30;

--on 非等值连接 、自连接
--部门编号为30的员工名称 工资等级
select ename, grade
from emp e
join salgrade s
on e.sal between s.losal and s.hisal
where deptno = 30;
--部门编号为30的员工名称 上级名称
select e.ename, m.ename mname
from emp e
join emp m
on e.mgr = m.empno
where e.deptno = 30;
--部门编号为30的员工 员工名称 部门名称 工资等级 上级名称
select e.ename, dname, grade, m.ename mname
from emp e
join dept d
on e.deptno = d.deptno
join salgrade s
on e.sal between s.losal and s.hisal
join emp m
on e.mgr = m.empno
where e.deptno = 30;

2.5外连接

--所有部门的 部门名称，员工数
--左外
select dname, n
from dept d
left outer join (select deptno, count(1) n from emp group by deptno) i
on d.deptno = i.deptno;
select dname, n
from dept d
left outer join (select deptno, count(1) n from emp group by deptno) i
using (deptno);
--右外
select dname, n
from (select deptno, count(1) n from emp group by deptno) i
right outer join dept d
on d.deptno = i.deptno;

2.6全连接

select *
from (select 1 no, 'a' "name"
from dual
union
select 2 no, 'b' "name"
from dual) a
full join (select 1 no, 'c' "name"
from dual
union
select 3 no, 'd' "name"
from dual) b
on a.no = b.no;
select *
from (select 1 no, 'a' "name"
from dual
union
select 2 no, 'b' "name"
from dual) a
full join (select 1 no, 'c' "name"
from dual
union
select 3 no, 'd' "name"
from dual) b
using (no);

--主表是员工
select * from emp e1 left join emp e2 on e1.mgr=e2.empno;
select * from emp e1 right join emp e2 on e1.mgr=e2.empno;
select * from emp e1 full join emp e2 on e1.mgr=e2.empno;

主表是员工表：

主表是上级表：

使用全连接后包含上面两种表：