outer join 的执行顺序

anlinew

select s1.name, s2.name from s1 left join s2 on s1.id=s2.id and s2.id=2;
vs
select s1.name, s2.name from s1 left join s2 on s1.id=s2.id where s2.id=2;

[ 本帖最后由 anlinew 于 2008-3-19 16:13 编辑 ]

fjmingyang

SQL> select s1.name, s2.name from s1,s2 where s1.id=s2.id and s2.id=2;

NAME                 NAME
-------------------- --------------------
b                    b


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   MERGE JOIN
   2    1     SORT (JOIN)
   3    2       TABLE ACCESS (FULL) OF 'S2'
   4    1     SORT (JOIN)
   5    4       TABLE ACCESS (FULL) OF 'S1'




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         14  consistent gets
          0  physical reads
          0  redo size
        428  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select s1.name, s2.name from s1,s2 where s1.id=s2.id(+) and s2.id=2;

NAME                 NAME
-------------------- --------------------
b                    b


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   FILTER
   2    1     MERGE JOIN (OUTER)
   3    2       SORT (JOIN)
   4    3         TABLE ACCESS (FULL) OF 'S1'
   5    2       SORT (JOIN)
   6    5         TABLE ACCESS (FULL) OF 'S2'




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         14  consistent gets
          0  physical reads
          0  redo size
        428  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select s1.name, s3.name from s1,(select * from s2 where s2.id=2 ) s3 where
s1.id=s3.id(+);

NAME                 NAME
-------------------- --------------------
a
b                    b
c
d


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   MERGE JOIN (OUTER)
   2    1     SORT (JOIN)
   3    2       TABLE ACCESS (FULL) OF 'S1'
   4    1     SORT (JOIN)
   5    4       TABLE ACCESS (FULL) OF 'S2'




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         14  consistent gets
          0  physical reads
          0  redo size
        478  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          4  rows processed

SQL>

三个语句的执行计划如上，各位给分析下

bluemoon0083

这个就是外连接的概念啊，不用看执行计划就应该知道结果

左连接或者右连接，就是其中一个表的符合连接条件的记录取出来，不符合的用null表示，并不是简单的相等连接，不是=关系

anlinew

原帖由 anlinew 于 2008-3-19 16:11 发表
select s1.name, s2.name from s1 left join s2 on s1.id=s2.id and s2.id=2;
vs
select s1.name, s2.name from s1 left join s2 on s1.id=s2.id where s2.id=2;

select s1.name, s2.name from s1,s2 where s1.id=s2.id(+) and s2.id(+)=2;
vs
select s1.name, s2.name from s1,s2 where s1.id=s2.id(+) and s2.id=2;

fjmingyang

原帖由 anlinew 于 2008-3-19 16:05 发表
select s1.name, s2.name from s1,s2 where s1.id=s2.id(+) and s2.id(+)=2;

请解释下，不是很理解

bluemoon0083

sql语句决定执行计划，并不是执行计划决定sql语句

fjmingyang

原帖由 fjmingyang 于 2008-3-19 16:23 发表



请解释下，不是很理解

If the WHERE clause contains a condition that compares a column from table B with a constant, then the (+) operator must be applied to the column so that Oracle returns the rows from table A for which it has generated nulls for this column. Otherwise Oracle returns only the results of a simple join.

fikong2005

看来外连接确实令一些人产生一些迷惑
anlinew 已经说的很明白了，
ON  XX=XX AND XX=XX 和 ON XX=XX WHERE XX=XX得到的结果是不同的。
前者的话无论被外连接的表条件如何，始终是以主表为准，副表为空的则为null
而后者，因为在where后面XX=XX，所以会把副表为null的行去掉。
你仔细换换各种写法，就会体会的到的啦。