分享程序员开发的那些事...
更新时间：2022-08-19 21:46:56
---交叉连接，即是笛卡儿乘积 是一种将一个表的所有数据与另一个表中的所有的数据进行组合的操作。
SQL> select e.ename,d.dname
2 from emp e,dept d;
Execution Plan
----------------------------------------------------------
Plan hash value: 3429684969
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 56 | 896 | 9 (0)| 00:00:01 |
| 1 | MERGE JOIN CARTESIAN| | 56 | 896 | 9 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | DEPT | 4 | 40 | 3 (0)| 00:00:01 |
| 3 | BUFFER SORT | | 14 | 84 | 6 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | EMP | 14 | 84 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------
SQL> select e.ename ,d.dname
2 from emp e cross join dept d; ---ANSI 的标准语法。(提倡使用)
Execution Plan
----------------------------------------------------------
Plan hash value: 3429684969
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 56 | 896 | 9 (0)| 00:00:01 |
| 1 | MERGE JOIN CARTESIAN| | 56 | 896 | 9 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | DEPT | 4 | 40 | 3 (0)| 00:00:01 |
| 3 | BUFFER SORT | | 14 | 84 | 6 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | EMP | 14 | 84 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------
SQL> set autot on
--条件连接，交叉连接加上过滤条件，即只返回符合条件的数据行而不是所有的数据行。条件连接也叫内连接。省略了inner
SQL> select e.ename ,s.grade
2 from emp e,salgrade s
3 where e.sal between s.losal and s.hisal;
Execution Plan
----------------------------------------------------------
Plan hash value: 2200150871
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 8 (25)| 00:00:01 |
| 1 | MERGE JOIN | | 1 | 20 | 8 (25)| 00:00:01 |
| 2 | SORT JOIN | | 5 | 50 | 4 (25)| 00:00:01 |
| 3 | TABLE ACCESS FULL | SALGRADE | 5 | 50 | 3 (0)| 00:00:01 |
|* 4 | FILTER | | | | | |
|* 5 | SORT JOIN | | 14 | 140 | 4 (25)| 00:00:01 |
| 6 | TABLE ACCESS FULL| EMP | 14 | 140 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("E"."SAL" 5 - access("E"."SAL">="S"."LOSAL")
filter("E"."SAL">="S"."LOSAL")
SQL> select e.ename ,s.grade
2 from emp e join salgrade s on e.sal between s.losal and s.hisal;
Execution Plan
----------------------------------------------------------
Plan hash value: 2200150871
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 8 (25)| 00:00:01 |
| 1 | MERGE JOIN | | 1 | 20 | 8 (25)| 00:00:01 |
| 2 | SORT JOIN | | 5 | 50 | 4 (25)| 00:00:01 |
| 3 | TABLE ACCESS FULL | SALGRADE | 5 | 50 | 3 (0)| 00:00:01 |
|* 4 | FILTER | | | | | |
|* 5 | SORT JOIN | | 14 | 140 | 4 (25)| 00:00:01 |
| 6 | TABLE ACCESS FULL| EMP | 14 | 140 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("E"."SAL" 5 - access("E"."SAL">="S"."LOSAL")
filter("E"."SAL">="S"."LOSAL")
---等值连接 也称为自然连接 natural join 。此种连接条件中只有 等于操作。
SQL> select e.ename ,d.dname
2 from emp e,dept d
3 where e.deptno =d.deptno;
Execution Plan
----------------------------------------------------------
Plan hash value: 2865896559
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 308 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 14 | 308 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 52 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 14 | 126 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 14 | 126 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("E"."DEPTNO"="D"."DEPTNO")
filter("E"."DEPTNO"="D"."DEPTNO")
SQL> select e.ename ,d.dname --和前一个sql是一样的。
2 from emp e join dept d on e.deptno =d.deptno;
Execution Plan
----------------------------------------------------------
Plan hash value: 2865896559
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 308 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 14 | 308 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 52 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 14 | 126 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 14 | 126 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("E"."DEPTNO"="D"."DEPTNO")
filter("E"."DEPTNO"="D"."DEPTNO")
SQL>--自连接 表和自己连接。
SQL> select e.ename,m.ename
2 from emp e join emp m on e.mgr=m.empno;
Execution Plan
----------------------------------------------------------
Plan hash value: 2513364761
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13 | 260 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 13 | 260 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 14 | 140 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_EMP | 14 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 13 | 130 | 4 (25)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | EMP | 13 | 130 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("E"."MGR"="M"."EMPNO")
filter("E"."MGR"="M"."EMPNO")
5 - filter("E"."MGR" IS NOT NULL)
SQL> select e.ename ,m.ename
2 from emp e ,emp m
3 where e.mgr=m.empno;
Execution Plan
----------------------------------------------------------
Plan hash value: 2513364761
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13 | 260 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 13 | 260 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 14 | 140 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_EMP | 14 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 13 | 130 | 4 (25)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | EMP | 13 | 130 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("E"."MGR"="M"."EMPNO")
filter("E"."MGR"="M"."EMPNO")
5 - filter("E"."MGR" IS NOT NULL)
SQL> --外连结，拓展了条件连接的结果集。外连结会返回基表的全部数据，即使另一个表中没有与之相匹配的数据行。对于无法匹配的相关数据行的字段，系统以NULL 值显示。
SQL> set autot on
SQL> select e.ename,m.ename
2 from emp e,emp m
3 where e.mgr=m.empno(+);
ENAME ENAME
---------- ----------
FORD JONES
SCOTT JONES
JAMES BLAKE
TURNER BLAKE
MARTIN BLAKE
WARD BLAKE
ALLEN BLAKE
MILLER CLARK
ADAMS SCOTT
CLARK KING
BLAKE KING
JONES KING
SMITH FORD
KING -空值
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1286159638
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 280 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN OUTER | | 14 | 280 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 140 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 140 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E"."MGR"="M"."EMPNO"(+))
--左连接
SQL> select e.ename ,m.ename
2 from emp e left join emp m
3 on e.mgr=m.empno;
ENAME ENAME
---------- ----------
FORD JONES
SCOTT JONES
JAMES BLAKE
TURNER BLAKE
MARTIN BLAKE
WARD BLAKE
ALLEN BLAKE
MILLER CLARK
ADAMS SCOTT
CLARK KING
BLAKE KING
JONES KING
SMITH FORD
KING
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1286159638
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 280 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN OUTER | | 14 | 280 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 140 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 140 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E"."MGR"="M"."EMPNO"(+))
--右连接
SQL> select e.ename ,m.ename
2 from emp e right join emp m on e.mgr =m.empno;
ENAME ENAME
---------- ----------
空值 SMITH
空值 ALLEN
空值 WARD
FORD JONES
SCOTT JONES
空值 MARTIN
TURNER BLAKE
ALLEN BLAKE
WARD BLAKE
JAMES BLAKE
MARTIN BLAKE
MILLER CLARK
ADAMS SCOTT
BLAKE KING
JONES KING
CLARK KING
空值 TURNER
空值 ADAMS
空值 JAMES
SMITH FORD
空值 MILLER
21 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1511908152
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 280 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN OUTER | | 14 | 280 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 14 | 140 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_EMP | 14 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 13 | 130 | 4 (25)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | EMP | 13 | 130 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("E"."MGR"(+)="M"."EMPNO")
filter("E"."MGR"(+)="M"."EMPNO")
5 - filter("E"."MGR"(+) IS NOT NULL)
SQL> select m.ename manager ,e.ename subordinate
2 from emp e full outer join emp m on e.mgr =m.empno;
MANAGER SUBORDINAT
---------- ----------
JONES FORD
JONES SCOTT
BLAKE JAMES
BLAKE TURNER
BLAKE MARTIN
BLAKE WARD
BLAKE ALLEN
CLARK MILLER
SCOTT ADAMS
KING CLARK
KING BLAKE
KING JONES
FORD SMITH
空值 KING
SMITH 空值
ALLEN 空值
WARD 空值
MARTIN 空值
TURNER 空值
ADAMS 空值
JAMES 空值
MILLER 空值
22 rows selected.
--这个执行计划 竟然扫描了四次emp，其中一次ifs
Execution Plan
----------------------------------------------------------
Plan hash value: 3980240577
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 22 | 308 | 13 (16)| 00:00:01 |
| 1 | VIEW --将结果集作为视图 | | 22 | 308 | 13 (16)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
|* 3 | HASH JOIN OUTER | | 14 | 280 | 7 (15)| 00:00:01 |
| 4 | TABLE ACCESS FULL | EMP | 14 | 140 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 14 | 140 | 3 (0)| 00:00:01 |
| 6 | MERGE JOIN ANTI | | 8 | 112 | 6 (17)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID| EMP | 14 | 140 | 2 (0)| 00:00:01 |
| 8 | INDEX FULL SCAN | PK_EMP | 14 | | 1 (0)| 00:00:01 |
|* 9 | SORT UNIQUE | | 13 | 52 | 4 (25)| 00:00:01 |
|* 10 | TABLE ACCESS FULL | EMP | 13 | 52 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("E"."MGR"="M"."EMPNO"(+))
9 - access("E"."MGR"="M"."EMPNO")
filter("E"."MGR"="M"."EMPNO")
10 - filter("E"."MGR" IS NOT NULL)
SQL> --半连接 SEMI JOIN ：当一个表在另一个表找到匹配的记录之后，半连接返回第一张表中的记录。与条件连接相反，
即使在右节点中找到几条匹配的记录，左节点也只会返回同一条记录。另外，右节点中的数据一条也不会返回。半连接使用 EXISTS
IN 作为连接条件
SQL> select deptno ,dname ,loc
2 from dept
3 where deptno in (select deptno from emp);
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
Execution Plan
----------------------------------------------------------
Plan hash value: 2365756639
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 69 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN SEMI | | 3 | 69 | 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 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"="DEPTNO")
filter("DEPTNO"="DEPTNO")
SQL> select deptno ,dname ,loc
2 from dept
3 where exists (select deptno from emp where emp.deptno = dept.deptno );
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
Execution Plan
----------------------------------------------------------
Plan hash value: 2365756639
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 69 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN SEMI | | 3 | 69 | 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 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"="DEPT"."DEPTNO")
filter("EMP"."DEPTNO"="DEPT"."DEPTNO")
SQL>
SQL> --反连接(ANTI JOIN) 是一种比较特殊的半连接，只有在另外一张表找不到匹配记录时才会返回结果，连接条件常以 NOT IN ,NOT EXISTS 出现。
SQL> select deptno ,dname ,loc
2 from dept
3 where not exists (select deptno from emp where emp.deptno =dept.deptno);
DEPTNO DNAME LOC
---------- -------------- -------------
40 OPERATIONS BOSTON
Execution Plan
----------------------------------------------------------
Plan hash value: 3724808082
----------------------------------------------------------------------------------------
| 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| DEPT | 4 | 80 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 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"="DEPT"."DEPTNO")
filter("EMP"."DEPTNO"="DEPT"."DEPTNO")
SQL> select deptno ,dname ,loc
2 from dept
3 where deptno not in (select deptno from emp where emp.deptno =dept.deptno);
DEPTNO DNAME LOC
---------- -------------- -------------
40 OPERATIONS BOSTON
Execution Plan
----------------------------------------------------------
Plan hash value: 3724808082
----------------------------------------------------------------------------------------
| 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| DEPT | 4 | 80 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 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"="DEPT"."DEPTNO")
filter("EMP"."DEPTNO"="DEPT"."DEPTNO")
关于外连接的介绍请参见我的另外的博客：http://space.itpub.net/22664653/viewspace-671023