SQL语句之多表操作

最新推荐文章于 2024-05-12 15:15:03 发布
最新推荐文章于 2024-05-12 15:15:03 发布
阅读量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
关注
专栏目录
mysql建多个表_SQL一次创建多个表
weixin_39724009的博客
01-18 3756
I need to create multiple tables at once. Im having a hard time figuring out the correct method for accomplishing this. Currently my script looks like this:private function buildDB() {$sql = <<C...
mysql面试题sql语句多表联查_MySQL多表查询优化
weixin_35705450的博客
02-08 2310
一、多表查询连接的选择： 相信这内连接，左连接什么的大家都比较熟悉了，当然还有左外连接什么的，基本用不上我就不贴出来了。这图只是让大家回忆一下，各种连接查询。 然后要告诉大家的是，需要根据查询的情况，想好使用哪种连接方式效率更高。二、MySQL的JOIN实现原理在MySQL 中，只有一种Join 算法，就是大名鼎鼎的Nested Loop Join，他没有其他很多数据库所提供的Hash Join，...
SQL多表操作
weixin_51120880的博客
08-26 236
多表查询 SELECT a.name ,b.id theatre FROM student A ,theatre B WHERE a.id='2' AND a.id=b.Studentid; -- 显示内连接查询 即表与表之间交集的部分 SELECT s.`name`, -- 学生姓名 s.`age`, -- 学生年龄 t.id -- 教室id FROM student s -- 表名一 JOIN theatre t -- 表名二 ON .
学习笔记：SQL语句-数据表操作(多表)
weixin_59008933的博客
05-12 1070
由于上方已经创好了表,所以下面演示创建表之后单独添加外键，学生和班级，学生为多的一方面称为从表，将主表班级表的id起名为class_id作为从表的外键。那么在数据表中怎么表示表之间的联系呢，一般使用外键，外键的作用是关联两张表，约束两张表的数据，具体情况具体分析。外键列还应该有唯一约束。注意：想要删除主表中的某一条数据，一定要看从表中对应的有没有引用主表的这条数据，不然是无法删除成功的。将新建的这一列称为外键列，它的作用是为了建立当前表和其他表的关联关系，一张表中，允许有多个外键列。
sql-多表操作
weixin_46158141的博客
09-27 849
sql-多表操作一、多表关系1. 一对多2.多对多3. 一对一1.2、使用步骤1.一对多2.多对多练习：二、多表查询2.1 常见分类1. 笛卡尔积：两张表的乘积。（了解）2. 内连接：3. 外链接：2.2 隐式内连接2.3显示内连接：inner join2.4 左外连接：left outer join2.5 右外连接：right outer join三 .子查询总结 一、多表关系 1. 一对多 2.多对多 3. 一对一 1.2、使用步骤 1.一对多 一对多关系：通过主外键关系，形成一对多关系。 一表：
SQL语句语法
03-21
本篇文章将深入探讨SQL语句的基本语法，包括数据定义、数据操作、实用工具、事务处理和锁定、数据库管理、复制语句以及预处理语句等关键概念。 13.1 数据定义语句： 数据定义语句主要用于创建、修改和删除数据库...
VBA+ADO 连接Excel表格查询操作数据(SQL语句操作)
06-18
VBA+ADO 连接Excel表格查询操作数据(SQL语句操作) SQL语句操作表格
学习SQL语句之SQL语句大全.docx
03-22
SQL语句大全是关系数据库管理系统中使用的标准语言，用于管理和操作数据库中的数据。它的主要功能是存储、管理和检索数据。SQL语句大全可以分为以下几个方面：数据操作、数据概念、事务操作、程序化SQL等。 数据...
sql语句范例sql语句范例pdf
最新发布
06-15
表操作 例1对于表的教学管理数据库中的表STUDENTS ， 可以定义如下: CREATE TABLE STUDENTS (SNONUMERIC (6， O) NOT NULL SNAME CHAR (8) NOT NULL AGENUMERIC (3,0) SEXCHAR (2) BPLACE CHAR(20) PRIMARY KEY (SNO...
SQL的多表操作
dbmg6064的博客
10-10 183
多表更新： 假定我们有两张表，一张表为Product表存放产品信息，其中有产品价格列Price；另外一张表是ProductPrice表，我们要将ProductPrice表中的价格字段Price更新为Price表中价格字段的80%。 在Mysql中我们有几种手段可以做到这一点，一种是update table1 t1, table2 ts ...的方式： UPDATE product...
SQL语句表操作
weixin_30398227的博客
01-19 113
创建表create table student( id int not null auto_increment primary key comment '这是主键', sno varchar(10) comment '这是姓名', sex int default 1 comment '这是性别 1代表男 0代表女', brithday date);数据类型： int dat...
SQL语句之表操作
weixin_34336526的博客
01-27 101
　　SQL语句系列 1、SQL语句之行操作 2、SQL语句之表操作 3、SQL语句之数据库操作 4、SQL语句之用户管理 　　写在前面 　　在上一篇博文里面我整理了“行”级别的操作，分别是“增(insert)、删(delete)、改(update)、查(select)”，这篇文章继续整理“表”级别的操作，同样分为“增(create table)、删(drop ...
纯生sql 语句创建一对多表
m0_51393235的博客
06-02 894
#1.登录数据客户端 mysql -uroot -p enter password:**** #2.查看所有的库 show databases; #3.新建一个库 create database 库名 charset utf8; #4.进入数据库 use 库名； #5.查看所有的表 show tables; # 6.创建一个表 create table 表名(字段名 类型，字段名 类型)； #7.查看一下表结构 desc 表名 #8.查看当前在那个数据库下： select databa.
SQL 语句 对于一个表的基本操作
@Cynthia博客
09-07 917
首先创建一个基本的表，在定义列名时注意赋默认值，注意用户定义完整性这一约束条件 create table base( col1 varchar(50) primary key, --主键 默认不为空 colF varchar(50), col2 int default 0, col3 numeric(5,2),--5位长度，两位小数 ) 2，给表添加一列  alter ta...
SQL server数据库 循环建表语句及多表循环插入语句
Ring1303的博客
04-18 2824
sql server数据库的循环建表语句及多表循环插入语句 --建多张表的存储过程 create  proc createtables as begin declare @temp nvarchar(20)  declare @sql nvarchar(3000) set @temp = 1 while @temp begin set @sql = 'create tabl
sql语句语法多表关联_SQL创建表语句-带有示例语法
cumi7754的博客
08-20 1378
sql语句语法多表关联SQL is one of the most reliable and straightforward querying languages around. It provides clear cut syntax that reads easily without abstracting away too much of the functionality's meanin...
SQL语句与数据库操作语句
Zzze0101的博客
05-06 1833
以下操作过程均在Navicat Premium中对MySQL进行操作。 数据库操作语句 1、创建数据库 语法 create database 数据库名 [ character set 字符集 ] ;其中字符集若不写，则默认为安装数据库时指定编码 例子 CREATE DATABASE keep CHARACTER SET utf8 执行结果：在MySQL中创建了一个数据库 2、查看数据库 语...
表关联查询
weixin_41511004的博客
03-07 833
sql 同时查询多个表 可以使用连表查询比如使用join select s1.*,s2.* from s1 left join s2 on s1.id = s2.id;利用where andselect s1.* ,s2.* from s1,s2 where s1.id = s2.id;建议根据条件选择 第二条sql优于第一条其次还可以 使用unionselect * from Auni...
"SQL查询语句实例：基于多表操作
此外，SQL还支持子查询和连接表的操作。通过子查询，我们可以在一个SELECT语句中嵌套另一个SELECT语句，从而实现更复杂的逻辑。连接表则允许我们在查询中使用多张表的数据。这样可以扩大查询的范围，获取更完整的...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值