Oracle 高级查询：内外连接、子查询、exists 查询、rownum 行号、offset rows fetch first/next 分页、集合运算, Sql 执行顺序_oracle fetch first

目录

Sql 执行顺序

连接查询

笛卡尔积

内连接 

左、右外连接

 全连接

自连接（Self Join）

优化说明

子查询

 exists 子查询

rownum 行号 与 分页

rownum 行号分页

rownum 判断数据是否存在

row_number() over 分页

offset rows fetch first/next 分页

集合运算

---

.~~~ 准备员工表与部门表测试数据.

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 <排序条件> # 排序

--统计每个部门的人数，没有员工的部门，统计个数展示为0。count(empno) 忽略统计为 null 的行.
select t.deptno, t.dname, count(empno)
  from (select d.deptno, d.dname, e.empno, e.ename
          from dept d
          left join emp e
            on d.deptno = e.deptno) t
 group by t.deptno, t.dname;

连接查询

1. 内连接（Inner Join）：只返回两张表中满足匹配条件的记录。

2. 左连接（Left (Outer) Join）：返回左表的所有行以及右表中的匹配行，对于右表中未匹配上的行，其列值在结果集中用 NULL 填充。

3. 右连接（Right(Outer) Join）：与左连接类似，只是主表为右表。返回右表的所有行以及左表中的匹配行，对于左表中未匹配上的行，其列值在结果集中用 NULL 填充。

4. 全连接（Full (Outer) Join）：返回两个表的所有行（无论是否匹配），对于左右表中未匹配上的行，其列值在结果集中用 NULL 填充。

5. 自连接（Self Join）：一种独特的连接类型，其中表与自身连接。当你需要比较同一表中的行时，就适合用自连接。

6. 交叉连接（Cross Join）：也称为笛卡尔连接，返回两个表的笛卡尔积，这意味着第一个表的每一行都与第二个表的所有行相结合。比如，若 A，B 两个表的行数分别为 m 和 n，则交叉连接后结果集中的总行数为：m * n。

笛卡尔积

1、笛卡尔集会在下面条件下产生：省略连接条件、连接条件无效、所有表中的所有行互相连接。

2、为了避免笛卡尔集，可以在 WHERE 加入有效的连接条件。在实际运行环境下，应避免使用全笛卡尔全集。

3、该查询将每个员工与每个部门组合在一起，产生一个将每个员工与每个部门配对的列表。这种情况会产生很多错误的数据，另外由于笛卡尔积产生的行数量比较多，所以会影响查询性能（特别是连接表的记录数较高时）。

--多表查询，没加 where 或者 on 条件，此时就是笛卡尔积/交叉连接
--CROSS JOIN 可以省略
select * from emp CROSS JOIN dept;
select * from emp, dept;

内连接 

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

--笛卡尔积加上 where 条件就是隐式内连接。
--只要员工的deptno等于部门数据的deptno，则两者就可以返回，比如共有20个员工，5个部门，其中有15个员工匹配了其中4个部门，则结果就是15条数据。
--没有分配到部门的员工无法展示
select e1.*,d1.* from emp e1,dept d1 where e1.deptno = d1.deptno;
 
-- 查询员工工资大于部门平均工资的人
select t1.empno, t1.ename, t1.sal, t2.dept_avg_sal
  from emp t1,
       (select e.deptno, round(avg(nvl(e.sal, 0)), 2) dept_avg_sal
          from emp e
         group by e.deptno) t2
 where t1.deptno = t2.deptno
   and t1.sal > t2.dept_avg_sal;

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

--查询员工姓名及其所在部门的位置
--只要员工的deptno等于部门数据的deptno，则两者就可以返回，比如共有20个员工，5个部门，其中有15个员工匹配了其中4个部门，则结果就是15条数据。
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

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

不等值连接

--查询员工信息：员工号 姓名 月薪工资级别
select e.empno, e.ename, e.sal, s.grade from emp e, salgrade s
 where e.sal between s.losal and s.hisal;

左、右外连接

1、left outer join on：返回左表的全部数据，以及右表中满足条件数据，如果右表没有对应的记录，则右表相关列结果为空。outer 可省略。

2、right outer join on：返回右表的全部数据，以及左表中满足条件的数据，如果左表没有对应的记录，则左表相关列结果为空。outer 可省略。

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

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

--查询每个部门的员工，同时没有员工的部门也要全部显示。在隐式内连接的基础上使用 (+) 表示如果没有数据对应时，就显示为空.
-- (+)的位置: 以左外连接为例，因为左表需要返回更多的记录，右表就需要 "加上" 更多的记录，所以在右表的链接条件上加上 "(+)".
select * from dept d1, emp e1 where d1.deptno(+) = e1.deptno(+);

连表查询时，条件放在on后边还是放在where后边的？

1、对于 left join(左外连接)，不管 on 后面跟什么条件，左表的数据全部会查出来，因此要想过滤结果必须把条件放到where后面，右外连接也是同理。

2、对于 inner join(内连接)，满足on后面的条件表的数据才能查出，可以起到过滤作用，也可以把条件放到where后面。

3、左连接时 on 条件过滤的右表的数据，左表数据全部返回；右连接时，on 条件过滤的左表的数据，右表数据全部返回。要想过滤整个查询结果，则需要再加 where 条件对虚表结果进行过滤。

-- 查询部门编号 = 10 的部门信息及其下面的员工信息, on 条件会结果放入到虚表中，然后使用 where 条件进行结果过滤
SELECT  T.*,t2.* FROM dept t left join emp t2
  on t.deptno = t2.deptno where t.deptno = 10;
 
--按部门统计员工人数
select d.deptno 部门号, d.dname 部门名称, count(e.empno) 人数  from emp e right join dept d on e.deptno = d.deptno
 group by d.deptno, d.dname order by d.deptno;
 
-- 查询所有部门信息，同时查询部门编号为 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;

多表连接示例1：

-- 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;

多表连接示例2：

-- 补充背景：emp 表新加 flow_id 列，关联 work_flow 表的主键，is_end 表示流程是否终审(1是2否)
create table WORK_FLOW
(
  flow_id     NUMBER not null,
  is_end      NUMBER(1) not null
);
comment on column WORK_FLOW.flow_id  is '主键ID';
comment on column WORK_FLOW.is_end  is '是否终审(1是2否)';
alter table WORK_FLOW  add constraint PK_WORK_FLOW primary key (FLOW_ID);
 
-- 多表连接示例1：查询终审且有上级领导的员工信息，同时展示员工的部门名称
SELECT e.*, e2.ename as mgr_name, w.is_end, d.dname
  FROM emp e
 inner join emp e2
    on e.mgr = e2.empno
 inner join work_flow w
    on e.flow_id = w.flow_id
   and w.is_end = 1
  left join dept d
    on e.deptno = d.deptno;
 
-- 多表连接示例2：查询终审且工资大于4.5k的员工信息，同时展示员工的部门名称以及领导的姓名
SELECT e.*, e2.ename as mgr_name, w.is_end, d.dname
  FROM emp e
 inner join work_flow w
    on e.flow_id = w.flow_id
   and w.is_end = 1
 left join emp e2
    on e.mgr = e2.empno
  left join dept d
    on e.deptno = d.deptno
  where e.sal > 4500;

 全连接

1、Full [outer] Join on 返回两个表的所有行（无论是否匹配），对于左、右表中未匹配上的行，其列值在结果集中用 NULL 填充。outer 可以省略。

-- 查询全部部门信息与全部员工信息，
-- 没有员工的部门信息会展示，没有分配部门的员工也会展示
select d1.*,e1.* from dept d1 FULL join emp e1 on d1.deptno = e1.deptno;

自连接（Self Join）

1、一种独特的连接类型，其中表与自身连接。当你需要比较同一表中的行时，就适合用自连接。 

--查询员工及其领导姓名(没有领导的员工不会展示)
SELECT T1.*,t2.ename 经理名称 FROM emp t1 join emp t2 on t1.mgr = t2.empno order by t1.ename;
--查询全部员工及其领导姓名(没有领导的员工也会展示)
SELECT T1.*,t2.ename 经理名称 FROM emp t1 left join emp t2 on t1.mgr = t2.empno order by t1.ename;

优化说明

1、为了确保 SQL 查询高效运行，请考虑以下优化技术：

• 索引：在连接条件的匹配列上使用索引，这样可以有效提升匹配查询速度。

• 连接类型：选择适当的连接类型以尽量减少返回的行数。

• 提前过滤：在连接之前应用 WHERE 子句以减小结果集的大小。

• 子查询最低优先级：在兼容的情况下，尽量选用 EXISTS 或 IN 子句而非子查询。

• 连接顺序：连接中表的顺序会影响性能。较小的表或具有更多过滤器的表通常应该首先连接。

• 避免不必要的列：只选择必要的列以减少数据负载。

2、在使用连接时，要注意这些常见的陷阱：

• 笛卡尔积：如果忘记 ON 子句，结果将会导致笛卡尔积，从而创建一个过大的结果集。

• 连接类型错误：使用错误的连接类型将会返回不期望的结果。

• 空值：在连接可能包含空值的列时要小心，因为它们可能会影响结果集。

• 性能问题：连接使用不当，如过度使用嵌套子查询，将会导致性能问题。

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

子查询

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

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

3、子查询(内查询) 在主查询之前一次执行完成，子查询的结果被主查询使用 (外查询)。一般先执行子查询，再执行主查询，但相关子查询例外。

4、理论上多表查询/连接查询比子查询性能更好，如果多表查询能替代的，优先使用多表查询/连接查询。

--查询工资小于员工 "SCOTT" 的所有员工信息
select t.*,t.rowid from emp t where t.sal < (select sal from emp where ename='SCOTT') ;
 
--查询工资大于自己部门平均工资的员工信息
--相关子查询：将主查询中的某些值作为参数传递给子查询
select e.empno,
       e.ename,
       e.sal,
       (select trunc(avg(sal), 2) from emp where deptno = e.deptno) avgsal
  from emp e where e.sal > (select avg(sal) from emp where deptno = e.deptno);
 
--查询每个部门最低薪水的员工姓名，薪水，以及他所属的部门名称。
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 t.deptno,
       t.dname,
       t.loc,
       (SELECT count(1) FROM emp e where t.deptno = e.deptno) as count_
  FROM dept t;

-- 子查询 配合删除
DELETE FROM emp t where t.empno in (SELECT T2.Empno FROM emp t2 where t2.deptno = 10);
 
-- 子查询配合新增插入，
-- 当表中不存在待插入数据的主键ID时才新增，否则不新增
insert into emp
  (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
  SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
    FROM (SELECT '1234' as EMPNO,
                 '约翰逊' as ENAME,
                 '总统' as JOB,
                 null as MGR,
                 to_date('2018-05-06', 'yyyy-mm--dd') as HIREDATE,
                 87889.89 as SAL,
                 887.98 as COMM,
                 10 as DEPTNO
            from dual) T
   where (SELECT count(1) FROM emp t2 where t2.empno = t.empno) <= 0;

-- 子查询配合更新
-- 通过 BAS_AGENCY_INFO 单位基本信息表 更新 ele_agency 单位要素表
update ele_agency b
   set (b.ele_name, b.mof_dep_id, b.agency_kind_id) =
       (select agency_name, mof_dep_id, agency_type_id
          from BAS_AGENCY_INFO a
         where b.is_deleted = 2
           and b.version = '2023rcdtwh'
           and b.ele_code = a.agency_code
           and b.mof_div_code = a.mof_div_code
           and rownum <= 1)
 where exists (select 1
          from BAS_AGENCY_INFO a
         where b.is_deleted = 2
           and b.version = '2023rcdtwh'
           and a.agency_code = b.ele_code
           and b.mof_div_code = a.mof_div_code);
​

 exists 子查询

1、格式与示例：

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.

--查询薪水最高的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个人
 
--限制每次delete数据的记录条数(更新操作也是同理)
delete from 表名 where 待删除数据的条件 and rownum<=100000 ;

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;
 
--排序后再分页
select *
  from (select rownum r, t.* from (select * from emp order by sal desc,empno) t) t2
 where t2.r between 6 and 12;
 
--推荐方式：第二层除了查询行号外，也可以加上分页的结束位置，减少第三层时的数据量
select *
  from (select rownum r, e1.*
          from (select * from emp order by sal desc, empno) e1
         where rownum <= 12)
 where r >= 6;

rownum 判断数据是否存在

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

2、推荐写法如下：

3、SQL 不再使用 count，而是改用 limit 1 或者 rownum <= 1，让数据库查询时遇到一条就返回，不要再继续查找还有多少条了，业务代码中直接判断结果是否非空即可。根据查询条件查出来的条数越多，性能提升的越明显。

row_number() over 分页

row_number() over 分组排序分页只需要嵌套1层子查询。

select t2.*
  from (select row_number() over(order by t1.sal desc, empno) as r, t1.*
          from emp t1) t2
 where t2.r between 6 and 12;

offset rows fetch first/next 分页

1、从Oracle 12c开始，可以使用SQL标准的 OFFSET x ROWS FETCH FIRST/NEXT y ROWS ONLY 来对结果进行分页。

x：表示需要跳过的行数(偏移量，比如 5 表示跳过前5行结果)。
y：表示想从剩余的记录（在跳过x行后）中选取出来的行数，比如 7 表示取后续结果中的7行数据。

-- 从Oracle 12c开始，可以使用SQL标准的 OFFSET x ROWS FETCH FIRST/NEXT y ROWS ONLY 来对结果进行分页。
-- x：表示需要跳过的行数(偏移量，比如 5 表示跳过前5行结果)。 
-- y：表示想从剩余的记录（在跳过x行后）中选取出来的行数，比如 7 表示取后续结果中的7行数据。
SELECT e.*  FROM emp e ORDER BY e.ename OFFSET 5 ROWS FETCH NEXT 7 ROWS ONLY;

集合运算

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

2、使用集合操作的时候，要注意：

• 查询所返回的列数以及列的类型必须匹配，列名可以不同；
• 采用第一个集合作为最后的表头；
• 采用最后一个集合上的 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';

INTERSECT(交集)

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

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

--错误类型1：俩表之间列的个数不一致。解决办法是调整好顺序与类型
-- ORA-01789: 查询块具有不正确的结果列数.
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 值代替
-- ORA-01790: 表达式必须具有与对应表达式相同的数据类型
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';