用scott/tiger登录。
对于外连接， 可以使用“(+)”来表示。 关于使用(+)的一些注意事项：
1.(+)操作符只能出现在where子句中，并且不能与outer join语法同时使用。
2. 当使用(+)操作符执行外连接时，如果在where子句中包含有多个条件，则必须在所有条件中都包含(+)操作符。
3.(+)操作符只适用于列，而不能用在表达式上。
4.(+)操作符不能与or和in操作符一起使用。
5.(+)操作符只能用于实现左外连接和右外连接，而不能用于实现完全外连接。
使用(+)
右外联结
SQL> set autotrace on
SQL> alter system flush buffer_cache;
系统已更改。
SQL> alter system flush shared_pool;
系统已更改。
SQL> select empno, ename, dname, loc from emp, dept where emp.deptno = dept.deptno(+);
EMPNO ENAME DNAME LOC
---------- ---------- -------------- -------------
7369 SMITH RESEARCH DALLAS
7499 ALLEN SALES CHICAGO
7521 WARD SALES CHICAGO
7566 JONES RESEARCH DALLAS
7654 MARTIN SALES CHICAGO
7698 BLAKE SALES CHICAGO
7782 CLARK ACCOUNTING NEW YORK
7788 SCOTT RESEARCH DALLAS
7839 KING ACCOUNTING NEW YORK
7844 TURNER SALES CHICAGO
7876 ADAMS RESEARCH DALLAS
EMPNO ENAME DNAME LOC
---------- ---------- -------------- -------------
7900 JAMES SALES CHICAGO
7902 FORD RESEARCH DALLAS
7934 MILLER ACCOUNTING NEW YORK
已选择14行。
执行计划
----------------------------------------------------------
Plan hash value: 1301846388
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 462 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS OUTER | | 14 | 462 | 4 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | EMP | 14 | 182 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO"(+))
统计信息
----------------------------------------------------------
652 recursive calls
0 db block gets
140 consistent gets
28 physical reads
0 redo size
996 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
10 sorts (memory)
0 sorts (disk)
14 rows processed
SQL>
NESTED LOOPS OUTER就是外联结。
左外联结
SQL> alter system flush buffer_cache;
系统已更改。
SQL> alter system flush shared_pool;
系统已更改。
SQL> select empno, ename, dname, loc from emp, dept where emp.deptno(+) = dept.deptno;
EMPNO ENAME DNAME LOC
---------- ---------- -------------- -------------
7782 CLARK ACCOUNTING NEW YORK
7839 KING ACCOUNTING NEW YORK
7934 MILLER ACCOUNTING NEW YORK
7566 JONES RESEARCH DALLAS
7902 FORD RESEARCH DALLAS
7876 ADAMS RESEARCH DALLAS
7369 SMITH RESEARCH DALLAS
7788 SCOTT RESEARCH DALLAS
7521 WARD SALES CHICAGO
7844 TURNER SALES CHICAGO
7499 ALLEN SALES CHICAGO
EMPNO ENAME DNAME LOC
---------- ---------- -------------- -------------
7900 JAMES SALES CHICAGO
7698 BLAKE SALES CHICAGO
7654 MARTIN SALES CHICAGO
OPERATIONS BOSTON
已选择15行。
执行计划
----------------------------------------------------------
Plan hash value: 2251696546
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 462 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN OUTER | | 14 | 462 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 80 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 14 | 182 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 14 | 182 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("EMP"."DEPTNO"(+)="DEPT"."DEPTNO")
filter("EMP"."DEPTNO"(+)="DEPT"."DEPTNO")
统计信息
----------------------------------------------------------
652 recursive calls
0 db block gets
127 consistent gets
26 physical reads
0 redo size
875 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
11 sorts (memory)
0 sorts (disk)
15 rows processed
SQL>
使用ANSI联结语法的外联结
右外联结
SQL> alter system flush buffer_cache;
系统已更改。
SQL> alter system flush shared_pool;
系统已更改。
SQL> select empno, ename, dname, loc from emp right outer join dept on emp.deptno = dept.deptno;
EMPNO ENAME DNAME LOC
---------- ---------- -------------- -------------
7782 CLARK ACCOUNTING NEW YORK
7839 KING ACCOUNTING NEW YORK
7934 MILLER ACCOUNTING NEW YORK
7566 JONES RESEARCH DALLAS
7902 FORD RESEARCH DALLAS
7876 ADAMS RESEARCH DALLAS
7369 SMITH RESEARCH DALLAS
7788 SCOTT RESEARCH DALLAS
7521 WARD SALES CHICAGO
7844 TURNER SALES CHICAGO
7499 ALLEN SALES CHICAGO
EMPNO ENAME DNAME LOC
---------- ---------- -------------- -------------
7900 JAMES SALES CHICAGO
7698 BLAKE SALES CHICAGO
7654 MARTIN SALES CHICAGO
OPERATIONS BOSTON
已选择15行。
执行计划
----------------------------------------------------------
Plan hash value: 2251696546
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 462 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN OUTER | | 14 | 462 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 80 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 14 | 182 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 14 | 182 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("EMP"."DEPTNO"(+)="DEPT"."DEPTNO")
filter("EMP"."DEPTNO"(+)="DEPT"."DEPTNO")
统计信息
----------------------------------------------------------
652 recursive calls
0 db block gets
127 consistent gets
26 physical reads
0 redo size
875 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
11 sorts (memory)
0 sorts (disk)
15 rows processed
SQL>
左外联结
SQL> alter system flush buffer_cache;
系统已更改。
SQL> alter system flush shared_pool;
系统已更改。
SQL> select empno, ename, dname, loc from emp left outer join dept on emp.deptno = dept.deptno;
EMPNO ENAME DNAME LOC
---------- ---------- -------------- -------------
7369 SMITH RESEARCH DALLAS
7499 ALLEN SALES CHICAGO
7521 WARD SALES CHICAGO
7566 JONES RESEARCH DALLAS
7654 MARTIN SALES CHICAGO
7698 BLAKE SALES CHICAGO
7782 CLARK ACCOUNTING NEW YORK
7788 SCOTT RESEARCH DALLAS
7839 KING ACCOUNTING NEW YORK
7844 TURNER SALES CHICAGO
7876 ADAMS RESEARCH DALLAS
EMPNO ENAME DNAME LOC
---------- ---------- -------------- -------------
7900 JAMES SALES CHICAGO
7902 FORD RESEARCH DALLAS
7934 MILLER ACCOUNTING NEW YORK
已选择14行。
执行计划
----------------------------------------------------------
Plan hash value: 1301846388
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 462 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS OUTER | | 14 | 462 | 4 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | EMP | 14 | 182 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO"(+))
统计信息
----------------------------------------------------------
672 recursive calls
0 db block gets
142 consistent gets
22 physical reads
0 redo size
996 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
10 sorts (memory)
0 sorts (disk)
14 rows processed
SQL>(+)不支持全外联结
SQL> select empno, ename, dname, loc from emp, dept where emp.deptno(+) = dept.deptno(+);
select empno, ename, dname, loc from emp, dept where emp.deptno(+) = dept.deptno(+)
*
第 1 行出现错误:
ORA-01468: 一个谓词只能引用一个外部联接的表
SQL>
只能使用ANSI的full outer join：
SQL> alter system flush buffer_cache;
系统已更改。
SQL> alter system flush shared_pool;
系统已更改。
SQL> select empno, ename, dname, loc from emp full outer join dept on emp.deptno = dept.deptno;
EMPNO ENAME DNAME LOC
---------- ---------- -------------- -------------
7369 SMITH RESEARCH DALLAS
7499 ALLEN SALES CHICAGO
7521 WARD SALES CHICAGO
7566 JONES RESEARCH DALLAS
7654 MARTIN SALES CHICAGO
7698 BLAKE SALES CHICAGO
7782 CLARK ACCOUNTING NEW YORK
7788 SCOTT RESEARCH DALLAS
7839 KING ACCOUNTING NEW YORK
7844 TURNER SALES CHICAGO
7876 ADAMS RESEARCH DALLAS
EMPNO ENAME DNAME LOC
---------- ---------- -------------- -------------
7900 JAMES SALES CHICAGO
7902 FORD RESEARCH DALLAS
7934 MILLER ACCOUNTING NEW YORK
OPERATIONS BOSTON
已选择15行。
执行计划
----------------------------------------------------------
Plan hash value: 312541734
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15 | 555 | 10 (10)| 00:00:01 |
| 1 | VIEW | | 15 | 555 | 10 (10)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
| 3 | NESTED LOOPS OUTER | | 14 | 798 | 4 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | EMP | 14 | 518 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
| 7 | MERGE JOIN ANTI | | 1 | 23 | 6 (17)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 80 | 2 (0)| 00:00:01 |
| 9 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 10 | SORT UNIQUE | | 14 | 42 | 4 (25)| 00:00:01 |
| 11 | TABLE ACCESS FULL | EMP | 14 | 42 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("EMP"."DEPTNO"="DEPT"."DEPTNO"(+))
10 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
filter("EMP"."DEPTNO"="DEPT"."DEPTNO")
统计信息
----------------------------------------------------------
652 recursive calls
0 db block gets
149 consistent gets
29 physical reads
0 redo size
1021 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
11 sorts (memory)
0 sorts (disk)
15 rows processed
SQL>
