10.Oracle 高级查询-多表关联（包括投影查询）

Oracle 高级查询-多表关联

关联条件

1. 等值关联
2. 表达式关联/非等值关联

关联方式

1. where
2. join

join可以实现where不能实现的一些关联（全外、交叉）

join的查询效率比where要高很多

---

where

• 等值

select e.deptno, empno, ename, sal, dname, loc
    from emp e, dept d
        where e.deptno = d.deptno      -- 关联条件
            and job = 'MANAGER';       -- 筛选条件

注意：

关联的列或者查询的列，如果同名需要限定表名（可以通过表别名）！

• 表达式

select empno, ename, sal, grade
    from salgrade, emp
        -- where sal >= losal and sal <= hisal
        where sal between losal and hisal
            and job = 'MANAGER';

三表关联（emp和dept等值关联，emp和salgrade表达式关联）

select empno, ename, sal, d.deptno, dname, loc, grade
    from salgrade, emp e, dept d
        where sal between losal and hisal and e.deptno = d.deptno   -- 关联条件
            and job = 'MANAGER';									-- 查询条件

扩展：

在where中如何进行左外和右外的关联

A表条件 = B表条件（这时关联条件中，A是左表，B是右表）

当A表和B表中的关联列有行没有等值关系，

如果希望左侧A表的数据出现（右侧B表没有匹配的列显示为null），称作左外关联

如果希望右侧B表的数据出现（左侧A表没有匹配的列显示为null），称作右外关联

左外：

-- 以雇员表作为左表查询所有行，右侧部门表没有匹配的数据列就显示null
select e.deptno, empno, ename, sal, dname, loc
    from dept d, emp e
        where e.deptno = d.deptno(+);

右外：

-- 以部门表作为右侧查询所有行，左侧雇员表没有匹配的数据列就显示null
select e.deptno, empno, ename, sal, dname, loc
    from dept d, emp e
        where e.deptno(+) = d.deptno;

join

[inner] join：

• 等值

select e.deptno, empno, ename, sal, dname, loc
    from dept d join emp e
        on e.deptno = d.deptno      -- 关联条件
            where job = 'MANAGER';  -- 筛选条件

• 表达式

select empno, ename, sal, grade
    from salgrade join emp
        on sal between losal and hisal  -- 关联条件
            where job = 'MANAGER';      -- 筛选条件

三表关联（emp和dept等值关联，emp和salgrade表达式关联）

select empno, ename, sal, d.deptno, dname, loc, grade
    from
        emp e join salgrade on sal between losal and hisal
            join dept d on e.deptno = d.deptno
                where job = 'MANAGER';

select empno, ename, sal, d.deptno, dname, loc, grade
    from
        dept d join emp e on e.deptno = d.deptno
            join salgrade on sal between losal and hisal
                where job = 'MANAGER';

左外 left [outer] join：

-- 所有雇员信息都出现（在join的左侧）
select empno, ename, e.deptno, dname, loc
    from emp e left join dept d
        on d.deptno = e.deptno;

右外 right [outer] join：

-- 所有部门信息都出现（在join的右侧）
select empno, ename, d.deptno, dname, loc
    from emp e right join dept d
        on d.deptno = e.deptno;

全外full [outer] join：

-- 左右两表的数据行全部出现，对方没有匹配的列就显示null
select empno, e.deptno, ename, d.deptno, dname, loc
    from emp e full outer join dept d
        on d.deptno = e.deptno;

自然连接（如果两个表具有相同的列名，那么可以不指定连接的列条件，自动进行同名列关联）

natural join

join using

select empno, ename, sal, deptno, dname, loc
    from emp natural join dept
        where job = 'MANAGER';

select empno, ename, sal, deptno, dname, loc
    from emp join dept using (deptno)
        where job = 'MANAGER';

注意：不需要使用表的别名！

交叉：

create table t_student (id int, name varchar(10));
create table t_course (id int, name varchar(10));

insert into t_student values (100, 'Tom');
insert into t_student values (101, 'Sam');

insert into t_course values (1, 'Java');
insert into t_course values (2, 'Web');
insert into t_course values (3, 'iOS');

select s.name, c.name
    from t_student s cross join t_course c;

---

自关联/自连接

where和join都可以实现

在一张表上，进行两个不同列的关联，一般用于设计和查询具有级联关系的数据

-- 查找上级BLAKE的下属
select e.ename, m.ename
    from emp e join emp m
        on e.mgr = m.empno   -- 雇员表的上级编号等于上级表的雇员编号
            where m.ename = 'BLAKE';

-- 查找比上级资历还早的雇员
select e.ename, e.hiredate, m.ename, m.hiredate
    from emp e, emp m
        where e.mgr = m.empno
            and e.hiredate < m.hiredate;

-- 同样支持外连接
-- 查看所有雇员信息（岗位可能是主席或者尚未分配）
select e.ename, e.job, m.ename
    from emp e left join emp m
        on e.mgr = m.empno;

应用场景 只要具有上下级关系的实体，都可以采用一张表来描述！

组织结构

商品菜单

文件目录

1 Windows null

2 Font 1

3 Programes 1

4 Office 3

5 Word 4

投影查询

多个查询结果的合/并集、交集、差集（A-B, B-A）

合/并集：两个结果集合并（包括重复行）

相当于同一张表进行多个查询条件or

• union 去重

• union all 包括重复

select * from emp where sal > 2000
union -- all
select * from emp where to_char(hiredate, 'yyyy') > 1982;

只要查询的不同结果集，列的个数相同，类型能够兼容，就能合并！！

• intersect 交集（两个结果集相同的行）

  相当于同一张表进行多个查询条件and

select * from emp where sal > 2000
intersect
select * from emp where to_char(hiredate, 'yyyy') > 1982;

• minus 差集

有两种情况：

A结果集中减去B和自己重复的部分

B结果集中减去A和自己重复的部分

select * from emp where sal > 2000
minus
select * from emp where to_char(hiredate, 'yyyy') > 1982;

select * from emp where to_char(hiredate, 'yyyy') > 1982
minus
select * from emp where sal > 2000;