Oracle查询优化学习

最新推荐文章于 2025-06-29 16:48:38 发布

原创最新推荐文章于 2025-06-29 16:48:38 发布 · 568 阅读

2 ·

CC 4.0 BY-SA版权

Oracle查询优化专栏收录该内容

1 篇文章

订阅专栏

一、单表查询

SQL> desc emp;
Name     Type         Nullable Default Comments 
-------- ------------ -------- ------- -------- 
EMPNO    NUMBER(4)                              
ENAME    VARCHAR2(10) Y                         
JOB      VARCHAR2(9)  Y                         
MGR      NUMBER(4)    Y                         
HIREDATE DATE         Y                         
SAL      NUMBER(7,2)  Y                         
COMM     NUMBER(7,2)  Y                         
DEPTNO   NUMBER(2)    Y

1.查询表中所有行与列

如：查看员工所有信息

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/2/20     1600.00    300.00     30
 7521 WARD       SALESMAN   7698 1981/2/22     1250.00    500.00     30
 7566 JONES      MANAGER    7839 1981/4/2      2975.00               20
 7654 MARTIN     SALESMAN   7698 1981/9/28     1250.00   1400.00     30
 7698 BLAKE      MANAGER    7839 1981/5/1      2850.00               30
 7782 CLARK      MANAGER    7839 1981/6/9      2450.00               10
 7788 SCOTT      ANALYST    7566 1987/4/19     3000.00               20
 7839 KING       PRESIDENT       1981/11/17    5000.00               10
 7844 TURNER     SALESMAN   7698 1981/9/8      1500.00      0.00     30
 7876 ADAMS      CLERK      7788 1987/5/23     1100.00               20
 7900 JAMES      CLERK      7698 1981/12/3      950.00               30
 7902 FORD       ANALYST    7566 1981/12/3     3000.00               20
 7934 MILLER     CLERK      7782 1982/1/23     1300.00               10

14 rows selected

2.从表中检索部分行

如：查看工所有多少销售人员

SQL> select * from emp where job = 'SALESMAN';

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7499 ALLEN      SALESMAN   7698 1981/2/20     1600.00    300.00     30
 7521 WARD       SALESMAN   7698 1981/2/22     1250.00    500.00     30
 7654 MARTIN     SALESMAN   7698 1981/9/28     1250.00   1400.00     30
 7844 TURNER     SALESMAN   7698 1981/9/8      1500.00      0.00     30

3.查找空值

SQL> select * from emp where comm is null;

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7369 SMITH      CLERK      7902 1980/12/17     800.00               20
 7566 JONES      MANAGER    7839 1981/4/2      2975.00               20
 7698 BLAKE      MANAGER    7839 1981/5/1      2850.00               30
 7782 CLARK      MANAGER    7839 1981/6/9      2450.00               10
 7788 SCOTT      ANALYST    7566 1987/4/19     3000.00               20
 7839 KING       PRESIDENT       1981/11/17    5000.00               10
 7876 ADAMS      CLERK      7788 1987/5/23     1100.00               20
 7900 JAMES      CLERK      7698 1981/12/3      950.00               30
 7902 FORD       ANALYST    7566 1981/12/3     3000.00               20
 7934 MILLER     CLERK      7782 1982/1/23     1300.00               10

null不能用“=”运算符，要用is null判断

null不支持加、减、乘、除、大小比较、相等比较，否则只能为空

4.将空值转化为实际值

SQL> select ename ,coalesce(comm,0) from emp;

ENAME      COALESCE(COMM,0)
---------- ----------------
SMITH                     0
ALLEN                   300
WARD                    500
JONES                     0
MARTIN                 1400
BLAKE                     0
CLARK                     0
SCOTT                     0
KING                      0
TURNER                    0
ADAMS                     0
JAMES                     0
FORD                      0
MILLER                    0

14 rows selected

5.返回多个值中第一个不为空的值

SQL> create or replace view v as
  2  select null as c1, null as c2,1 as c3 , null as c4 , 2 as c5, null as c6 from dual
  3  union all
  4  select null as c1 , null as c2 , null as c4,3 as c4, null as c5 , 2 as c6 from dual;

View created

SQL> select coalesce(C1,C2,C3,C4,C5,C6) as c from v;

         C
----------
         1
         3

6.查找满足多个条件的行

如：查找部门10中所有员工、所有得到提成的员工，以及部门20中工资不超过2000美元的员工

SQL> select * from emp where (deptno=10 or comm is not null or(sal <= 2000 and deptno = 20));

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7369 SMITH      CLERK      7902 1980/12/17     800.00               20
 7499 ALLEN      SALESMAN   7698 1981/2/20     1600.00    300.00     30
 7521 WARD       SALESMAN   7698 1981/2/22     1250.00    500.00     30
 7654 MARTIN     SALESMAN   7698 1981/9/28     1250.00   1400.00     30
 7782 CLARK      MANAGER    7839 1981/6/9      2450.00               10
 7839 KING       PRESIDENT       1981/11/17    5000.00               10
 7844 TURNER     SALESMAN   7698 1981/9/8      1500.00      0.00     30
 7876 ADAMS      CLERK      7788 1987/5/23     1100.00               20
 7934 MILLER     CLERK      7782 1982/1/23     1300.00               10

9 rows selected

7.从表中检索部分列

SQL> select empno,ename,hiredate,sal from emp where deptno=10;

EMPNO ENAME      HIREDATE          SAL
----- ---------- ----------- ---------
 7782 CLARK      1981/6/9      2450.00
 7839 KING       1981/11/17    5000.00
 7934 MILLER     1982/1/23     1300.00

8.为列取有意义名字

SQL> select ename as 姓名,deptno as 部门编号,sal as 工资,comm as 提成 from emp;

姓名       部门编号        工资        提成
---------- ---- --------- ---------
SMITH        20    800.00 
ALLEN        30   1600.00    300.00
WARD         30   1250.00    500.00
JONES        20   2975.00 
MARTIN       30   1250.00   1400.00
BLAKE        30   2850.00 
CLARK        10   2450.00 
SCOTT        20   3000.00 
KING         10   5000.00 
TURNER       30   1500.00      0.00
ADAMS        20   1100.00 
JAMES        30    950.00 
FORD         20   3000.00 
MILLER       10   1300.00 

14 rows selected

9.在where条件中引用取别名的列

SQL> select * from (select sal as 工资,comm as 提成 from emp) x where 工资<1000;

       工资        提成
--------- ---------
   800.00 
   950.00

注意：引用别名时千万别忘了嵌套一层，因为这个别名是在select之后才生效的。
10.拼接列

SQL> select ename || '的工作是'||job as msg from emp where deptno = 10;

MSG
-------------------------------
CLARK的工作是MANAGER
KING的工作是PRESIDENT
MILLER的工作是CLERK

11.拼接列高级用法

SQL> select 'TRANCATE TABLE' || owner || '.'|| table_name || ';' as 清空表 from all_tables where owner = 'SCOTT';

清空表
----------------------------------------------------------------------------
TRANCATE TABLESCOTT.DEPT;
TRANCATE TABLESCOTT.EMP;
TRANCATE TABLESCOTT.BONUS;
TRANCATE TABLESCOTT.SALGRADE;

12.在select语句中使用条件逻辑

如：当职员工资小于或等于2000时候，就返回消息'太低',大于或等于4000时就返回'过高',如果在这两者之间就返回'OK';

SQL> select ename,
  2         sal,
  3         case
  4           when sal < 2000 then
  5            '过低'
  6           when sal >= 4000 then
  7            '过高'
  8           else
  9            'ok'
 10         end as status
 11    from emp
 12   where deptno = 10;

ENAME            SAL STATUS
---------- --------- ------
CLARK        2450.00 ok
KING         5000.00 过高
MILLER       1300.00 过低

13.按照工资分档次统计人数

SQL> select 档次,count(*) as 人数
  2  from (
  3  	select (
  4  		case
  5  			when sal <=1000 then '0000-1000'
  6  			when sal <=2000 then '2000-3000'
  7  			when sal <=3000 then '3000-4000'
  8  			when sal <=4000 then '4000-5000'
  9  		end) as 档次,
 10  		ename,
 11  		sal
 12  		from emp)
 13  	group by 档次
 14  	order by 1;

档次              人数
--------- ----------
0000-1000          2
2000-3000          6
3000-4000          5
                   1

14.限制返回行数
rownum是一次对数据做标识的，就像上学时依据考分排名一样，需要有第一名，后面才会有第二名。
如：获取第二行数据

SQL> select * from (select rownum as sn , emp.* from emp where rownum <=2) where sn = 2;

        SN EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
---------- ----- ---------- --------- ----- ----------- --------- --------- ------
         2  7499 ALLEN      SALESMAN   7698 1981/2/20     1600.00    300.00     30

如：获取所有员工信息，按照员工编号降序

SQL> select * from emp order by empno desc;

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

14 rows selected

如：获取前两条数据

SQL> select * from (select * from emp order by empno desc) where rownum <=2;

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7934 MILLER     CLERK      7782 1982/1/23     1300.00               10
 7902 FORD       ANALYST    7566 1981/12/3     3000.00               20

15.从表中随机返回n条记录

SQL> select empno ,ename from (select empno,ename from emp order by dbms_random.value()) where rownum <=3;

EMPNO ENAME
----- ----------
 7654 MARTIN
 7839 KING
 7844 TURNER

16.模糊查询

SQL> create or replace view v as
  2  select 'ABCEDF' as vname from dual
  3  union all
  4  select '_BCEFG' as vname from dual
  5  union all
  6  select '_BCEDF' as vname from dual
  7  union all
  8  select '_\BCEDF' as vname from dual
  9  union all
 10  select 'XYCEG' as vname from dual;

View created

如：查出vname中包含字符串"CED"的数据

SQL> select * from v where vname like '%CED%';

VNAME
-------
ABCEDF
_BCEDF
_\BCEDF

如：查出vname中包含字符串“_BCE"的数据

SQL> select * from v where vname like '_BCE%';

VNAME
-------
ABCEDF
_BCEFG
_BCEDF

发现"_"被当作通配符了，需要escape

如：查出vname中包含字符串“_BCE"的数据

SQL> select * from v where vname like '\_BCE%' escape '\';

VNAME
-------
_BCEFG
_BCEDF

-------------------
1.以指定次序返回查询结果
如：查看单位雇员的信息

SQL> select empno , ename ,hiredate from emp where deptno = 10 order by hiredate asc;

EMPNO ENAME      HIREDATE
----- ---------- -----------
 7782 CLARK      1981/6/9
 7839 KING       1981/11/17
 7934 MILLER     1982/1/23

SQL> select empno , ename ,hiredate from emp where deptno = 10 order by 3 asc;

EMPNO ENAME      HIREDATE
----- ---------- -----------
 7782 CLARK      1981/6/9
 7839 KING       1981/11/17
 7934 MILLER     1982/1/23

2.按多个字段排序
如：按部门编号升序，按工资降序排列

SQL> select empno , deptno ,sal ,ename ,job from emp order by 2 asc , 3 desc;

EMPNO DEPTNO       SAL ENAME      JOB
----- ------ --------- ---------- ---------
 7839     10   5000.00 KING       PRESIDENT
 7782     10   2450.00 CLARK      MANAGER
 7934     10   1300.00 MILLER     CLERK
 7788     20   3000.00 SCOTT      ANALYST
 7902     20   3000.00 FORD       ANALYST
 7566     20   2975.00 JONES      MANAGER
 7876     20   1100.00 ADAMS      CLERK
 7369     20    800.00 SMITH      CLERK
 7698     30   2850.00 BLAKE      MANAGER
 7499     30   1600.00 ALLEN      SALESMAN
 7844     30   1500.00 TURNER     SALESMAN
 7654     30   1250.00 MARTIN     SALESMAN
 7521     30   1250.00 WARD       SALESMAN
 7900     30    950.00 JAMES      CLERK

多列排序时，若前面的列有重复值(如deptno=10有3行数据) 后面的排序才有用
3.按子串排序
如：按顾客电话尾号顺序排序

SQL> select last_name as 名称,
  2  	   phone_number as 号码,
  3  	   salary as 工资,
  4  	   substr(phone_number,-4) as 尾号
  5  from   hr.employees
  6  where rownum <=5
  7  order  by 4;

名称                      号码                         工资 尾号
------------------------- -------------------- ---------- ----------------
King                      515.123.4567           24000.00 4567
Hunold                    590.423.4567            9000.00 4567
Kochhar                   515.123.4568           17000.00 4568
Ernst                     590.423.4568            6000.00 4568
De Haan                   515.123.4569           17000.00 4569

a.Sql执行顺序

1  (8)SELECT  (9) DISTINCT (11) <TOP_specification> <select_list> 
2  (1)  FROM <left_table>  
3  (3) <join_type> JOIN <right_table> 
4  (2) ON <join_condition> 
5  (4) WHERE <where_condition> 
6  (5) GROUP BY <group_by_list> 
7  (6) WITH {CUBE | ROLLUP} 
8  (7) HAVING <having_condition> 
9 (10) ORDER BY <order_by_list>

b.如果对排序之后数据取前几条数据，一定要在外面包一层查询。

4.TRANSLATE

SQL> select translate('ab 你好 bcadefg','abcdefg','1234567') as new_str from dual;

NEW_STR
-----------------
12 你好 2314567

5.按数字和字母混合字符串中的字母排序

6.处理排序空值

SQL> select ename , sal ,comm , nvl(comm , -1) order_col from emp order by 4;

ENAME            SAL      COMM  ORDER_COL
---------- --------- --------- ----------
SMITH         800.00                   -1
CLARK        2450.00                   -1
FORD         3000.00                   -1
JAMES         950.00                   -1
ADAMS        1100.00                   -1
JONES        2975.00                   -1
BLAKE        2850.00                   -1
MILLER       1300.00                   -1
SCOTT        3000.00                   -1
KING         5000.00                   -1
TURNER       1500.00      0.00          0
ALLEN        1600.00    300.00        300
WARD         1250.00    500.00        500
MARTIN       1250.00   1400.00       1400

14 rows selected

oracle默认排序空值在后面，如果想把空值显示在前面怎么办
如：空值在前面

SQL> select ename , sal, comm from emp order by 3 nulls first;

ENAME            SAL      COMM
---------- --------- ---------
SMITH         800.00 
CLARK        2450.00 
FORD         3000.00 
JAMES         950.00 
ADAMS        1100.00 
JONES        2975.00 
BLAKE        2850.00 
MILLER       1300.00 
SCOTT        3000.00 
KING         5000.00 
TURNER       1500.00      0.00
ALLEN        1600.00    300.00
WARD         1250.00    500.00
MARTIN       1250.00   1400.00

14 rows selected

如：空值在后面

SQL> select ename , sal ,comm from emp order by 3 nulls last;

ENAME            SAL      COMM
---------- --------- ---------
TURNER       1500.00      0.00
ALLEN        1600.00    300.00
WARD         1250.00    500.00
MARTIN       1250.00   1400.00
SCOTT        3000.00 
KING         5000.00 
ADAMS        1100.00 
JAMES         950.00 
FORD         3000.00 
MILLER       1300.00 
BLAKE        2850.00 
JONES        2975.00 
SMITH         800.00 
CLARK        2450.00

7.根据条件取不同列中的值排序
如：领导对工资在1000到2000元工资的员工很感兴趣，于是要求这批人排在前面
SQL> select ename as 编码,
  2  	   ename as 姓名,
  3  	   case when sal >=1000 and sal<2000 then 1 else 2 end as 级别,
  4  	   sal as 工资
  5  from   emp
  6  where  deptno = 30
  7  order by 3,4
  8  ;

编码       姓名               级别        工资
---------- ---------- ---------- ---------
MARTIN     MARTIN              1   1250.00
WARD       WARD                1   1250.00
TURNER     TURNER              1   1500.00
ALLEN      ALLEN               1   1600.00
JAMES      JAMES               2    950.00
BLAKE      BLAKE               2   2850.00

6 rows selected

或不显示级别

SQL> select empno as 编码,
  2  	   ename as 姓名,
  3  	   sal as 工资
  4  from emp
  5  where deptno = 30
  6  order by case when sal >=1000 and sal <2000 then 1 else 2 end , 3;

   编码 姓名              工资
----- ---------- ---------
 7654 MARTIN       1250.00
 7521 WARD         1250.00
 7844 TURNER       1500.00
 7499 ALLEN        1600.00
 7900 JAMES         950.00
 7698 BLAKE        2850.00

6 rows selected

------------------------------------------------------------------
1.union all 与空字符串

SQL> select empno as 编码, ename as 名称, nvl(mgr, deptno) as 上级编码
  2    from emp
  3   where empno = 7788
  4  union all
  5  select deptno as 编码, dname as 名称, null as 上级编码
  6    from dept
  7   where deptno = 10;

        编码 名称                 上级编码
---------- -------------- ----------
      7788 SCOTT                7566
        10 ACCOUNTING

当其中一个数据集不够时候，可以用null来填充该列的值
''空字符串本身是varchar2类型，null可以是任何类型

2.union与or

SQL> create index idx_emp_ename on emp(ename);
Index created
SQL> create index idx_emp_empno on emp(ename);
create index idx_emp_empno on emp(ename)
ORA-01408: 此列列表已索引

如：查询员工编号为7788或者名字为SCOTT的员工

SQL> select empno , ename from emp where empno = 7788 or ename = 'SCOTT';

EMPNO ENAME
----- ----------
 7788 SCOTT

执行计划如下：

 Plan Hash Value  : 1272340143 

---------------------------------------------------------------------------------------------
| Id  | Operation                          | Name          | Rows | Bytes | Cost | Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |               |    2 |    20 |    3 | 00:00:01 |
|   1 |   TABLE ACCESS BY INDEX ROWID      | EMP           |    2 |    20 |    3 | 00:00:01 |
|   2 |    BITMAP CONVERSION TO ROWIDS     |               |      |       |      |          |
|   3 |     BITMAP OR                      |               |      |       |      |          |
|   4 |      BITMAP CONVERSION FROM ROWIDS |               |      |       |      |          |
| * 5 |       INDEX RANGE SCAN             | PK_EMP        |      |       |    0 | 00:00:01 |
|   6 |      BITMAP CONVERSION FROM ROWIDS |               |      |       |      |          |
| * 7 |       INDEX RANGE SCAN             | IDX_EMP_ENAME |      |       |    1 | 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 5 - access("EMPNO"=7788)
* 7 - access("ENAME"='SCOTT')

or一般改写为union,去掉重复数据

SQL> select empno, ename from emp where empno = 7788
  2  union
  3  select empno , ename from emp where ename = 'SCOTT';

EMPNO ENAME
----- ----------
 7788 SCOTT

执行计划如下

 Plan Hash Value  : 4119156321 

------------------------------------------------------------------------------------------
| Id  | Operation                       | Name          | Rows | Bytes | Cost | Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |               |    2 |    20 |    5 | 00:00:01 |
|   1 |   SORT UNIQUE                   |               |    2 |    20 |    5 | 00:00:01 |
|   2 |    UNION-ALL                    |               |      |       |      |          |
|   3 |     TABLE ACCESS BY INDEX ROWID | EMP           |    1 |    10 |    1 | 00:00:01 |
| * 4 |      INDEX UNIQUE SCAN          | PK_EMP        |    1 |       |    0 | 00:00:01 |
|   5 |     TABLE ACCESS BY INDEX ROWID | EMP           |    1 |    10 |    2 | 00:00:01 |
| * 6 |      INDEX RANGE SCAN           | IDX_EMP_ENAME |    1 |       |    1 | 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 4 - access("EMPNO"=7788)
* 6 - access("ENAME"='SCOTT')

这样两个语句可以使用empno及ename上的索引

3.组合相关的行

如：显示部门10的员工编码、姓名及所在部门名称和工作地址

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

EMPNO ENAME      DNAME          LOC
----- ---------- -------------- -------------
 7782 CLARK      ACCOUNTING     NEW YORK
 7839 KING       ACCOUNTING     NEW YORK
 7934 MILLER     ACCOUNTING     NEW YORK

4.in、exists和inner join

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

Table created

SQL> desc emp;
Name     Type         Nullable Default Comments 
-------- ------------ -------- ------- -------- 
EMPNO    NUMBER(4)                              
ENAME    VARCHAR2(10) Y                         
JOB      VARCHAR2(9)  Y                         
MGR      NUMBER(4)    Y                         
HIREDATE DATE         Y                         
SAL      NUMBER(7,2)  Y                         
COMM     NUMBER(7,2)  Y                         
DEPTNO   NUMBER(2)    Y                         

SQL> desc emp2
Name  Type         Nullable Default Comments 
----- ------------ -------- ------- -------- 
ENAME VARCHAR2(10) Y                         
JOB   VARCHAR2(9)  Y                         
SAL   NUMBER(7,2)  Y                         
COMM  NUMBER(7,2)  Y

如：返回与表emp2(empno,job,sal)中数据相匹配的emp(empno,ename,job,sal,deptno)信息

SQL> select empno , ename , job , sal ,deptno
  2  from emp
  3  where (ename , job,sal) in (select ename , job ,sal from emp2);

EMPNO ENAME      JOB             SAL DEPTNO
----- ---------- --------- --------- ------
 7369 SMITH      CLERK        800.00     20
 7876 ADAMS      CLERK       1100.00     20
 7900 JAMES      CLERK        950.00     30
 7934 MILLER     CLERK       1300.00     10

执行计划

 Plan Hash Value  : 2994194434 

-----------------------------------------------------------------------------------------
| Id  | Operation                      | Name          | Rows | Bytes | Cost | Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |               |    4 |   160 |    6 | 00:00:01 |
|   1 |   NESTED LOOPS                 |               |      |       |      |          |
|   2 |    NESTED LOOPS                |               |    4 |   160 |    6 | 00:00:01 |
|   3 |     SORT UNIQUE                |               |    4 |    60 |    3 | 00:00:01 |
|   4 |      TABLE ACCESS FULL         | EMP2          |    4 |    60 |    3 | 00:00:01 |
| * 5 |     INDEX RANGE SCAN           | IDX_EMP_ENAME |    1 |       |    0 | 00:00:01 |
| * 6 |    TABLE ACCESS BY INDEX ROWID | EMP           |    1 |    25 |    1 | 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 5 - access("ENAME"="ENAME")
* 6 - filter("JOB"="JOB" AND "SAL"="SAL")

或

SQL> select empno , ename , job , sal , deptno
  2  from emp a
  3  where exists(select null from emp2 b where b.ename=a.ename
  4  and b.job=a.job and b.sal=a.sal);

EMPNO ENAME      JOB             SAL DEPTNO
----- ---------- --------- --------- ------
 7369 SMITH      CLERK        800.00     20
 7876 ADAMS      CLERK       1100.00     20
 7900 JAMES      CLERK        950.00     30
 7934 MILLER     CLERK       1300.00     10

执行计划

 Plan Hash Value  : 2994194434 

-----------------------------------------------------------------------------------------
| Id  | Operation                      | Name          | Rows | Bytes | Cost | Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |               |    4 |   160 |    6 | 00:00:01 |
|   1 |   NESTED LOOPS                 |               |      |       |      |          |
|   2 |    NESTED LOOPS                |               |    4 |   160 |    6 | 00:00:01 |
|   3 |     SORT UNIQUE                |               |    4 |    60 |    3 | 00:00:01 |
|   4 |      TABLE ACCESS FULL         | EMP2          |    4 |    60 |    3 | 00:00:01 |
| * 5 |     INDEX RANGE SCAN           | IDX_EMP_ENAME |    1 |       |    0 | 00:00:01 |
| * 6 |    TABLE ACCESS BY INDEX ROWID | EMP           |    1 |    25 |    1 | 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 5 - access("B"."ENAME"="A"."ENAME")
* 6 - filter("B"."JOB"="A"."JOB" AND "B"."SAL"="A"."SAL")

或

SQL> select a.empno,a.ename,a.job,a.sal,a.deptno from emp a
  2  inner join emp2 b on (b.ename = a.ename and b.job = a.job  and b.sal = a.sal);

EMPNO ENAME      JOB             SAL DEPTNO
----- ---------- --------- --------- ------
 7369 SMITH      CLERK        800.00     20
 7876 ADAMS      CLERK       1100.00     20
 7900 JAMES      CLERK        950.00     30
 7934 MILLER     CLERK       1300.00     10

执行计划

 Plan Hash Value  : 166525280 

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

5.inner join 、left join 、right join和full join解析

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
rifht_5 5          0
right_6 6          0

inner join 特点(只返回两表相匹配的数据)

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

LEFT_STR RIGHT_STR
-------- ---------
left_3   right_3
left_4   right_4

left join特点(左表返回所有数据，右表只返回与左表相匹配数据)

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

LEFT_STR RIGHT_STR
-------- ---------
left_1   
left_2   
left_3   right_3
left_4   right_4

right join特点(右表返回所有数据，左表只返回与右表相匹配数据)

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

LEFT_STR RIGHT_STR
-------- ---------
left_3   right_3
left_4   right_4
         rifht_5
         right_6

full jon 特点(左右表均返回说有数据，但只有相匹配的数据显示同一行，非匹配的行只显示一个表数据)

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

LEFT_STR RIGHT_STR
-------- ---------
left_1   
left_2   
left_3   right_3
left_4   right_4
         rifht_5
         right_6

6 rows selected

6.自关联

select worker.empno as 职工编码,
       worker.ename as 职工姓名,
       worker.job   as 工作,
       worker.mgr   as 员工表_主管编码,
       boss.empno   as 主管表_主管编码,
       boss.ename   as 主管表姓名
  from emp worker, emp boss
 where worker.mgr = boss.empno;

注意:1.这个时候表起别名会报错