oracle先 内连接之后再left outer join,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;