Oracle查询优化-03操作多个表

小小工匠

发布于 2021-08-16 15:12:47

3.1K0

发布于 2021-08-16 15:12:47

文章被收录于专栏：小工匠聊架构

3.1 记录集的叠加

问题

要将来自多个表的数据组织到一起，就像将一个结果集叠加到另外一个上面一样。这些表不必有相同的关键字，但是他们对应列的数据类型必须相同。

解决方案

使用union all 把多个表中的行组合到一起。

select ename, deptno
  from emp
 where deptno = 10
union all
select '-----', deptno
  from dept
union all
select dname, deptno from dept;

结论

UNION ALL将多个来源的行组合起来，放到一个结果集中。所有select列表中的项目数和对应项目的数据类型必须要匹配。
UNION ALL会包括重复的项目，如果要筛选掉重复项，可以使用UNION运算符。
如果使用UNION而不是UNION ALL，很可能是为了去除重复项而进行排序操作。在处理大结果集时要记住，使用UNION子句大致相当于下面的查询，对UNION ALL子句的查询结果使用DISTINCT子句

SQL> select distinct deptno
  2    from (select deptno  from dept
  3          union all
  4          select deptno from dept);

DEPTNO
------
    30
    20
    40
    10

SQL> 
SQL> select deptno
  2    from dept
  3  union
  4  select deptno from dept
  5  ;

DEPTNO
------
    10
    20
    30
    40

SQL>

通常，查询中不要使用distinct，除非确定有必要这样做；对于UNION而言也是如初，除非确定有必要，一般使用UNION ALL，而不适用UNION。

3.2 组合相关的行

问题

多表有一些相同的列，或者有些列的值相同，需要通过关联这些列得到结果。

解决方案

select a.ename ,b.dname  from emp a ,dept b where a.deptno = b.deptno and  a.deptno  = 10   ;

select a.ename ,b.dname  from emp a inner  join dept b  on   a.deptno = b.deptno  where a.deptno  = 10 ;

结论

第二种解决方式是利用显示的ＪＯＩＮ子句（inner 关键字可省略），如果希望将联接逻辑关系放在from子句中，而不是在where 子句中，可以使用JOIN子句，这两种方式都符合ANSI标准。

3.4 IN、EXISTS 和 INNER JOIN

问题

先创建一个表EMP2

create table emp2 as
       select ename, job, sal, comm 
         from emp where job = 'CLERK';

要求返回与emp2(ename, job, sal)中数据相匹配的emp(ename, job, sal,deptno)信息

有in , exists 和 inner join 三种写法，为了加强理解，我们来看下三种写法及其对应的执行计划。

解决方案

ORACLE VERSION ： Oracle Database 11g Enterprise Edition Release 11.2.0.4.0

IN

SQL> explain plan  for
  2  select ename, job, sal, deptno
  3    from emp
  4   where (ename, job, sal) in (select ename, job, sal from emp2);

Explained

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 4039873364
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     4 |   260 |     6   (0)| 00:00:01 |
|*  1 |  HASH JOIN SEMI    |      |     4 |   260 |     6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |   546 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMP2 |     4 |   104 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("ENAME"="ENAME" AND "JOB"="JOB" AND "SAL"="SAL")
Note
-----
   - dynamic sampling used for this statement (level=2)

19 rows selected

SQL>

EXISTS

SQL> explain plan  for
  2  select ename, job, sal, deptno
  3    from emp a  where exists (select * from emp2 b
  4    where b.ename= a.ename
  5    and b.job = a.job
  6    and b.sal = a.sal) ;

Explained

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 4039873364
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     4 |   260 |     6   (0)| 00:00:01 |
|*  1 |  HASH JOIN SEMI    |      |     4 |   260 |     6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |   546 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMP2 |     4 |   104 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("B"."ENAME"="A"."ENAME" AND "B"."JOB"="A"."JOB" AND
              "B"."SAL"="A"."SAL")
Note
-----
   - dynamic sampling used for this statement (level=2)

20 rows selected

SQL>

INNER JOIN

因为子查询的join列（emp2.ename ,emp2.job ,emp2.sal）没有重复行，说这个查询可以直接改写为inner join

SQL>   explain plan for
  2    select a.ename,  a.job,  a.sal,  a.deptno
  3    from emp a join emp2 b on
  4    (a.ename = b.ename
  5    and  a.job= b.job
  6    and a.sal =b.sal);

Explained

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 166525280
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     4 |   260 |     6   (0)| 00:00:01 |
|*  1 |  HASH JOIN         |      |     4 |   260 |     6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP2 |     4 |   104 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMP  |    14 |   546 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("A"."ENAME"="B"."ENAME" AND "A"."JOB"="B"."JOB" AND
              "A"."SAL"="B"."SAL")
Note
-----
   - dynamic sampling used for this statement (level=2)

20 rows selected

SQL>

结论

或许与大家想象的不一样，以上三个PLAN中join写法利用了hash join（哈希连接）,其他两种运用的是 hash join semi(哈希半连接) 。说明在这个语句中 in 和 exists的效率是一样的。

所以，在不知道哪种写法高效时应该查看Plan，而不是去记固定的结论。

3.5 INNER JOIN、LEFT JOIN、RIGHT JOIN 和 FULL JOIN 解析

问题

有人对这几种连接方式，特别是left join 和 right join 分不清楚，下面通过案例来分析一下。

解决方案

SQL>CREATE TABLE L AS
SELECT 'left_1' AS str,'1' AS v FROM dual UNION ALL
SELECT 'left_2','2' AS v FROM dual UNION ALL
SELECT 'left_3','3' AS v FROM dual UNION ALL
SELECT 'left_4','4' AS v FROM dual;


/*右表*/
SQL>CREATE TABLE R AS
SELECT 'right_3' AS str,'3' AS v,1 AS status FROM dual UNION ALL
SELECT 'right_4' AS str,'4' AS v,0 AS status FROM dual UNION ALL
SELECT 'right_5' AS str,'5' AS v,0 AS status FROM dual UNION ALL
SELECT 'right_6' AS str,'6' AS v,0 AS status FROM dual;

SQL> select * from l ;

STR    V
------ -
left_1 1
left_2 2
left_3 3
left_4 4

SQL> select * from r;

STR     V     STATUS
------- - ----------
right_3 3          1
right_4 4          0
right_5 5          0
right_6 6          0

SQL>

inner join的特点

该方式返回两表相匹配的数据。

inner join写法：
select l.str, r.str from l inner join r on l.v = r.v order by 1, 2;

where写法：
select l.str, r.str from l, r where l.v = r.v order by 1, 2;

输出：
STR STR
------ -------
left_3 right_3
left_4 right_4

left join的特点

该方式以左表为主表，左表返回所有的数据，右表只返回与左表匹配的数据。

SQL> select l.str, r.str from l left  join r on l.v = r.v order by 1, 2;

STR STR
------ -------
left_1 
left_2 
left_3 right_3
left_4 right_4

SQL>

(+)写法：

SQL> select l.str, r.str from l, r where l.v = r.v(+) order by 1, 2;

STR STR
------ -------
left_1 
left_2 
left_3 right_3
left_4 right_4

SQL>

right join的特点

该方式以右表为主表，右表返回所有的数据，左表只返回与左表匹配的数据。

select l.str, r.str from l right  join r on l.v = r.v order by 1, 2;


(+)写法：
select l.str, r.str from l, r where l.v(+) = r.v order by 1, 2;


SQL> select l.str, r.str from l right  join r on l.v = r.v order by 1, 2;

STR STR
------ -------
left_3 right_3
left_4 right_4
       right_5
       right_6

SQL>  select l.str, r.str from l, r where l.v(+) = r.v order by 1, 2;

STR STR
------ -------
left_3 right_3
left_4 right_4
       right_5
       right_6

SQL>

full join的特点

该方式的左表和右表都返回所有的数据，但只有相匹配的数据显示在同一行，非匹配的行只显示一个表的数据。

SQL> select l.str, r.str from l full  join r on l.v = r.v order by 1, 2;

STR STR
------ -------
left_1 
left_2 
left_3 right_3
left_4 right_4
       right_5
       right_6

6 rows selected

SQL>

注意 FULL JOIN没有（+）写法

3.6 自关联

问题

表emp中有个字段 mgr，是主管的编码（对应于emp.empno）

如何根据这个信息返回主管的姓名呢？

解决方案

自关联，也就是两次查询表emp,分别取不同的别名，这样就可以当做是两个表，后面的任务就是将这两个表 join连接起来即可。

为了便于理解，这里我们使用汉字作为别名，并把相关列一起返回。

select  员工.empno as 员工编码 , 
员工.ename as 员工name,
员工.job as 工作,
员工.mgr as 员工表主管编码,
主管.empno as 主管表主管编码,
主管.ename as 主管姓名
from emp 员工
left join emp 主管 on  (员工.mgr = 主管.empno)
order by 1;

结论

上述的操作，可以理解为我们是在两个不同的数据集中取数据。

create or replace view 员工 as select * from emp ;
create or replace view 主管 as select * from emp ;

select  员工.empno as 员工编码 , 
员工.ename as 员工name,
员工.job as 工作,
员工.mgr as 员工表主管编码,
主管.empno as 主管表主管编码,
主管.ename as 主管姓名
from  员工
left join  主管 on  (员工.mgr = 主管.empno)
order by 1;

3.7 NOT IN、NOT EXISTS 和 LEFT JOIN

问题

有些单位的部门如40中一个员工也没有，只是设置了一个部门名字，如何通过关联查询把这些信息查询出来呢？

解决方案

数据库版本 11.2.0.4.0

alter table dept add constraints pk_dept primary key(deptno)

执行以下SQL并查询执行计划

not in

explain plan for 
select * from dept
where deptno not in (select deptno from emp where deptno is not null);

not exists

explain plan for 
select * from dept 
where not exists (select null from emp where emp.deptno = dept.deptno)

left join

Left join 取出的是左表中所有的数据，其中右表不匹配的就表示左表not in 右表

explain plan for 
select dept.*  from dept
left join emp on emp.deptno = dept.deptno
where emp.deptno is null ;

select * from table(dbms_xplan.display());

结论

三个PLAN应用的都是 MERGE ＪＯＩＮＡＮＴＩ，说明这三种方法的效率是一样的。　

若果想改写，那么就要比对前后的ＰＬＡＮ，根据ＰＬＡＮ来判断并测试哪种方法的效率高，而不是凭借某些结论来碰运气。

3.8 外连接中的条件不要乱放

问题

对于左连语句，见下面的数据

SQL> select l.str, r.str ,r.status from l 
    left  join r 
    on l.v = r.v 
    order by 1, 2;

STR     STR     STATUS
------ -------  ------- 
left_1 
left_2 
left_3 right_3    1
left_4 right_4    0

SQL>

对于L表，4条数据全部返回了，而对于R表，我们如果只需要显示 status=1的部分，该如何写SQL呢？

常见的错误写法，会有人直接在上面的语句中加入条件 status=1

 select l.str, r.str ,r.status from l 
    left  join r 
    on l.v = r.v 
    where r.status=1
    order by 1, 2;

我们来看下返回结果：

STR     STR     STATUS
------ -------  ------- 
left_3 right_3    1

很明显这不是我们想要的数据集。这是很多人写查询或者改查询时常遇到的一种错误，问题在于所加条件的位置及写法。

那该如何做呢？

解决方案

left join写法

select l.str, r.str, r.status
  from l
  left join r
    on (l.v = r.v and r.status = 1)
 order by 1, 2;

(+)写法

select l.str, r.str, r.status
  from l, r
 where l.v = r.v(+)
   and r.status(+) = 1
 order by 1, 2;

3.9 检测两个表中的数据及对应数据的条数是否相同

问题

查找视图V 和 emp表中不同的数据

我们先创建一个视图

create or replace view v  as 
 select * from emp where deptno !=10
 union all 
 select * from emp where ename='SCOTT';

我们可以知道视图V中，SCOTT有两条记录， EMP中有一条

SQL> select * from v where ename='SCOTT';

EMPNO ENAME JOB     MGR HIREDATE       SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7788 SCOTT ANALYST  7566 1987-04-19   3000.00               20
 7788 SCOTT ANALYST  7566 1987-04-19   3000.00               20

SQL> select * from emp where ename='SCOTT';

EMPNO ENAME JOB     MGR HIREDATE       SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7788 SCOTT ANALYST  7566 1987-04-19   3000.00               20

SQL>

比较两个数据集的不同时，通常类似下面的FULL JOIN 语句。

SQL> select v.EMPNO, v.ENAME, emp.empno, emp.ename
  2    from v
  3    full join emp
  4      on v.EMPNO = emp.empno
  5   where (v.EMPNO is null or emp.empno is null);

EMPNO ENAME EMPNO ENAME
----- ---------- ----- ----------
                  7782 CLARK
                  7839 KING
                  7934 MILLER

SQL>

但是这种语句查不到 SCOTT的区别。

解决方案

增加一列显示相同数据的条数，再进行比较。

select v_new.empno,
       v_new.ENAME,
       v_new.cnt,
       emp_new.empno,
       emp_new.ename,
       emp_new.cnt
  from (select v.empno, v.ENAME, count(1) as cnt
          from v
         group by v.empno, v.ENAME) v_new
  full join (select a.empno, a.ENAME, count(1) as cnt
               from emp a
              group by a.empno, a.ENAME) emp_new
    on (v_new.EMPNO = emp_new.empno and v_new.cnt = emp_new.cnt)
 where (v_new.EMPNO is null or emp_new.empno is null);

结果集：

EMPNO ENAME        CNT EMPNO ENAME        CNT
----- ---------- ---------- ----- ---------- ----------
                             7788 SCOTT          1
                             7934 MILLER         1
                             7782 CLARK          1
                             7839 KING           1
 7788 SCOTT          2                  

SQL>

3.10 聚集与内连接

问题

解决方案

结论

3.11 聚集与外连接

问题

解决方案

结论

3.12 从多个表中返回丢失的数据

问题

同时返回多个表中丢失的数据。要从DEPT中返回EMP不存在的行(所有没有员工的部门)需要做外连接。

首先我们在EMP中增加一行deptno为空的数据，如下：

insert into emp
  (empno, ename, job, mgr, hiredate, sal, comm, deptno)
  select 6666, 'XGJ', 'JEDI', null, hiredate, sal, comm, null
    from emp
   where ename = 'KING';

commit ;

此时，我们来看下我们要查询的表中数据：

这时，如果我们使用下面的语句关联查询，就会发现少了emp=6666和 deptno=40的数据

select e.empno, e.ename, b.deptno, b.dname
  from emp e
  join dept b
    on e.deptno = b.deptno;

如果想要返回这两条数据该如何写查询语句呢？下面介绍两种方法

解决方案

full join

select e.empno, e.ename, b.deptno, b.dname
  from emp e
  full join dept b
    on e.deptno = b.deptno;

union all

select e.empno, e.ename, b.deptno, b.dname
  from emp e
  left join dept b
    on e.deptno = b.deptno

union all

select e.empno, e.ename, b.deptno, b.dname
  from emp e
 right join dept b
    on e.deptno = b.deptno
where e.empno is  null ;

在这里不建议使用union ,因为union会去掉重复记录。如果确定需要去掉重复记录再使用。

3.13 多表查询时的空值处理

问题

NULL值永远不会等于或者不等于任何值，也包括null自己，但是需要像计算真实值一样计算可为空列的返回值。

返回所有比ALLEN提成低的员工，提成 comm字段，有空值。

数据如下：

SQL> select * from emp;

EMPNO ENAME JOB     MGR HIREDATE       SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7369 SMITH CLERK  7902 1980-12-17    800.00               20
 7499 ALLEN SALESMAN  7698 1981-02-20   1600.00    300.00     30
 7521 WARD SALESMAN  7698 1981-02-22   1250.00    500.00     30
 7566 JONES MANAGER  7839 1981-04-02   2975.00               20
 7654 MARTIN SALESMAN  7698 1981-09-28   1250.00   1400.00     30
 7698 BLAKE MANAGER  7839 1981-05-01   2850.00               30
 7782 CLARK MANAGER  7839 1981-06-09   2450.00               10
 7788 SCOTT ANALYST  7566 1987-04-19   3000.00               20
 7839 KING  PRESIDENT       1981-11-17   5000.00               10
 7844 TURNER SALESMAN  7698 1981-09-08   1500.00      0.00     30
 7876 ADAMS CLERK  7788 1987-05-23   1100.00               20
 7900 JAMES CLERK  7698 1981-12-03    950.00               30
 7902 FORD ANALYST  7566 1981-12-03   3000.00               20
 7934 MILLER CLERK  7782 1982-01-23   1300.00               10

14 rows selected

SQL>

我们来看个错误的写法：

SQL> select  a.ename , a.comm  from emp a where a.comm  < (select comm from emp a where a.ename = 'ALLEN');

ENAME      COMM
---------- ---------
TURNER      0.00

SQL>

只返回了一条 TURNER的数据，comm有许多空值的并没有被显示出来，原因在于与空值比较后结果还是空值，需要先转换才行

解决方案

使用coalesce函数将null值转换为一个可以用来作为标准值进行比较的真实值。

coalesce函数从值列表中返回第一个非NULL值。当遇到NULL值将其替换为0，这样就可以同ALLEN的提成进行比较了。

SQL> select  a.ename , a.comm  from emp a where   coalesce( a.comm,0 ) < (select comm from emp a where a.ename = 'ALLEN');

ENAME      COMM
---------- ---------
SMITH 
JONES 
BLAKE 
CLARK 
SCOTT 
KING  
TURNER      0.00
ADAMS 
JAMES 
FORD 
MILLER 

11 rows selected

SQL>

或者

select  a.ename , a.comm  from emp a where   nvl( a.comm,0 ) < (select comm from emp a where a.ename = 'ALLEN');

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2017/02/07 ，如有侵权请联系 cloudcommunity@tencent.com 删除

自动驾驶

本文分享自作者个人站点/博客前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体同步曝光计划，欢迎热爱写作的你一起参与！

自动驾驶

登录后参与评论

0 条评论

热度