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

• 1 记录集的叠加
  • 问题
  • 解决方案
  • 结论
• 2 组合相关的行
  • 问题
  • 解决方案
  • 结论
• 4 INEXISTS 和 INNER JOIN
  • 问题
  • 解决方案
    • IN
    • EXISTS
    • INNER JOIN
  • 结论
• 5 INNER JOINLEFT JOINRIGHT JOIN 和 FULL JOIN 解析
  • 问题
  • 解决方案
    • inner join的特点
    • left join的特点
    • right join的特点
    • full join的特点
• 6 自关联
  • 问题
  • 解决方案
  • 结论
• 7 NOT INNOT EXISTS 和 LEFT JOIN
  • 问题
  • 解决方案
    • not in
    • not exists
    • left join
  • 结论
• 8 外连接中的条件不要乱放
  • 问题
  • 解决方案
• 9 检测两个表中的数据及对应数据的条数是否相同
  • 问题
  • 解决方案
• 10 聚集与内连接
  • 问题
  • 解决方案
  • 结论
• 11 聚集与外连接
  • 问题
  • 解决方案
  • 结论
• 12 从多个表中返回丢失的数据
  • 问题
  • 解决方案
    • full join
    • union all
• 13 多表查询时的空值处理
  • 问题
  • 解决方案

3.1 记录集的叠加

问题

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

解决方案

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

代码语言：javascript复制

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

结论

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

代码语言：javascript复制

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> 

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

---

---

3.2 组合相关的行

问题

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

解决方案

代码语言：javascript复制

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

代码语言：javascript复制

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

代码语言：javascript复制

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

代码语言：javascript复制

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

代码语言：javascript复制

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 分不清楚，下面通过案例来分析一下。

解决方案

代码语言：javascript复制

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;

代码语言：javascript复制

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的特点

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

代码语言：javascript复制

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的特点

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

代码语言：javascript复制

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> 

( )写法：

代码语言：javascript复制

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的特点

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

代码语言：javascript复制

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的特点

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

代码语言：javascript复制

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连接起来即可。

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

代码语言：javascript复制

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;

结论

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

代码语言：javascript复制

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

代码语言：javascript复制

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

代码语言：javascript复制

alter table dept add constraints pk_dept primary key(deptno)

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

not in

代码语言：javascript复制

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

not exists

代码语言：javascript复制

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

left join

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

代码语言：javascript复制

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 外连接中的条件不要乱放

问题

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

代码语言：javascript复制

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

代码语言：javascript复制

 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;

我们来看下返回结果：

代码语言：javascript复制

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

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

那该如何做呢？

解决方案

left join写法

代码语言：javascript复制

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;

( )写法

代码语言：javascript复制

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表中不同的数据

我们先创建一个视图

代码语言：javascript复制

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

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

代码语言：javascript复制

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 语句。

代码语言：javascript复制

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的区别 。

解决方案

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

代码语言：javascript复制

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

结果集：

代码语言：javascript复制

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为空的数据，如下：

代码语言：javascript复制

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的数据

代码语言：javascript复制

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

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

解决方案

full join

代码语言：javascript复制

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

union all

代码语言：javascript复制

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字段 ，有空值 。

数据如下：

代码语言：javascript复制

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> 

我们来看个错误的写法：

代码语言：javascript复制

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的提成进行比较了。

代码语言：javascript复制

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> 

或者

代码语言：javascript复制

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

---

自动驾驶

0 人点赞