稍微研究了一下 oracle 自己的join 和标准的join。主要表现在on ,where 关键字所起的作用不同,和连接本身的特性。
yang@ORACL> set autotrace on
yang@ORACL> select *
2 from a,b
3 where a.id=b.id(+) and a.name like 'x%';
ID NAME ID NAME
---------- ----- ---------- -----
1 x1 1 x1
2 x2 2 x2
4 x4
3 x3
执行计划
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 280 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN OUTER | | 4 | 280 | 7 (15)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| A | 4 | 140 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| B | 2 | 70 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."ID"="B"."ID"(+))
2 - filter("A"."NAME" LIKE 'x%')
由执行计划可以看出先对a表进行过滤,让后进行与b表的left join,
--
yang@ORACL> select * from a left join b
2 on a.id=b.id and a.name like 'x%';
ID NAME ID NAME
---------- ----- ---------- -----
1 x1 1 x1
2 x2 2 x2
3 x3
4 x4
1 y1
2 y2
3 y3
4 y4
已选择8行。
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 560 | 27 (0)| 00:00:01 |
| 1 | NESTED LOOPS OUTER | | 8 | 560 | 27 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | A | 8 | 280 | 3 (0)| 00:00:01 |
| 3 | VIEW | | 1 | 35 | 3 (0)| 00:00:01 |
|* 4 | FILTER | | | | | |
|* 5 | TABLE ACCESS FULL| B | 1 | 35 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("A"."NAME" LIKE 'x%')
5 - filter("A"."ID"="B"."ID")
-- 先连接后筛选 (由 Table A 驱动 NESTED LOOPS OUTER ,每一次循环筛选记录
外连接中的on不需要过滤基表数据,过滤基表数据是在where里做的,on只是连接条件,根据连接条件找匹配的从表数据,找不到匹配的从表行,则置空。
yang@ORACL> select * from a left join b
2 on a.id=b.id
3 where a.name like 'x%';
ID NAME ID NAME
---------- ----- ---------- -----
1 x1 1 x1
2 x2 2 x2
4 x4
3 x3
执行计划
----------------------------------------------------------
Plan hash value: 1365417139
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 280 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN OUTER | | 4 | 280 | 7 (15)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| A | 4 | 140 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| B | 2 | 70 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."ID"="B"."ID"(+))
2 - filter("A"."NAME" LIKE 'x%')
Note
-----
- dynamic sampling used for this statement
yang@ORACL> select * from a left join b
2 on a.name like 'x%'
3 where a.id=b.id;
ID NAME ID NAME
---------- ----- ---------- -----
1 x1 1 x1
2 x2 2 x2
执行计划
----------------------------------------------------------
Plan hash value: 652036164
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 70 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 70 | 7 (15)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| A | 1 | 35 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| B | 2 | 70 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."ID"="B"."ID")
2 - filter("A"."NAME" LIKE 'x%')
需要说明的是:
on关键字,的确是连接条件,它不能过滤基表。过滤不了的原因是on过滤掉的emp表结果,最后又被left outer join拿了回来。
select e.ename,d.dname from emp e left outer join dept d on d.deptno=e.deptno and e.ename='SCOTT';
伪代码
for rec_e in ( select ename from emp e ) loop
for rec_d in ( select dname from dept d where d.deptno = rec_e.deptno ) loop
if rec_d != NULL and rec_e..ename='SCOTT' then
show (rec_e.ename,rec_d.name);
else
show (rec_e.ename,null); --on过滤掉的emp表结果,最后又被left outer join拿了回来。
end if;
end loop;
end loop;
--伪代码部分内容参考了puber ccsnmoracle 的表述