Oracle 表连接(内连接、外连接(左连接、右连接)、隐式连接、表并集、表交集 ...

一、内连接 inner join

在内连接中，数据库一般会主动选择数据量少的表作为驱动表，然后循环驱动每一条数据，按照on后面的关联字段，和被驱动表举行匹配，举行数据的拼接。

1. SELECT T1.EMPNO,T1.JOB,T2.DEPTNO,T2.DNAME,T2.LOC
2. FROM EMP T1 --被驱动表
3. INNER JOIN DEPT T2--驱动表
4.       ON T1.DEPTNO = T2.DEPTNO

复制代码

通过部分编号将员工表和部分信息表举行关联连接，得到两表合并之后的数据。
1.and 和 where 的区别

例：查出员工SCOTT所在的部分的部分编号，部分名称和所在地域

1. ---用and过滤
2.     select e.*,t.dname,t.loc
3.     from emp e inner join dept t
4.     on e.deptno=t.deptno
5.     and e.ename = 'SCOTT';
6. ---where过滤
7.     select e.*,t.dname,t.loc
8.     from emp e inner join dept t
9.     on e.deptno=t.deptno
10.     where e.ename = 'SCOTT';

复制代码

and为表连接前的过滤，where为表关联后的过滤
2.内关联 隐式写法（Oracle 专有）

语法：

1. select 查询字段
2.   from 表1，表2
3.   where 关联字段（此处where相当于 on）
4.   and 过滤条件

复制代码

注：虽然在隐式写法中，where 相称于 on ，但在隐式写法中不能将两者做等价使用，隐式写法只允许用where 作为两表的关联条件，用on为错误的写法。

1. ---正确写法
2.   select e.*,t.dname,t.loc
3.      from emp e ,dept t
4.      where e.deptno=t.deptno
5.      and e.ename = 'SCOTT'
6. ---错误写法
7.   select e.*,t.dname,t.loc
8.      from emp e ,dept t
9.      on e.deptno=t.deptno --on 错误
10.      and e.ename = 'SCOTT'

复制代码

3.交叉连接–笛卡尔积式连接

• 注意：关联查询（针对隐式写法），一定要写关联字段，否则会产生笛卡尔积(查询出来的结果会相乘)；
  
• 若两表的数据量大,会造成数据库服务器高负载；
  比如：
  

1.   SELECT * FROM EMP E  cross join  DEPT D ;  -- cross join 显式交叉连接写法
2.   SELECT * FROM EMP E ,DEPT D ;  --隐式交叉连接写法

复制代码

总结一下：

• 我们的内关联求的结果集是两张表的交集（根据关联字段，互相匹配的表现出来）。
  
• 一定要写关联字段，否则会产生笛卡尔积(查询出来的结果会相乘)。
  
• 尺度写法中表之间的关联字段用ON，Oracle专有隐式写法中的表之间的关联字段用WHERE。
  

二、外连接

外连接区别与内连接：
6. 语法结构上，内连接是[INNER] JOIN,左外连接是LEFT [OUTER] JOIN。
7. 结果上，内连接取的是两个表的交集，左外连接取得是主表的所有信息加上从表的信息，主表的数据就算不满足关联条件也可以被查询出来，从表不匹配的表现为空。
8. 主表、从表。按照左连接的语法结构：表1就是主表，表2就是从表。
1. 左连接 left join

语法：

1.     SELECT 要查询的字段/列
2.     FROM 表1 ---主表     
3.     LEFT JOIN 表2 ---从表,     
4.           ON 关联字段
5.           AND 过滤条件;

复制代码

注：
9. 主表的数据就算不满足关联条件也可以被查询出来，也就是说主表的数据会全部展示
10. 从表数据，匹配的正常展示，不匹配的表现为空。

1.     SELECT *
2.     FROM DEPT D --主表
3.     LEFT JOIN EMP E --从表
4.         ON E.DEPTNO = D.DEPTNO;  

复制代码

2. 右连接 right join

语法：

1.     SELECT 要查询的字段/列
2.     FROM 表1 ---从表     
3.     RIGHT JOIN 表2 ---主表,     
4.           ON 关联字段
5.           AND 过滤条件;

复制代码

右连接以表 2 为主表，表现表 2 的所有字段内容，不匹配的则表现为null。

1.     SELECT *
2.     FROM EMP E --从表
3.     RIGHT JOIN  DEPT D --主表
4.           ON E.DEPTNO = D.DEPTNO;  

复制代码

3. 全连接 full join

全外关联 ,没有主从表，两张表互相比较，相同的部分正常表现，差别的部分互相为 NULL。
语法：

1.     SELECT 要查询的字段/列
2.     FROM 表1
3.     FULL JOIN 表2
4.          ON 关联字段
5.     AND 过滤条件;

复制代码

全外连接- -展示两张表的全部数据

1.     SELECT e.*,t.*
2.     FROM EMP e
3.     FULL  JOIN DEPT t
4.           ON e.deptno = t.deptno

复制代码

4. Oracle外连接隐式写法

• 左连接
  

1.   SELECT *
2.   FROM emp e,dept f
3.   where e.deptno=f.deptno(+)  -- (+)在右边，另一侧的emp表就是主表
4.   --AND 过滤条件;  --是表连接完成后的过滤

复制代码

2. 右连接

1.   SELECT *
2.   FROM  emp e,dept f
3.   where e.deptno(+)=f.deptno  -- (+)在哪一侧，另一侧的dept表就是主表
4.   --AND 过滤条件   --是表连接完成后的过滤

复制代码

5. 重点

• 用外连接的时候需要注意：主表跟从表的取舍(要判断好到底哪张表作为主表，哪张表作为从表)。
  
• 在外连接的时候。用and过滤条件不会过滤主表的信息。用where的时候，会过滤掉主表的部分信息。
  
• 在用外连接连接表的时候，大部分都是用and来当做过滤条件 --重点
  

连接查询总结：

• 内连接：取两张表的交集。没有主从表之分
  
• 外连接：
  
  
  
  • a.左外连接：关键字LEFT左边的那个表是主表（有主从表之分），表现主表的所有信息以及从表的关联得上那部分信息，从表不匹配的部分用null补全。
    
  • b.右外连接：关键字RIGHT右边的那个表是主表（有主从表之分），表现主表的所有信息以及从表的部分信息，从表不匹配的部分用null补全
    
  • c.全外连接full：表现两张表的相同的信息，差别的部分互相用null补全，两张表的数据就算有关联不上的也可以被查询出来,会展示出两张表的全部信息。
    
  
  

三、自连接

内连接自关联 与 外连接自关联的差异。
例：通过上级领导编号举行表自连接关联。

• 内连接-表自关联
  

1.   SELECT T1.EMPNO,T2.EMPNO,T2.ENAME--上级领导员工编号，姓名
2.   FROM EMP T1 --取员工的数据
3.   INNER JOIN EMP T2 --取领导数据
4.         ON T1.MGR = T2.EMPNO  --用内连接会过滤掉了董事长那条数据

复制代码

2. 外连接-表自关联

1.   SELECT T1.EMPNO,T2.EMPNO,T2.ENAME--上级领导员工编号，姓名
2.   FROM EMP T1 ---取员工的数据
3.   LEFT JOIN EMP T2 ---取领导数据   
4.        ON T1.MGR = T2.EMPNO   --改为左连接可得到董事长那条数据

复制代码

四、表并集 UNION

1. UNION ALL

在emp表中共有14条数据，表全并集后，得到28条数据。

1. SELECT * FROM EMP  --注意上下2个SQL结果集字段顺序，数量要保持一致
2.   UNION ALL
3. SELECT * FROM EMP;

复制代码

2. UNION

1. SELECT * FROM EMP
2.   UNION --拼接上下2个SQL结果集，会进行去重排序（默认是升序排序);
3. SELECT * FROM EMP;

复制代码

3. UNION ALL 和 UNION 区别

• UNION ALL 简单拼接上下2个SQL结果集，不会举行去重排序。 --效率更高
  
• UNION 拼接上下2个SQL结果集，会举行去重排序（默认是升序排序); --效率更低
  

注： 上下2个SQL结果集字段次序，数量要保持同等
五、表交集 INTERSECT

表交集 INTERSECT 会返回两个查询中共有的查询记录

1. SELECT DEPTNO FROM EMP
2.   INTERSECT
3. SELECT DEPTNO FROM DEPT;

复制代码

六、表补集 MINUS

表补集 也可以为具有减的功能;

• MINUS 返回第一个查询的记录 减去 第二个查询的记录之后 剩余的记录。
  
• 也可理解为取出第一个记录中有的，但是第二个记录没有的记录;
  

1. --emp表中只有部门（10、20、30），而部门表中有（10、20、30、40），故补集结果为空
2. SELECT DISTINCT DEPTNO FROM EMP
3.   MINUS
4. SELECT DEPTNO FROM DEPT;   
5. --dept部门表中只有部门（10、20、30、40），而emp员工表中有（10、20、30），故补集结果为 40      
6. SELECT DEPTNO FROM DEPT
7.   MINUS
8. SELECT DISTINCT DEPTNO FROM EMP;

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。