Oracle 高级查询：内外连接、子查询、exists 查询、rownum 行号、集合运算, Sql 执行顺序

灰太狼 2022-01-26 12:37 534阅读 0赞

**目录**

Sql 执行顺序

笛卡尔积 与 内连接

左、右外连接

子查询

exists 子查询

rownum 行号 与 分页

集合运算

--------------------

[~~~ 准备员工表与部门表测试数据][Link 1]

# Sql 执行顺序 #

1、SELECT 查询语句语法顺序如下：

SELECT DISTINCT <select_list> FROM <left_table> <join_type> JOIN <right_table>
    ON <join_condition> WHERE <where_condition>
    GROUP BY <group_by_list> HAVING <having_condition>
    ORDER BY <order_by_condition>

2、SELECT 查询语句执行顺序如下：

FROM <表名> # 选取表，将多个表数据通过笛卡尔积变成一个表。
    ON <筛选条件> # 对笛卡尔积的虚表进行筛选
    JOIN <join, left join, right join...> <join表> # 指定join，用于添加数据到on之后的虚表中，例如left join会将左表的剩余数据添加到虚表中
    WHERE <where条件> # 对上述虚表进行筛选
    GROUP BY <分组条件> # 分组
    <SUM()等聚合函数> # 用于having子句进行判断，在书写上这类聚合函数是写在having判断里面的
    HAVING<分组筛选> # 对分组后的结果进行聚合筛选
    SELECT <返回数据列表> # 返回的单列必须在 group by子句中，聚合函数除外.
    DISTINCT # 数据除重
    ORDER BY <排序条件> # 排序

3、Sql 示例：

SELECT t.mgr, t.job, count(1)
      FROM emp t
      left join dept t2
        on t.deptno = t2.deptno
     where t.sal > 1000
     group by t.mgr, t.job
    having count(1) > 1
     order by t.job;

# 笛卡尔积 与 内连接 #

1、两张表数据的乘积，实际基本用不上，只是助于理解。

select * from emp;--查询所有员工
    select * from dept;--查询所有部门
    select * from emp,dept;--多表查询，没加 where 条件，此时就是笛卡尔积

![20190529070340891.gif][]

**隐式内连接**：在笛卡尔积的基础上加上 where 条件后就是隐式内连接。（比较常用的操作）

select * from emp;--查询所有员工
    select * from dept;--查询所有部门
    --笛卡尔积加上 where 条件就是隐式内连接。查询员工及其所在的部门信息
    select * from emp,dept where emp.deptno = dept.deptno;
    --查询员工姓名及其所在部门的位置
    select e1.ename,d1.loc from emp e1,dept d1 where e1.deptno = d1.deptno;

**显式内连接：**select \* from 表1 inner join 表2 on 连接条件；--inner 关键字可以省略。显示内连接必须用 on，而不是 where

--查询员工姓名及其所在部门的位置
    select e1.ename,d1.loc from emp e1 inner join dept d1 on e1.deptno = d1.deptno;
    --查询员工姓名及其所在部门的名称
    select e1.ename,d1.dname from emp e1 join dept d1 on e1.deptno = d1.deptno;--必须是 on，而不是 where

![20190529071643157.gif][]

完整示例：[人员信息同步视图.sql][.sql]

# 左、右外连接 #

1、left outer join ：返回左表中的所有数据，如果右表没有对应的记录，则右表记录显示为空。outer 可省略。

2、right outer join ：返回右表中的所有数据，如果左表没有对应的记录，则左表记录显示为空。outer 可省略。

select * from emp;--查询所有员工
    select * from dept;--查询所有部门
    
    --查询每个部门的员工，同时没有员工的部门也要全部显示。外连接必须用 on 而不是 where
    select * from dept d1 left outer join emp e1 on d1.deptno = e1.deptno;
    select * from emp e1 right outer join dept d1  on d1.deptno = e1.deptno;

3、上面是 msyql 与 oralce 的通用写法，Oracle 中还可以使用 (+) 来进行操作。

--查询每个部门的员工，同时没有员工的部门也要全部显示。在隐式内连接的基础上使用 (+) 表示如果没有数据对应时，就显示为空
    select * from dept d1,emp e1 where d1.deptno = e1.deptno(+);

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdteDE5OTMzMjg_size_16_color_FFFFFF_t_70][]

4、查询部门编号 = 10 的部门信息及其下面的员工信息

-- 查询部门编号 = 10 的部门信息及其下面的员工信息, on 条件会结果放入到虚表中，然后使用 where 条件进行结果过滤
    SELECT  T.*,t2.* FROM dept t left join emp t2
      on t.deptno = t2.deptno where t.deptno = 10;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdteDE5OTMzMjg_size_16_color_FFFFFF_t_70 1][]

5、查询所有部门信息，同时查询部门编号为 30 的部门下的员工中工资超过 5k 的员工：

-- tips：左连接时 on 条件过滤的右表的数据，左表数据全部返回；右连接时，on 条件过滤的左表的数据，右表数据全部返回
    -- 要想过滤整个查询结果，则需要再加 where 条件对虚表结果进行过滤
    SELECT T.*, t2.* FROM dept t left join emp t2
      on t.deptno = t2.deptno and t.deptno = 30 and t2.sal > 5000
      order by t.deptno;

6、示例：

-- BAS_PERSON_EXT、BAS_PERSON_MONTH 表的主键 per_id 关联 BAS_PERSON_INFO 表的主键 per_id
    -- 查询所有人员的基本(info)信息，人员有扩展(ext)信息时也一起返回，人员有月数(month)信息时也一起返回.
    select A.PER_ID,A.IDEN_TYPE_ID,A.IDEN_TYPE_CODE,A.IDEN_TYPE_NAME,
           B.SALA_GR_ID,B.SALA_GR_CODE,B.SALA_GR_NAME,
           M.M_POS_SALA, M.M_GRAD_SALA,M.M_NATION_REG_SUB
      from BAS_PERSON_INFO A
      left join BAS_PERSON_EXT B
        on A.Per_Id = B.Per_Id
      left join BAS_PERSON_MONTH M
        on A.PER_ID = M.per_id;

7、完整示例：[人员信息同步视图.sql][.sql]

# 子查询 #

1、在 SELECT、UPDATE、DELETE 语句内部可以出现 SELECT 语句，内部的 SELECT 语句结果可以作为外部语中条件子句的一部分，也可以作为外部查询的临时表。子查询分为单行子查询、多行子查询。

2、子查询语句用括号括起来，一般嵌入位置可以是：where 后面、from 后面、select 后面。

<table> 
 <tbody> 
  <tr> 
   <td style="width:210px;"><span style="color:#f33b45;">单行子查询</span>：不向外部返回结果，或者只返回一行结果。<br> &nbsp;</td> 
   <td style="width:692px;"> <p>select * from emp where sal = (select max(sal) from emp);--查询工资最高的员工信息</p> <p>1、如果内部查询不返回任何记录，则外部条件中字段 sal 与 NULL 比较永远为 false，外部查询不返还任何结果。<br> 2、在单行子查询中外部查询可以使用 =、&gt;、&lt;、≥、&lt;=、&lt;&gt; 等比较运算符<br> 3、内部查询返回的结果类型必须与外部查询条件中的字段 ( sal ) 匹配。<br> 4、如果内部查询返回多行结果则会报错</p> </td> 
  </tr> 
  <tr> 
   <td style="width:210px;"><span style="color:#f33b45;">多行子查询</span></td> 
   <td style="width:692px;">向外部返回零行、一行或者多行结果。</td> 
  </tr> 
 </tbody> 
</table>

**示例一：查询工资小于员工 "SCOTT" 的所有员工信息**  
select t.\*,t.rowid from emp t where t.sal < (select sal from emp where ename='SCOTT') ;

**示例二：查询每个部门最低薪水的员工姓名，薪水，以及他所属的部门名称**

--查询每个部门最低薪水的员工姓名，薪水，以及他所属的部门名称。
    select deptno,min(sal) as minsal from emp group by deptno;--第一步分组查询各部门的最低薪水。为最低薪水设置别名
    
    --使用子查询查询部门中等于最低薪水的员工，放在 from ..的位置作为子表再次查询
    select * from emp e1,(select deptno,min(sal) as minsal from emp group by deptno) t1 where e1.deptno = t1.deptno and e1.sal = t1.minsal;
    --设置需要显示的最终字段值
    select e1.ename, e1.sal,d1.dname from emp e1,(select deptno,min(sal) as minsal from emp group by deptno) t1,dept d1
    where e1.deptno = t1.deptno and e1.sal = t1.minsal and e1.deptno = d1.deptno;

**示例三：查询所有员工数据以及员工所属的部门名称及地址**

--查询所有员工数据以及员工所属的部门名称及地址    ·   使用 select 子查询
    select t1.*,
     (select t2.dname from dept t2 where t1.deptno = t2.deptno) as dname,
     (select t2.loc from dept t2 where t1.deptno = t2.deptno) as loc
     from EMP t1 order by t1.empno;
    
    --查询所有员工数据以及员工所属的部门名称及地址    ·   使用左外连接
    select t1.*,t2.dname,t2.loc from EMP t1 left outer join dept t2 on t1.deptno = t2.deptno order by t1.empno;

# exists 子查询 #

**1、格式与示例**：

<table> 
 <tbody> 
  <tr> 
   <td colspan="2">格式1：select cloumn1,cloumn2... from tableName where<span style="color:#f33b45;"> exists ( select 子查询)。</span><span style="color:#f33b45;">如果子查询语句有结果，则返回 true，否则返回 false.</span></td> 
  </tr> 
  <tr> 
   <td>格式2：select cloumn1,cloumn2... from tableName where<span style="color:#f33b45;"> not exists ( select 子查询)。</span><span style="color:#f33b45;">如果子查询语句没有结果，则返回 true，否则 false.</span></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>select * from dept d1 where <span style="color:#f33b45;">exists</span>(select * from emp e1 where e1.deptno = d1.deptno); --查询有员工的部门信息</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td> <p>-- <span style="color:#f33b45;">如果不存在用户 "KING"，或者存在用户 "SCOTT"，则返回 1</span><br> select 1 from dual t1 where&nbsp; <span style="color:#f33b45;">not exists</span> (select 2 from emp t2 where t2.ename='KING' ) or <span style="color:#f33b45;">exists </span>(select 3 from emp t2 where t2.ename='SCOTT' );</p> </td> 
   <td></td> 
  </tr> 
 </tbody> 
</table>

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdteDE5OTMzMjg_size_16_color_FFFFFF_t_70 2][]

<table> 
 <tbody> 
  <tr> 
   <td>dept 表的第一条数据会逐个对比 emp 表中的所有数据，只要有一条能匹配上，则 exists 结果为 true，就会返回，部门信息就会显示，否则不显示；然后 dept 表的第二条数据开始逐个比对 emp 表，以此类推。</td> 
  </tr> 
 </tbody> 
</table>

# rownum 行号 与 分页 #

**rownum 行号概述与注意事项**

1、rownum 是 Oracle 特有的用于表示行号的关键字，行号由系统自动生成，起始值为1，每查询出一条结果，则 rownum 自动加1。通过 rownum  伪列可以限制查询结果集中返回的行数，比如 rownum <=3 表示只取前3条数据，类似 Mysql 的 limit。

2、rownum 因为从1开始，所以不推荐做大于判断，适用做小于等于判断。

select * from emp;--查询所有员工
    select rownum ,e1.* from emp e1;--查询所有员工，同时显示行号
    select * from emp where rownum <=3;--查询前3条数据
    --注意：如下所示是错误的，不会有值。因为 rownum 是从1开始，永远不会大于2，where 条件永不成立
    --因为没有结果查询出来，所以 rownum 也不能加一，永远是1
    select * from emp where rownum >2;

3、再次提醒，查询 SQL 执行顺序：from ...-> where ...->group by...->having ...->select ...-> order by.

<table> 
 <tbody> 
  <tr> 
   <td>select rownum,e1.* from emp e1 order by sal desc; &nbsp;&nbsp; --所以这里的行号是混乱的，并不是由1开始逐个递增</td> 
  </tr> 
 </tbody> 
</table>

--查询薪水最高的3个人
    select * from emp e1 order by sal desc;--第一步先按薪水降序
    select t1.* from (select * from emp e1 order by sal desc) t1 where rownum <=3;--第二步截取前3个人

**rownum 行号分页**

1、rownum 还有一个重要的作用就是分页，Oracle 中分页需要借助子查询。

select * from emp;--查询所有员工
    
    --查询第 5-10 条数据。Oralce 分页需要借助子查询。
    select * from (select rownum r,e1.* from emp e1) t where t.r>=5 and t.r<=10;
    select * from (select rownum r,e1.* from emp e1) t where t.r between 4 and 8;
    
    --按薪水降序之后，然后查询第 6-12 条数据。排序后的分页，需要嵌套两层。
    select * from emp order by sal desc;--第一步降序
    select rownum r,t.* from (select * from emp order by sal desc) t;--第二步设置行号
    --第三步根据内部行号进行分页
    select * from (select rownum r,t.* from (select * from emp order by sal desc) t) t2 where t2.r between 6 and 12;

![20190529142043293.gif][]

[row\_number() over 分组排序分页只需要嵌套1层子查询。][row_number_ over _1]

**rownum 判断数据是否存在**

1、业务代码中，需要根据一个或多个条件，查询是否存在记录，而不关心有多少条记录，比如某个部门是否存在，普遍的 SQL写法如下：

<table> 
 <tbody> 
  <tr> 
   <td>select count(1) from dept t where t.deptno &gt; 30 and t.loc like '%长沙%' ;</td> 
  </tr> 
 </tbody> 
</table>

2、推荐写法如下：

<table> 
 <tbody> 
  <tr> 
   <td> <p>select 1 from dept t where t.deptno &gt; 30 and t.loc like '%长沙%' and rownum &lt;=1; -- Oracle 写法</p> <p>select 1 from dept t where t.deptno &gt; 30 and t.loc like '%长沙%'&nbsp; limit 1; -- Mysql 写法</p> </td> 
  </tr> 
 </tbody> 
</table>

3、SQL 不再使用 `count`，而是改用 limit `1 或者 rownum <= 1`，让数据库查询时遇到一条就返回，不要再继续查找还有多少条了

业务代码中直接判断结果是否非空即可。根据查询条件查出来的条数越多，性能提升的越明显。

# 集合运算 #

1、集合运算就是将两个或者多个结果集组合成为一个结果集，集合运算包括：

<table> 
 <tbody> 
  <tr> 
   <td> <p>INTERSECT(交集)：返回两个查询共有的记录。</p> <p>UNION ALL(并集)：返回各个查询的所有记录，包括重复记录。</p> <p>UNION(并集)：返回各个查询的所有记录，不包括重复记录。</p> <p>MINUS(差集)：返回第一个查询检索出的记录减去第二个查询检索出的记录之后剩余的记录。</p> </td> 
  </tr> 
 </tbody> 
</table>

2、使用集合操作的时候，要注意：1）查询所返回的列数以及列的类型必须匹配，列名可以不同；2）采用第一个集合作为最后的表头；3） order by永远在最后

**union 并集运算**

1、并集运算用于对两个结果进行合并，union 会去重，union all 不会去重。union 左边的结果排在前，右边的结果排在后。

select * from emp;--查询所有员工
    --查询薪水大于 2000，或者部门号为 30 的员工。union 会自动去重
    select * from emp where sal > 2000 union select * from emp where deptno = 30;
    --union all 不会去重
    select * from emp where sal > 2000 union all select * from emp where deptno = 30;
    --虽然这个需求也可以使用 or 关键字来解决，但是 or 只能是在同一张表内，而 union 并集运算可以对不同的表进行运算
    select * from emp where sal >2000 or deptno = 30;

**minus 差集运算**

select * from emp;--查询所有员工
    --查询 1981 年入职的员工，但是不包括总裁（PRESIDENT）和经理（MANAGER）
    select * from emp where to_char(hiredate,'yyyy') = '1981'
    minus
    select * from emp where job = 'PRESIDENT' or job = 'MANAGER';
    
    --虽然本需求使用 and 关键字也可以实现，但是 and 只能是表内操作，而 minus 支持不同表之间求差集
    select * from emp where job != 'PRESIDENT' and job != 'MANAGER' and to_char(hiredate,'yyyy') = '1981';

![20190529144501283.gif][]

**INTERSECT(交集)**

<table> 
 <tbody> 
  <tr> 
   <td> <p>--查询有员工的部门号<br> select t1.deptno from emp t1<br> intersect<br> select t2.deptno from dept t2;</p> </td> 
  </tr> 
 </tbody> 
</table>

**列个数类型不一致解决方式**

1、集合运算注意事项：表与表之间列的个数、类型、顺序必须一致，对不齐的可以使用 null 补齐，否则报错。

--错误类型1：俩表之间列的个数不一致。解决办法是调整好顺序与类型
    select e1.empno,e1.ename,e1.sal from emp e1 where to_char(hiredate,'yyyy') = '1981'
    minus
    select e2.empno,e2.ename from emp e2 where job = 'PRESIDENT' or job = 'MANAGER';
    --错误类型2：俩表之间列的类型/顺序不一致。解决办法是 e2 表缺省的字段可以用 null 值代替
    select e1.empno,e1.ename,e1.sal from emp e1 where to_char(hiredate,'yyyy') = '1981'
    minus
    select e2.empno,e2.sal,e2.ename from emp e2 where job = 'PRESIDENT' or job = 'MANAGER';

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdteDE5OTMzMjg_size_16_color_FFFFFF_t_70 3][]

[Link 1]: https://wangmaoxiong.blog.csdn.net/article/details/90581383#%E5%87%86%E5%A4%87%E5%91%98%E5%B7%A5%E8%A1%A8%E4%B8%8E%E9%83%A8%E9%97%A8%E8%A1%A8%E6%B5%8B%E8%AF%95%E6%95%B0%E6%8D%AE
[20190529070340891.gif]: /images/20220126/2eec1f6f2f7f453495a3cbda0bd27aa5.png
[20190529071643157.gif]: /images/20220126/af4a7409d680401b97f6751bc4dad30d.png
[.sql]: https://gitee.com/wangmx1993/material/blob/master/sql/oracle/%E4%BA%BA%E5%91%98%E4%BF%A1%E6%81%AF%E5%90%8C%E6%AD%A5%E8%A7%86%E5%9B%BE.sql
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdteDE5OTMzMjg_size_16_color_FFFFFF_t_70]: /images/20220126/464e95ee12194067a2387cce19b4a828.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdteDE5OTMzMjg_size_16_color_FFFFFF_t_70 1]: /images/20220126/068e23d268054d1b881326991b5208b5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdteDE5OTMzMjg_size_16_color_FFFFFF_t_70 2]: /images/20220126/83f4162a0e37471cb0148a8eb1af99fb.png
[20190529142043293.gif]: /images/20220126/46362246c8004646ab9d713ac70b74f9.png
[row_number_ over _1]: https://wangmaoxiong.blog.csdn.net/article/details/108242444
[20190529144501283.gif]: /images/20220126/4556eb0786fb4209b275467c0ee0813d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdteDE5OTMzMjg_size_16_color_FFFFFF_t_70 3]: /images/20220126/f9cc45048c5b41cca0601d6fdf59045e.png