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

一、内连接 inner join

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

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

通过部门编号将员工表和部门信息表进行关联连接，得到两表合并之后的数据。

1.and 和 where 的区别

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

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

and为表连接前的过滤，where为表关联后的过滤

2.内关联 隐式写法（Oracle 专有）

语法：

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

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

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

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

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

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

总结一下：

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

二、外连接

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

1. 左连接 left join

语法：

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

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

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

2. 右连接 right join

语法：

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

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

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

3. 全连接 full join

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

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

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

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

4. Oracle外连接隐式写法

1. 左连接

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

2. 右连接

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

5. 重点

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

连接查询总结：

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

三、自连接

内连接自关联 与 外连接自关联的差异。

例：通过上级领导编号进行表自连接关联。

1. 内连接-表自关联

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

2. 外连接-表自关联

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

四、表并集 UNION

1. UNION ALL

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

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

2. UNION

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

3. UNION ALL 和 UNION 区别

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

注： 上下2个SQL结果集字段顺序，数量要保持一致

五、表交集 INTERSECT

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

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

六、表补集 MINUS

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

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

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