Oracle 9i 中关于外连接的怪异行为

 

 

在偶然一次执行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(+);

   还是错误的结果！

 

   从上面的实验，我们得到了以下结论：

1、  对于SQL92语法的左外连接(left outer join)，WHERE的过滤条件是连接后再过滤的；

2、  对于SQL92语法的左外连接(left outer join)，把过滤条件写在FROM部分和写在WHERE部分效果是不同的；（为什么不同，到底有些什么不同不得而知，只能猜测）

3、  对于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;

 

   对于LEFT OUTER JOIN的怪异行为，还请高手指教。