oracle 数据库3——关联查询

1. 关联查询概述

1. 关联查询就是从多张表中关联查询数据，重点是多张表之间的连接条件，根据该条件数据库才知道这些表之间数据的对应关系，从而分别获取这些对应的记录中查询的字段并构成结果集。
2. 关联查询要有连接条件，不添加连接条件会产生笛卡尔积，这通常是一个无意义的结果集。
3. 原则上 N 张表关联至少需要有 N-1 个连接条件。

2. 关联查询的分类：

• 关联查询有内连接、外连接、自连接三种情况。

3. 等值连接

• 是连接查询中最常见的一种，通常是在有主外键关联关系的表间建立，并将关联条件设定为有关系的列，使用“=”连接有关系的表。等值连接是关联查询的基础。

--没有关联条件会出现笛卡尔积
select  ename,dname from emp,dept; 

--不会出现笛卡尔积
select emp.ename,dept.dname from emp,dept where emp.deptno = dept.deptno;

--表也可以使用别名，方便操作
select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno;

--where 中关联条件和过滤条件必须同时成立，否则出现笛卡尔积
select e.name,e.job,d.loc from emp e,dept d 
where e.deptno = d.deptno AND e.job='SALESMAN';

--查看每个地区的平均工资
select d.loc,AVG(e.salary) from emp e,dept d where e.deptno = d.deptno group by d.loc; 

4. 关联查询分类

关联查询	关键字	意义
内连接	JOIN ON	关联查询中不满足连接条件的数据不会被查询出来
外连接	左外连接（LEFT OUTER JOIN ON）	以 JOIN 左侧表为驱动表，该表中所有记录都要显示，那么右侧表的字段若不满足连接条件则为 NULL
	右外连接（RIGHT OUTER JOIN ON）	以 JOIN 右侧表为驱动表，右侧表的所有记录都要显示，左侧表中不满足连接条件的字段则为 NULL
	全外连接(FULL OUTER JOIN ON)	除了满足返回连接条件的记录，还将不满足连接条件的所有行返回，是左外连接和右外连接查询结果的总和
自连接	JOIN ON	数据来源是一个表，通过别名将一张表虚拟成两张表来实现，关联关系是来自单表中的多个列

内连接
只显示满足连接条件的记录

--查看每个员工以及所在部门名
select e.ename,d.dname 
from emp e JOIN dept d ON e.deptno = d.deptno;

--也可以用等值连接来表示：
select e.ename,d.dname
from emp e,dept d
where e.deptno = d.deptno;

外连接
不仅将满足连接条件的记录查询出来，还将驱动表中的记录都显示出来，不满足条件的字段为 NULL

--左外连接（JOIN 左边为驱动表）
--列出所有员工及其所在部门名（所有员工为驱动表）
select e.ename,d.dname
from emp e LEFT OUTER JOIN dept d ON e.deptno = d.deptno; 

等值连接（普通连接）也可以作外连接操作，但是不可以作全外连接操作。
+ 在连接条件的左边，左边为 NULL ，是右外连接；
+ 在连接条件的右边，右边为 NULL，是左外连接；
+ 不能连接条件两边都写，所以等值连接不可以作全外连接；

--左外连接
select e.ename,d.dname
from emp e,dept d
where e.deptno = d.deptno(+);

自连接
自连接是一种特殊的连接查询，数据来源是一个表，即关联关系来自于单表中的多个列；
表中的列参照同一个表中的其他列的情况，称作自参照表；
自连接是当前表的一条数据对应当前表的多条数据；
自连接用于解决相同属性的一组数据，但是又存在上下级关系的树状结构组织时使用；

--查看每个员工及其上司的名字
select e.enmae,m.ename
from emp e JOIN emp m ON e.mgr = m.mepno;