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