MySQL--外连接的用法

本文根据《SQL进阶教程》（[日]MICK/著 吴炎昌/译）所写笔记。
数据库的一个难题是无法将SQL语句的执行结果转换为想要的格式。本节，我们将通过学习格式转换中具有代表性的行列转换和嵌套式侧栏的生成方法，深入理解一下其中骑着重要作用的外连接。

全外连接

外连接有三种：左外连接、右外连接、全外连接
其中，左外连接和右外连接没有什么功能上的区别。用作主表的表写在运算符左边时用左外连接，写在运算符右边时用右外连接。
现在，我们先用实际例子来体验一下什么是全外连接。首先我们先创建两张表：

CREATE TABLE Class_A
(id char(1), 
 name varchar(30), 
 PRIMARY KEY(id));

CREATE TABLE Class_B
(id   char(1), 
 name varchar(30), 
 PRIMARY KEY(id));

INSERT INTO Class_A (id, name) VALUES('1', '田中');
INSERT INTO Class_A (id, name) VALUES('2', '铃木');
INSERT INTO Class_A (id, name) VALUES('3', '伊集院');

INSERT INTO Class_B (id, name) VALUES('1', '田中');
INSERT INTO Class_B (id, name) VALUES('2', '铃木');
INSERT INTO Class_B (id, name) VALUES('4', '西园寺');

mysql> select * from class_a;
+----+-----------+
| id | name      |
+----+-----------+
| 1  | 田中      |
| 2  | 铃木      |
| 3  | 伊集院    |
+----+-----------+

mysql> select * from class_b;
+----+-----------+
| id | name      |
+----+-----------+
| 1  | 田中      |
| 2  | 铃木      |
| 4  | 西园寺    |
+----+-----------+

在这两张表里，田中和铃木同时属于两张表，而伊集院和西园寺只属于其中一张表，全外连接能够从这样两张内容不一致的表里，没有遗漏地获取全部信息的方法，所以也可以理解成“把两张表都当作主表来使用” 的连接。

mysql> select coalesce(a.id,b.id) as id,a.name as a_name,b.name as b_name
    -> from class_a a full outer join class_b
    -> on a.id=b.id;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'full outer join class_b
on a.id=b.id' at line 2    

执行错误，因为MySQL中不支持外连接，所以可以分别进行左外连接和右外连接，再把两个结果通过union合并起来。

mysql> select a.id as id,a.name,b.name
    -> from class_a a left outer join class_b b
    -> on a.id=b.id
    -> union select b.id as id ,a.name,b.name
    -> from class_a a right outer join class_b b
    -> on a.id=b.id;
+----+-----------+-----------+
| id | name      | name      |
+----+-----------+-----------+
| 1  | 田中      | 田中      |
| 2  | 铃木      | 铃木      |
| 3  | 伊集院    | NULL      |
| 4  | NULL      | 西园寺    |
+----+-----------+-----------+

我们换个角度，把表连接堪称集合运算：

• 内连接相当于求集合的积（intersect）
• 外连接相当于求集合的和（union）

用外连接进行集合运算

伊集院在A班里存在而在B班里不存在b_name列的值为null。西园寺在B班里存在而在A班里不存在，a_name列的值是null。于是我们可以通过判断连接后的相关字段是否为null来求得差集。

用外连接求差集：A-B

mysql> select a.id as id,a.name as a_name
    -> from class_a a left outer join class_b b
    -> on a.id=b.id
    -> where b.name is null;

用外连接求差集：B-A

mysql> select b.id as id,b.name as b_name
    -> from class_a a right outer join class_b b
    -> on a.id=b.id
    -> where a.name is null;
+----+-----------+
| id | b_name    |
+----+-----------+
| 4  | 西园寺    |
+----+-----------+

用全外连接求异或集

什么是异或：
如果a、b两个值不相同，则异或结果为1。如果a、b两个值相同，异或结果为0。

mysql> select coalesce(a.id,b.id) as id,
    ->        coalesce(a.name,b.name) as name
    -> from class_a a full outer join class_b b
    -> on a.id=b.id
    -> where a.name is null or b.name is null;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'full outer join class_b b
on a.id=b.id
where a.name is null or b.name is null' at line 3

MySQL中不支持全连接，将代码改成如下:

mysql> select a.id as id,a.name as name
    -> from class_a a left outer join class_b b
    -> on a.id=b.id
    -> where b.name is null
    -> union select b.id as id,b.name as name
    -> from class_a a right outer join class_b b
    -> on a.id=b.id
    -> where a.name is null;
+----+-----------+
| id | name      |
+----+-----------+
| 3  | 伊集院    |
| 4  | 西园寺    |
+----+-----------+