ORACLE数据库(八) ---- 表连接

ORACLE数据库(八) ---- 表连接

一、表连接分类

连接时，左表中的每条数据都会试图和右表中的每条数据做一次关联

1 内连接

按照关联条件关联，将两表中都符合关联条件的数据取出

FROM A [INNER] JOIN B ON A.a = B.b -- 常用

2 外连接

左外连接：FROM A LEFT [OUTER] JOIN B ON A.a = B.b 常用

右外连接：FROM A RIGHT [OUTER] JOIN B ON A.a = B.b 几乎不用

全外连接：FROM A FULL [OUTER] JOIN B ON A.a = B.b 偶尔用 用来排查问题

特点

左外连接：按照关联条件关联，左表作为主表， 取出 两表中都符合关联条件的数据 + 主表中独有的数据

右外连接：右表作为主表， 取出 两表中都符合关联条件的数据 + 主表中独有的数据

全外连接： 取出 两表中都符合关联条件的数据 + 左右表独有的数据

3 笛卡尔连接

交叉连接 : FROM A CROSS JOIN B 不常用 笛卡尔积：比较危险，会引起数据量暴涨

没有条件 ，左表中的每一条数据都与右表中的每一条数据做一次关联

二、常见用法

1 关联条件不止一个

使用AND 和 OR连接关联条件

-- 和SMITH同部门的其他人
SELECT A.DEPTNO,B.ENAME
  FROM EMP A
  JOIN EMP B
    ON A.DEPTNO = B.DEPTNO 
 WHERE A.ENAME = 'SMITH'
   AND B.ENAME <> 'SMITH';

2 关联表不止一张

-- 查询每个员工的员工编号、姓名、薪资、上级姓名、部门名称、员工薪资等级、领导薪资等级
SELECT A.EMPNO,A.ENAME,A.SAL,B.ENAME,D.DNAME,S.GRADE,S2.GRADE
  FROM EMP A
  LEFT JOIN EMP B 
    ON A.MGR = B.EMPNO
  LEFT JOIN DEPT D
    ON A.DEPTNO = D.DEPTNO
  LEFT JOIN SALGRADE S
    ON A.SAL BETWEEN S.LOSAL AND S.HISAL
  LEFT JOIN SALGRADE S2
    ON B.SAL BETWEEN S2.LOSAL AND S2.HISAL;

三、总结

1 数据发散

两表关联时，根据关联条件与实际的数据情况，可能会出现多种关联情况
1）1-N：A表中的每条数据可能对应B表中的多条数据
2）N-1：A表中的多条数据对应B表中的一条数据
3）1-1：A表中的一条数据对应B表中的一条数据
4）N-N：A表中的每条数据对应B表中的多条数据，同时B表中的每条数据也对应A表中的多条数据
其中第1和第4这两种，主表关联上次表后数据变多的情况称为数据发散。
数据发散有时是依据事实需求的正常情况，有时是因为逻辑欠缺造成的错误现象。

2 ORACLE中特殊写法

内连接：FROM A , B WHERE A.a = B.b
外连接：左外连接：FROM A , B WHERE A.a = B.b(+) B表为次表
右外连接：FROM A , B WHERE A.a(+) = B.b A表作为次表
笛卡尔连接(交叉连接)：FROM A , B

3 表连接和子查询选择

1 若查询时用于最终展示的字段来源于多个数据源，必须使用表连接

2 若查询时用于最终展示的字段只来源于一个数据源，同时查询过程中用于筛选判断的字段来源于多个数据源，可以使用表连接或子查询

4 表连接和集合运算对比

相似点：都是将数据拼接到一起，可以将拼接结果作为一个新的数据源使用
区别点：集合运算是在纵向上将多行数据拼在一起

​ 表连接时在横向上将多列数据拼接在一起

5 左外连接，条件写在WHERE和ON中区别

写在WHERE中，是先做连接，再对结果做筛选 先连后筛

​ 表连接时在横向上将多列数据拼接在一起

5 左外连接，条件写在WHERE和ON中区别

写在WHERE中，是先做连接，再对结果做筛选 先连后筛
写在ON中，是先对其中的数据源做一次筛选，以筛选结果作为数据源再进行连接 先筛后连