Oracle的left outer join查询(转)

在偶然一次执行Oracle 9i的left outer join查询时，发现了一些匪夷所思的问题，在此贴出来和大家讨论一下。
先创建一些表和记录来模拟当时的情况：
create table temp_test1
(
       a number(10)

);
INSERT INTO temp_test1 VALUES(1);
INSERT INTO temp_test1 VALUES(2);
INSERT INTO temp_test1 VALUES(3);
INSERT INTO temp_test1 VALUES(4);
COMMIT;
 
create table temp_test2
(
       b number(10),

       c varchar2(2)

);
INSERT INTO temp_test2 VALUES(1, 'a');
INSERT INTO temp_test2 VALUES(2, 'a');
INSERT INTO temp_test2 VALUES(3, 'a');
INSERT INTO temp_test2 VALUES(5, 'b');
COMMIT;

 
好了，我们来执行一个左外连接：
--最初想得到的结果
SELECT a, b, c
FROM temp_test1 LEFT OUTER JOIN temp_test2 ON temp_test1.a=temp_test2.b;
 

结果如下：
A B C
1 1 1 a
2 2 2 a
3 3 3 a
4 4

(预想的结果)
OK，在后面画蛇添足地加上个过滤条件：
--在temp_test2加一个条件(先连接，后过滤，结果少了一列)
SELECT a, b, c
FROM temp_test1 LEFT OUTER JOIN temp_test2 ON temp_test1.a=temp_test2.b

WHERE c='a';

结果居然少了一条：
A B C
1 1 1 a
2 2 2 a
3 3 3 a

(错误的结果)
   从这个现象看，说明左外连接的时候，是先把两个表join起来，然后在合成后的结果集里面再做过滤。
再换种写法试试：
--在temp_test2加一个条件，换一种写法（似乎是过滤了再连接）
SELECT a, b, c
FROM temp_test1 LEFT OUTER JOIN temp_test2

ON temp_test1.a=temp_test2.b AND temp_test2.C='a';

   神了，用这种方法居然是最初预想的结果。从这里看，似乎是在单表上过滤了再连接的。
   不服气，把条件的顺序换了看：
SELECT a, b, c
FROM temp_test1 LEFT OUTER JOIN temp_test2

ON temp_test2.C='a' AND temp_test1.a=temp_test2.b;

   还是我们预想的结果，看来条件的顺序和结果无关。
   恩，越来越有趣了，再换写法，使用SQL89的连接语法：
--用另一种语法进行左连接
SELECT a,b,c
FROM temp_test1, temp_test2
WHERE temp_test1.a=temp_test2.b(+)

   效果和left outer join果然是一样的！（废话）
   加上画蛇添足的条件试试：
--用另一种语法进行左连接，加上过滤条件，发现是连接后再过滤
SELECT a,b,c
FROM temp_test1, temp_test2
WHERE temp_test1.a=temp_test2.b(+) AND temp_test2.C='a';
 

   唉！错误的结果！再换条件的顺序：
--用另一种语法进行左连接，修改条件的顺序，发现还是连接后再过滤
SELECT a,b,c
FROM temp_test1, temp_test2
WHERE temp_test2.C='a' AND temp_test1.a=temp_test2.b(+);
 

   还是错误的结果！
 
   从上面的实验，我们得到了以下结论：
对于SQL92语法的左外连接(left outer join)，WHERE的过滤条件是连接后再过滤的；
对于SQL92语法的左外连接(left outer join)，把过滤条件写在FROM部分和写在WHERE部分效果是不同的；（为什么不同，到底有些什么不同不得而知，只能猜测）
对于SQL89语法的左外连接( =(+) )，都是连接后再过滤的。

 
经过测试，RIGHT OUTER JOIN的表现与LEFT OUTER JOIN是一致的。
对于LEFT OUTER JOIN如此怪异的行为，我推荐一种比较“安全”的写法，强制在语法中体现我们的意图：
--强制性地过滤后再连接
WITH
 result1 AS
 (
      SELECT b, c FROM temp_test2 WHERE c='a'

 )
SELECT a,b,c
FROM temp_test1 LEFT OUTER JOIN result1 ON temp_test1.a=result1.b;