SQL中的全外连接

今天看到一贴, 说到 MySQL 不支持全外连接, 希望能给出一个方法能够解决这个问题.

我们首先必须明白在数学领域, set 和 bag 是两种不同的概念. bag 比 set 更加复杂. 主要就是允许了元素的重复出现. 因此 bag 的union, intersect, difference 也就有了些区别.

要模拟全外连接, 我们需要借助: 左外连接(右外连接是左外连接的对称运算...), UNION ALL, EXCEPT ALL.

为什么要用 UNION ALL, EXCEPT ALL? 因为 JOIN 操作产生的结果集是 bag, 因此必须使用 bag 运算符.

我没有花太多时间设计测试用例, 这里只是简单的给出几个错误的结果, 希望大家平时应用小心.

SQL SERVER 2005 虽然支持全外连接, 但是不支持 EXCEPT ALL, 因此无法模拟这个过程. 需要注意使用混合使用 bag 和 set 的运算符将会对结果产生影响. 除非你清楚你的目的, 否则永远不要把它们混合在一起.

下面是测试用例的准备:

CREATE TABLE a(c INT);
CREATE TABLE b(c INT);

INSERT INTO a VALUES(1);
INSERT INTO a VALUES(2);
INSERT INTO a VALUES(3);
INSERT INTO b VALUES(1);
INSERT INTO b VALUES(1);
INSERT INTO b VALUES(2);
INSERT INTO b VALUES(4);

下面是在SQL Server 2005中执行的标准输出结果(5条!): 

SELECT * FROM a FULL JOIN b ON a.c = b.c;

c           c
----------- -----------
1           1
1           1
2           2
3           NULL
NULL        4

(5 row(s) affected)

我们看到了两行(1,1), 这就是我们需要注意的 bag 才具有的行为. 下面是几个典型的错误语句:

SELECT * FROM a LEFT JOIN b ON a.c = b.c UNION SELECT * FROM a RIGHT JOIN b ON a.c = b.c;

c           c
----------- -----------
NULL        4
1           1
2           2
3           NULL

(4 row(s) affected)

SELECT * FROM a LEFT JOIN b ON a.c = b.c UNION ALL SELECT * FROM a RIGHT JOIN b ON a.c = b.c;

c           c
----------- -----------
1           1
1           1
2           2
3           NULL
1           1
1           1
2           2
NULL        4

(8 row(s) affected)

SELECT a.c, b.c FROM a LEFT JOIN b ON a.c = b.c
UNION
SELECT a.c, b.c FROM b LEFT JOIN a ON a.c = b.c
WHERE a.c IS NULL;

c           c
----------- -----------
NULL        4
1           1
2           2
3           NULL

(4 row(s) affected)

正确的应该是:

SELECT * FROM a LEFT JOIN b ON a.c = b.c UNION ALL SELECT * FROM a RIGHT JOIN b ON a.c = b.c EXCEPT ALL SELECT * FROM a INNER JOIN b ON a.c = b.c;

希望大家小心 bag 和 set 的区别. 你还可以指定 DISTINCT 消除重复元组. 但是你必须清楚这些操作的影响, 有人说 bag 操作是 SQL 语言最大的缺点... 我倒觉得仁者见仁, 智者见智. 就好像有人说 '第一范式' 关于不应该有 NULL 值的问题, 认为既然最初允许了 NULL 为什么第一范式就要禁止呢? 

最后大家要注意 wikipedia 上关于 JOIN 的说明并没有考虑 bag 的情况, 而是当作 set 给出的例子. 希望不是 wikipedia 的错误, 端午过后, 我再去仔细求证一下.