SQL语句之多表操作

于 2017-09-22 23:41:39 发布
阅读量1.8k 收藏 1
点赞数 1
分类专栏： oracle-11g 文章标签： sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/sungsasong/article/details/78067969
版权

在讲多表查询前，先做一个与多表查询无关的需求。
假设在B表中有这样一列id

+------+
| id   |
+------+
|    1 |
|    2 |
|    1 |
|    3 |
|    4 |
|    5 |
+------+

请根据B表查询为id列标明序号，并显示如下：

+------+----------+
| id   | sequence |
+------+----------+
|    1 |        0 |
|    2 |        2 |
|    1 |        0 |
|    3 |        3 |
|    4 |        4 |
|    5 |        5 |
+------+----------+

其实这就是一个简单的排序操作，就是对列进行排序。在Oracle中以rownum来表示列的顺序的。
如下，创建一张表B1并插入数据。

SQL> create table B1(id number(1));

Table created.

SQL> begin
  2  for i in 1..5
  3  loop
  4  insert into B1 values(i);
  5  end loop;
  6  commit;
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL> insert into B1 values(2);

1 row created.

SQL> insert into B1 values(5);

1 row created.

SQL> select * from B1;

    ID
----------
     1
     2
     3
     4
     5
     2
     5

7 rows selected.

SQL>

---通过rownum来现实序列号

SQL> select rownum,id from B1;

    ROWNUM     ID
---------- ----------
     1      1
     2      2
     3      3
     4      4
     5      5
     6      2
     7      5

7 rows selected.

SQL>

当然，还有一种方法，这种方法自己下去可以思考一下。
**

现在开始时多表操作示例

**

一、union all

union all通常会用在多种场合，作用就是合并数据集
示例：有两张表A和B ，数据如下：

SQL> select * from a1; 

    ID
----------
     1
     2
SQL> select * from c1;

    ID
----------
     3
     4
     1

使用union all 后的数据集为如下：

**SQL> select * from a1
  2  union all
  3  select * from c1;

    ID
----------
     1
     2
     3
     4
     1

SQL> **

发现，union all将会将两个表的数据集合并起来。但是注意，两张表的数据类型要一致。如果不一致，将会发生以下ORA-01790错误：
ORA-01790: expression must have same datatype as corresponding expression
2、空字符串
空字串本身就是varchar2()数据类型，所以它与Null是不同的。null可以是任意的数据类型。

二、UNION 和OR

通常，在查询中，我们会在条件中使用or来操作表。来取多个表中的合集。通常情况下，我们会将or改写为union语句。
示例，我们创建一张表emp2，并在ename和empno 列上创建索引。

SQL> create table emp2
  2  as
  3  select * from emp;

Table created.

SQL> create index idx_emp2_ename on emp2(ename);

Index created.

SQL> create index idx_emp2_empno on emp2(empno);

Index created.

SQL>

需求，查找empno为7788和ename为SCOTT的姓名，工资和雇佣日期。

-------------首先使用or作为筛选条件查询---------------------
SQL> select ename,sal,hiredate
  2  from emp2
  3  where empno=7788 or ename=upper('scott');

ENAME         SAL HIREDATE
---------- ---------- ---------
SCOTT        3000 19-APR-87
-----------下面使用union作为连接条件查询----------------------
SQL> select ename,sal,hiredate from emp2
  2  where empno=7788
  3  union
  4  select ename,sal,hiredate from emp2
  5  where ename=upper('scott');

ENAME         SAL HIREDATE
---------- ---------- ---------
SCOTT        3000 19-APR-87

SQL>

以上的示例当中，我们发现，查询到的结果是一样的，但是如果此处使用union all那么得到的结果将会是错误的。如下：

SQL> select ename,sal,hiredate from emp2
  2  where empno=7788
  3  union all
  4  select ename,sal,hiredate from emp2
  5  where ename=upper('scott');

ENAME         SAL HIREDATE
---------- ---------- ---------
SCOTT        3000 19-APR-87
SCOTT        3000 19-APR-87

SQL>

注意1：发现结果多了一行，所以使用union将会自动去掉重复的行。
注意2：使用union 和使用or的查询代价将会是不同的。我们可以通过查看执行计划来查看

此处为了消除bitmap convert的影响，先设置参数

SQL> alter session set "_b_tree_bitmap_plans"=false;

Session altered.

先查看使用or时的执行计划：

SQL> set timing on;
SQL> explain plan for
  2  select ename,sal,hiredate from emp2
  3  where empno=7788 or ename=upper('scott');

Explained.

Elapsed: 00:00:00.14
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2941272003

--------------------------------------------------------------------------
| Id  | Operation     | Name | Rows  | Bytes | Cost (%CPU)| Time  |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |  |     2 |    44 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP2 |     2 |    44 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("EMPNO"=7788 OR "ENAME"='SCOTT')

13 rows selected.

Elapsed: 00:00:00.27
SQL>

发现此处进行的是全表扫描，我们知道，我们先前已经在ename,empno列上创建了索引，但是依然此处走的是全表扫描。

使用union 作为筛选条件的时候

SQL> explain plan for
  2  select ename,sal,hiredate from emp2
  3  where empno=7788
  4  union
  5  select ename,sal,hiredate from emp2
  6  where ename=upper('scott');

Explained.

Elapsed: 00:00:00.10
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4161404436

--------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |            |     2 |    40 |     6  (34)| 00:00:01 |
|   1 |  SORT UNIQUE                  |            |     2 |    40 |     6  (34)| 00:00:01 |
|   2 |   UNION-ALL               |            |       |       |        |          |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| EMP2           |     1 |    22 |     2   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN              | IDX_EMP2_EMPNO |     1 |       |     1   (0)| 00:00:01 |
|   5 |    TABLE ACCESS BY INDEX ROWID BATCHED| EMP2           |     1 |    18 |     2   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN              | IDX_EMP2_ENAME |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("EMPNO"=7788)
   6 - access("ENAME"='SCOTT')

19 rows selected.

Elapsed: 00:00:00.11
SQL>

此处我们发现，使用Union的时候检索数据的时候走的是索引，并且查询的时间也稍微少了一点。这个表中只有十几行数据，但是如果是上万上百万条数据，那么查询的时间就会明显变慢很多。
但是，有的时候，union的去重功能可能会出现错误。
列如如下的数据：
查询mgr为7698和job为salesman的员工的部门号和编号：

SQL> select empno,deptno from emp2 where mgr=7698 order by 1;

     EMPNO     DEPTNO
---------- ----------
      7499     30
      7521     30
      7654     30
      7844     30
      7900     30

Elapsed: 00:00:00.01
SQL> select empno,deptno from emp2 where job=upper('salesman') order by 1;

     EMPNO     DEPTNO
---------- ----------
      7499     30
      7521     30
      7654     30
      7844     30

Elapsed: 00:00:00.00
SQL>

发现总共有五行数据。所以如果使用or作为筛选条件的话，那么应该是五行数据。

SQL> select deptno from emp2
  2  where mgr=7698 or job=upper('salesman') order by 1;

    DEPTNO
----------
    30
    30
    30
    30
    30

Elapsed: 00:00:00.00
SQL>

但是如果此时使用union作为筛选条件，将会出现以下的结果：

SQL> select deptno from emp2
  2  where mgr=7698
  3  union
  4  select deptno from emp2
  5  where job=upper('salesman')
  6  order by 1;

    DEPTNO
----------
    30

Elapsed: 00:00:00.00
SQL>

哟呵，我们发现去重了。只剩下一行数据了。
以上示例得出：
1>不仅两个数据集之间的结果会去重，单个数据集重复的结果也会被去重。
2>使用union作为筛选条件，得到的数据可能与预期的结果不一致。
对于上面mgr和job以union作为筛选条件的语句可以模拟成为下面这样：

SQL> select distinct deptno
  2  from
  3  (
  4  select deptno from emp2 where mgr=7698
  5  union all
  6  select deptno from emp2 where job=upper('salesman')
  7  )
  8  order by 1;

    DEPTNO
----------
    30

Elapsed: 00:00:00.11
SQL>

问题：既然像这种去重与预期结果不一致的数据集我们还可以用union改写吗？答案是肯定的。
可以在加入一项empno唯一列来消除去重的这种影响，如下：

SQL> select deptno
  2  from 
  3  (
  4  select empno,deptno from emp2 where mgr=7698
  5  union
  6  select empno,deptno from emp2 where job=upper('salesman')
  7  )
  8  order by 1;

    DEPTNO
----------
    30
    30
    30
    30
    30

Elapsed: 00:00:00.00
SQL>

当然除了唯一列，主键之外，oracle中还提供了表示列的rownum，也叫伪列。

SQL> select deptno
  2  from 
  3  (
  4  select rownum,deptno from emp2 where mgr=7698
  5  union
  6  select rownum,deptno from emp2 where job=upper('salesman')
  7  )
  8  order by 1;

    DEPTNO
----------
    30
    30
    30
    30
    30

Elapsed: 00:00:00.00
SQL>

三、组合相关和行

相对于查询单表中的数据来说，通常见到的更多的是要在多个表中返回数据。
示例如：查询部门10的员工编号、姓名、以及所在的部门名称和工作地址

SQL> select e.empno,e.ename,d.deptno,d.dname
  2  from emp e,dept d
  3  where e.deptno=d.deptno and e.deptno=10;

     EMPNO ENAME      DEPTNO DNAME
---------- ---------- ---------- --------------
      7782 CLARK          10 ACCOUNTING
      7839 KING           10 ACCOUNTING
      7934 MILLER         10 ACCOUNTING

Elapsed: 00:00:00.01
SQL>

也可以使用内连接这样：

SQL> select e.empno,e.ename,d.deptno,d.dname
  2  from emp e
  3  inner join dept d on e.deptno=d.deptno
  4  where e.deptno=10;

     EMPNO ENAME      DEPTNO DNAME
---------- ---------- ---------- --------------
      7782 CLARK          10 ACCOUNTING
      7839 KING           10 ACCOUNTING
      7934 MILLER         10 ACCOUNTING

Elapsed: 00:00:00.00
SQL>

四、IN、EXISTS和inner join

示例:先创建一个表emp3，并且只包含ename,job,sal,comm四列

SQL> create table emp3
  2  as
  3  select ename,job,sal,comm from emp where job='CLERK';

Table created.

Elapsed: 00:00:00.20
SQL>

需求：要求返回与emp3表中（empno,job,sal）中数据相匹配的emp(empno,ename,job,sal,deptno)的信息

1、使用IN

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

Explained.

Elapsed: 00:00:00.00
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 349248158

---------------------------------------------------------------------------
| Id  | Operation      | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      | 1 |    40 | 6   (0)| 00:00:01 |
|*  1 |  HASH JOIN SEMI    |      | 1 |    40 | 6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |   350 | 3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMP3 | 4 |    60 | 3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("ENAME"="ENAME" AND "JOB"="JOB" AND "SAL"="SAL")

15 rows selected.

Elapsed: 00:00:00.02
SQL>

2、EXISTS的写法

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

Explained.

Elapsed: 00:00:00.00
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 349248158

---------------------------------------------------------------------------
| Id  | Operation      | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      | 1 |    40 | 6   (0)| 00:00:01 |
|*  1 |  HASH JOIN SEMI    |      | 1 |    40 | 6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |   350 | 3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMP3 | 4 |    60 | 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")

16 rows selected.

Elapsed: 00:00:00.01
SQL>

3、使用inner join

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

Explained.

Elapsed: 00:00:00.05
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 620718003

---------------------------------------------------------------------------
| Id  | Operation      | Name | Rows  | Bytes | Cost (%CPU)| Time  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      | 4 |   160 | 6   (0)| 00:00:01 |
|*  1 |  HASH JOIN     |      | 4 |   160 | 6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP3 | 4 |    60 | 3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMP  |    14 |   350 | 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")

16 rows selected.

Elapsed: 00:00:00.13
SQL>

结论：发现子在使用IN 和EXISTS的时候，查询都使用的是hash join semi（hash半连接）,只有join的时候使用的hash join，所以IN和EXISTS的查询效率是一致的。
**

五、inner join ，left join ,right join ,full join 解析

首先创建两张表：这里使用sql脚本


SQL> ! cat c_join.sql
-----prompt--------
--this is left table
create table left as
select 'left_1' as str,'a' 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;
-------end----------
-----prompt--------
--this is right table
create table right 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;
------end----------

SQL>

查看两张表中的数据：

SQL> select * from left;

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

Elapsed: 00:00:00.01
SQL> select * from right;

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

Elapsed: 00:00:00.00
SQL>

**
1、inner join
返回两个表中相匹配的数据集。

SQL> select * from left;

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

Elapsed: 00:00:00.01
SQL> select * from right;

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

Elapsed: 00:00:00.00
SQL> 
SQL> select l.str as left_str,r.str as right_str
  2  from left l
  3  inner join right r on
  4  l.v=r.v;

LEFT_S RIGHT_S
------ -------
left_3 right_3
left_4 right_4

Elapsed: 00:00:00.00
SQL>

2、left join
以左表为主，返回左表中的所有数据集，右表只返回于左表相匹配的数据集

SQL> select * from left;

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

Elapsed: 00:00:00.01
SQL> select * from right;

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

Elapsed: 00:00:00.00
SQL> select l.str as left_str,r.str as right_str
  2  from left l
  3  left join right r on
  4  l.v=r.v;

LEFT_S RIGHT_S
------ -------
left_3 right_3
left_4 right_4
left_2
left_1

Elapsed: 00:00:00.00
SQL>

上面是SQL 99的表准写法，但是ORACLE将其进行了拓展以
**

+

** 为区分
还可以改写为

SQL> select l.str as left_str,r.str as right_str
  2  from left l,right r
  3  where l.v=r.v(+)
  4  ;

LEFT_S RIGHT_S
------ -------
left_3 right_3
left_4 right_4
left_2
left_1

Elapsed: 00:00:00.00
SQL>

3、right join
与left join恰好相反

SQL> select l.str as left_str,r.str as right_str
  2  from left l
  3  right join right r on
  4  l.v=r.v
  5  order by 1,2;

LEFT_S RIGHT_S
------ -------
left_3 right_3
left_4 right_4
       right_5
       right_6

Elapsed: 00:00:00.00
SQL>

改写后的为：

SQL> select l.str as left_str,r.str as right_str
  2  from left l,right r
  3  where l.v(+)=r.v
  4  order by 1,2;

LEFT_S RIGHT_S
------ -------
left_3 right_3
left_4 right_4
       right_5
       right_6

Elapsed: 00:00:00.00
SQL>

4、full join
返回两张表当中的所有的数据集，但是匹配的显示在同一行，非匹配的只显示一个表的数据集；

SQL> select l.str as left_str,r.str as right_str
  2  from left l
  3  full join right r
  4  on l.v=r.v
  5  order by 1,2;

LEFT_S RIGHT_S
------ -------
left_1
left_2
left_3 right_3
left_4 right_4
       right_5
       right_6

6 rows selected.

Elapsed: 00:00:00.00
SQL>

**

六、自关联

**
如下：
Oracle表中有一列为mgr列，此列为empno对应的主管列，因此要查询主管的姓名，需要进行自关联。

SQL> select e.empno||' -> '|| m.empno as bianhao,rpad(e.ename,8,' ')||' -> '||m.ename as xingming
  2  from emp e
  3  left join emp m on(e.mgr=m.empno);

BIANHAO           XINGMING
------------------------- -------------------------
7788 -> 7566          SCOTT    -> JONES
7902 -> 7566          FORD     -> JONES
7499 -> 7698          ALLEN    -> BLAKE
7521 -> 7698          WARD     -> BLAKE
7654 -> 7698          MARTIN   -> BLAKE
7844 -> 7698          TURNER   -> BLAKE
7900 -> 7698          JAMES    -> BLAKE
7934 -> 7782          MILLER   -> CLARK
7876 -> 7788          ADAMS    -> SCOTT
7566 -> 7839          JONES    -> KING
7698 -> 7839          BLAKE    -> KING
7782 -> 7839          CLARK    -> KING
7369 -> 7902          SMITH    -> FORD
7839 ->           KING     ->

14 rows selected.

SQL>

可以理解为在两个不同的数据集里面取数据。
首先创建视图employee和manager
然后进行left join 连接

SQL> create or replace view employee
  2  as
  3  select * from emp;

View created.

SQL> create or replace view manager
  2  as
  3  select * from emp;

View created.

SQL> select e.empno||' -> '|| m.empno as bianhao,rpad(e.ename,8,' ')||' -> '||m.ename as xingming
  2  from employee e
  3  left join manager m on(e.mgr=m.empno)
  4  ;

BIANHAO           XINGMING
------------------------- -------------------------
7788 -> 7566          SCOTT    -> JONES
7902 -> 7566          FORD     -> JONES
7499 -> 7698          ALLEN    -> BLAKE
7521 -> 7698          WARD     -> BLAKE
7654 -> 7698          MARTIN   -> BLAKE
7844 -> 7698          TURNER   -> BLAKE
7900 -> 7698          JAMES    -> BLAKE
7934 -> 7782          MILLER   -> CLARK
7876 -> 7788          ADAMS    -> SCOTT
7566 -> 7839          JONES    -> KING
7698 -> 7839          BLAKE    -> KING
7782 -> 7839          CLARK    -> KING
7369 -> 7902          SMITH    -> FORD
7839 ->           KING     ->

14 rows selected.

SQL>

**

七、NOT IN、NOT EXISTS、LEFT JOIN

**
示例：

SQL> select count(*) from emp where deptno=40;

  COUNT(*)
----------
     0

SQL>

发现，在emp表中，有一列为deptno列，部门编号为40，但是一个员工都没有，那么通过什么方式将部门40的信息也查询出来呢？这里提供了三种方式查询。同时我们也查看它们的查询计划。
创建一张表dept1并为其增加主键

SQL> create table dept1 as select * from dept;

Table created.

SQL> alter table dept1 add constraints pk_deptno primary key(deptno);

Table altered.

SQL> alter session set "_b_tree_bitmap_plans"=false;

Session altered.

SQL> set timing on;

1、NOT IN

SQL> select * from dept1
  2  where deptno not in
  3  (select emp.deptno from emp  where emp.deptno is not null);

    DEPTNO DNAME      LOC
---------- -------------- -------------
    40 OPERATIONS     BOSTON

Elapsed: 00:00:00.01
SQL> explain plan for
  2  select * from dept1
  3  where deptno not in
  4  (
  5  select emp.deptno from emp where emp.deptno is not null);

Explained.

Elapsed: 00:00:00.03
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3947175208

------------------------------------------------------------------------------------------
| Id  | Operation            | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |       |     1 |    23 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN ANTI         |       |     1 |    23 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT1     |     4 |    80 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN       | PK_DEPTNO |     4 |   |     1   (0)| 00:00:01 |
|*  4 |   SORT UNIQUE            |       |    14 |    42 |     4  (25)| 00:00:01 |
|*  5 |    TABLE ACCESS FULL         | EMP   |    14 |    42 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("DEPTNO"="EMP"."DEPTNO")
       filter("DEPTNO"="EMP"."DEPTNO")
   5 - filter("EMP"."DEPTNO" IS NOT NULL)

19 rows selected.

Elapsed: 00:00:00.16
SQL>

2、NOT EXISTS

SQL> explain plan for
  2  select * from dept1
  3  where not exists
  4  (
  5  select null from emp where emp.deptno=dept1.deptno);

Explained.

Elapsed: 00:00:00.01
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3947175208

------------------------------------------------------------------------------------------
| Id  | Operation            | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |       |     1 |    23 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN ANTI         |       |     1 |    23 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT1     |     4 |    80 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN       | PK_DEPTNO |     4 |   |     1   (0)| 00:00:01 |
|*  4 |   SORT UNIQUE            |       |    14 |    42 |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL         | EMP   |    14 |    42 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("EMP"."DEPTNO"="DEPT1"."DEPTNO")
       filter("EMP"."DEPTNO"="DEPT1"."DEPTNO")

18 rows selected.

Elapsed: 00:00:00.02
SQL>

3、LEFT JOIN
特点上面有提到过

SQL> explain plan for
  2  select * from dept1
  3  left join emp on emp.deptno=dept1.deptno
  4  where emp.deptno is null;

Explained.

Elapsed: 00:00:00.02
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4282179200

-------------------------------------------------------------------------------------------
| Id  | Operation             | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |       | 1 |    58 |  6  (17)| 00:00:01 |
|*  1 |  FILTER               |       |   |   |         |     |
|   2 |   MERGE JOIN OUTER        |       | 1 |    58 |  6  (17)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| DEPT1     | 4 |    80 |  2   (0)| 00:00:01 |
|   4 |     INDEX FULL SCAN       | PK_DEPTNO | 4 |   |  1   (0)| 00:00:01 |
|*  5 |    SORT JOIN              |       |    14 |   532 |  4  (25)| 00:00:01 |
|   6 |     TABLE ACCESS FULL         | EMP   |    14 |   532 |  3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("EMP"."DEPTNO" IS NULL)
   5 - access("EMP"."DEPTNO"(+)="DEPT1"."DEPTNO")
       filter("EMP"."DEPTNO"(+)="DEPT1"."DEPTNO")

20 rows selected.

Elapsed: 00:00:00.02

发现三个的PLAN应用都是merge join anti，说明三种方法的执行计划一样。
**

八、外连接中的条件不要乱放

**
之前创建了两张视图left和right
这里先进行一个查询

SQL> select l.str as left_str,r.str as right_str,r.status
  2  from left l
  3  left join right r on
  4  l.v=r.v
  5  order by 1;

LEFT_S RIGHT_S     STATUS
------ ------- ----------
left_1
left_2
left_3 right_3      1
left_4 right_4      0

Elapsed: 00:00:00.00
SQL>

对于右表中的status，此时即想要显示连接的所有结果集，但同时只想显示status为1,不要status=0的结果怎么办？通常做法是给结果加过滤where条件,来看一下结果
如下形式：

LEFT_S RIGHT_S     STATUS
------ ------- ----------
left_1
left_2
left_3 right_3      1
left_4

Elapsed: 00:00:00.00
SQL>

SQL 99写法

SQL> select l.str as left_str,r.str as right_str,r.status
  2  from left l
  3  left join right r on
  4  l.v=r.v
  5  where r.status=1
  6  order by 1;

LEFT_S RIGHT_S     STATUS
------ ------- ----------
left_3 right_3      1

ORACLE扩展写法

SQL> select l.str as left_str,r.str as right_str,r.status
  2  from left l,right r
  3  where l.v=r.v(+) and r.status=1
  4  order by 1,2;

LEFT_S RIGHT_S     STATUS
------ ------- ----------
left_3 right_3      1

Elapsed: 00:00:00.00
SQL>

此时查看一下查询计划

SQL> explain plan for
  2  select l.str as left_str,r.str as right_str,r.status
  3  from left l,right r
  4  where l.v=r.v(+) and r.status=1
  5  order by 1,2;

Explained.

Elapsed: 00:00:00.04
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 377074687

-----------------------------------------------------------------------------
| Id  | Operation       | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |       |     2 |    42 |     7  (15)| 00:00:01 |
|   1 |  SORT ORDER BY      |       |     2 |    42 |     7  (15)| 00:00:01 |
|*  2 |   HASH JOIN     |       |     2 |    42 |     6   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL| RIGHT |     2 |    24 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| LEFT  |     4 |    36 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("L"."V"="R"."V")
   3 - filter("R"."STATUS"=1)

17 rows selected.

Elapsed: 00:00:00.13
SQL>

发现，与我们想要的结果是不一样的，我们想要的是显示全部的连接数据，但是同时只显示status=1，status=0的需要去掉
所以此时where条件的过滤并不正确，正确的写法应该如下：

SQL> select l.str as left_str,r.str as right_str,r.status
  2  from left l
  3  left join right r on(l.v=r.v and r.status=1)
  4  order by 1,2;

LEFT_S RIGHT_S     STATUS
------ ------- ----------
left_1
left_2
left_3 right_3      1
left_4

Elapsed: 00:00:00.00
SQL>

看一下PLAN

SQL> explain plan for
  2  select l.str as left_str,r.str as right_str,r.status
  3  from left l
  4  left join right r on(l.v=r.v and r.status=1)
  5  order by 1,2;

Explained.

Elapsed: 00:00:00.00
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 653465157

-----------------------------------------------------------------------------
| Id  | Operation       | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |       |     4 |    84 |     7  (15)| 00:00:01 |
|   1 |  SORT ORDER BY      |       |     4 |    84 |     7  (15)| 00:00:01 |
|*  2 |   HASH JOIN OUTER   |       |     4 |    84 |     6   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| LEFT  |     4 |    36 |     3   (0)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL| RIGHT |     2 |    24 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("L"."V"="R"."V"(+))
   4 - filter("R"."STATUS"(+)=1)

17 rows selected.

Elapsed: 00:00:00.02
SQL>

发现status=0的数据不见，但是status的数据正常显示。
也可以使用下面的方式进行查询，即先过滤，然后再join

SQL> select l.str as left_str,r.str as right_str,r.status
  2  from left l
  3  left join (select * from right r where r.status=1) r
  4  on(l.v=r.v)
  5  order by 1,2;

LEFT_S RIGHT_S     STATUS
------ ------- ----------
left_1
left_2
left_3 right_3      1
left_4

Elapsed: 00:00:00.00
SQL>

查看PLAN

SQL> explain plan for
  2  select l.str as left_str,r.str as right_str,r.status
  3  from left l
  4  left join (select * from right r where r.status=1) r
  5  on(l.v=r.v)
  6  order by 1,2;

Explained.

Elapsed: 00:00:00.00
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 653465157

-----------------------------------------------------------------------------
| Id  | Operation       | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |       |     4 |    84 |     7  (15)| 00:00:01 |
|   1 |  SORT ORDER BY      |       |     4 |    84 |     7  (15)| 00:00:01 |
|*  2 |   HASH JOIN OUTER   |       |     4 |    84 |     6   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| LEFT  |     4 |    36 |     3   (0)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL| RIGHT |     2 |    24 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("L"."V"="R"."V"(+))
   4 - filter("R"."STATUS"(+)=1)

17 rows selected.

Elapsed: 00:00:00.09
SQL>

结果都发现多了一个OUTER关键字，这表示前面的已经不是left jion了，这个才是。

DeveloperHonor
关注
专栏目录
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值