Oracle笔记之表连接

概述
本篇博文中主要探讨以下内容：

1. 表连接（92、99）
2. 笛卡尔积
3. 等值连接
4. 自连接、内连接、外连接、全连接

当我们获取的数据不是来自于同一张表而是来自于多张
表时就需要使用到表连接

1. 表连接（92）

select * from emp;
select * from dept;

注意:同名列 非* 必须区分
数据源 、关系列、 过滤条件、字段

1.1. 笛卡尔积

笛卡尔乘积是指在数学中，两个集合X和Y的笛卡尔积（Cartesian product），又称直积，表示为X × Y，第一个对象是X的成员而第二个对象是Y的所有可能有序对的其中一个成员.

在Oracle中的笛卡尔积：A的集合 x B的集合

非* 必须区分 使用表名 或别名.区分

select * from emp , dept;
select ename , dname from emp , dept;
select ename, dname, e.deptno from emp e, dept d;

1.2. 等值连接

等值连接(在笛卡尔积基础上 取条件列相同的值)

--员工名称及部门名称
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. 非等值连接

非等值连接 > < != <>between and

--查询员工姓名，工资及等级
--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. 外连接

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

1.6. 内连接

inner join（和join一样）

select e.deptno,dname,ename from emp e ,dept d where e.deptno = d.deptno order by deptno;

--using on clauses

SQL> select e.deptno,dname,ename from emp e inner join dept d on e.deptno = d.deptno order by deptno;
-- inner可以省略

2. 表连接（99）

1. 交叉连接
2. 内连接
3. join using连接(同名列)
4. 外连接
5. 全连接

名称	关键词	说明
交叉连接	cross join	笛卡尔积
自然连接	natural join	等值连接（主外键、同名列）
using连接	join using	等值连接，非等值 自连接（解决一切）关系列必须区分
外连接	left	right [outer] join on
全连接	full join on	using

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.3. using 连接

指定同名字段做等值连接

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

2.4. on 连接

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