Outer Join新旧语法对比分析

jboracle1981

先顶个，继续关注。。。

philip_123456

期待。。。

ocpdba591

不知楼主说的结果是不是语句执行顺序问题：

scott@orcl>select e.ename,d.dname from emp e left outer join dept d on d.deptno=e.deptno and e.ename='SCOTT';

ENAME      DNAME
---------- --------------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT      RESEARCH
KING
TURNER
ADAMS
JAMES
FORD
MILLER

已选择14行。


其实上面的语句我们主要关注下on和left outer join的执行顺序就可以理解了：
首先要知道on 是优先于left outer join先执行的
所以on过滤掉的emp表结果，最后又被left outer join拿了回来。

scott@orcl>select e.ename,d.dname from emp e left outer join dept d on e.deptno=d.deptno where e.ename='SCOTT';

ENAME      DNAME
---------- --------------
SCOTT      RESEARCH

上面的语句就不一样了，where 是在left outer join后执行的，所以是最终的过滤结果。

dingjun123

on过滤掉的emp表结果，最后又被left outer join拿了回来???
oracle不会这么傻的吧，其实无须关注顺序问题，重在基础，吉庆说的那个理解了就差不多了。
另外还涉及到outer join的算法问题，9i一般以基表为驱动表，但是到10g就不一定了，先研究基础的，使用上的注意点，然后研究下性能问题

ocpdba591

记住sql语句也是由算术运算符构成的，也是有执行顺序的，详细的可以参见微软的sql server 2005 技术内幕 t-sql查询，第一章说的就是select语句的执行顺序

dingjun123

原帖由 ocpdba591 于 2010-9-4 14:11 发表
这不是傻不傻的问题，sql语句的执行顺序在这个例子中是很重要的，呵呵，我想我的sql基础还是够的

外连接中的on不需要过滤基表数据，过滤基表数据是在where里做的，on只是连接条件，根据连接条件找匹配的从表数据，找不到匹配的从表行，则置空。

dingjun123

原帖由 ocpdba591 于 2010-9-4 14:13 发表
记住sql语句也是由算术运算符构成的，也是有执行顺序的，详细的可以参见微软的sql server 2005 技术内幕 t-sql查询，第一章说的就是select语句的执行顺序

你这个顺序就是的from查询优先，然后where过滤，分组计算---》having---order by之类的吧

sudn

还得认真学习呀

jboracle1981

SQL> select * from a
  2  left join b
  3  on a.id = b.id and b.name like 'x%';


Execution Plan
----------------------------------------------------------
Plan hash value: 1365417139

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     8 |   560 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN OUTER   |      |     8 |   560 |     7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| A    |     8 |   280 |     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"(+))
   3 - filter("B"."NAME"(+) LIKE 'x%')


SQL> select * from a
  2  left join b
  3  on a.id = b.id and a.name like 'x%';


Execution Plan
----------------------------------------------------------
Plan hash value: 2608930719

----------------------------------------------------------------------------
| 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 |    TABLE ACCESS FULL| B    |     1 |    35 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter("A"."ID"="B"."ID" AND "A"."NAME" LIKE 'x%')

-------------------------------------------
on 后面的and 条件是a.name like 还是b.name like看上去都是先过滤掉b表的数据，再做外连接？
如果是a.name like的话是否可以理解成这样条件的("A"."ID"="B"."ID" AND "A"."NAME" LIKE 'x%')
b表数据和a表做外连接？
如果是b.name like的话是否可以理解成这样条件的("A"."ID"="B"."ID" AND "B"."NAME" LIKE 'x%')
b表数据和a表做外连接？

dingjun123

第1个 1 - access("A"."ID"="B"."ID"(+))
   3 - filter("B"."NAME"(+) LIKE 'x%')
可以很容易用旧语法改写，from a,b where a.id=b.id(+) and b.name(+) like 'x%';

第2个用老语法改写就麻烦了：相当于按照连接条件到从表里找，a.id=b.id,找到了，再看匹配后的记录主表a.name是否like 'x%',如果否，则没有匹配上，从表的记录为NULL，找到就显示从表数据，用老语法改写为
   
select * from a,b where a.id=(case when a.name like 'x%' then b.id(+) end);