36. SQL--full join：全连接

1. 前言

sql full join 将返回左表（table1）和右表（table1）中的所有记录，相当于 left join 和 right join 的叠加。

full join 先执行 left join 遍历左表，再执行 right join 遍历右表，最后将 right join 的结果直接追加到 left join 后面。注意，full join 会返回重复的行，它们会被保留，不会被删除。

2. 语法

full join 的基本语法如下：

select table1.column1, table2.column2...
from table1
full join table2
on table1.common_column1 = table2.common_column2;

table1.common_column1 = table2.common_column2 是两个表的连接条件。

以上 SQL 语句将产生 table1 和 table2 的并集，如下图所示：

3. 示例

现在有如下所示的两个表，分别是客户表和订单表。

表1：CUSTOMERS 表

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

表2：ORDERS 表

+-----+---------------------+-------------+--------+
| OID | DATE                | CUSTOMER_ID | AMOUNT |
+-----+---------------------+-------------+--------+
| 102 | 2009-10-08 00:00:00 |           3 |   3000 |
| 100 | 2009-10-08 00:00:00 |           3 |   1500 |
| 101 | 2009-11-20 00:00:00 |           2 |   1560 |
| 103 | 2008-05-20 00:00:00 |           4 |   2060 |
+-----+---------------------+-------------+--------+

现在，让我们使用 FULL JOIN 连接这两个表，如下所示：

sql> select  id, name, amount, date
     from customers
     full join orders
     on customers.id = orders.customer_id;

执行结果：

+------+----------+--------+---------------------+
| ID   | NAME     | AMOUNT | DATE                |
+------+----------+--------+---------------------+
|    1 | Ramesh   |   NULL | NULL                |
|    2 | Khilan   |   1560 | 2009-11-20 00:00:00 |
|    3 | kaushik  |   3000 | 2009-10-08 00:00:00 |
|    3 | kaushik  |   1500 | 2009-10-08 00:00:00 |
|    4 | Chaitali |   2060 | 2008-05-20 00:00:00 |
|    5 | Hardik   |   NULL | NULL                |
|    6 | Komal    |   NULL | NULL                |
|    7 | Muffy    |   NULL | NULL                |
|    3 | kaushik  |   3000 | 2009-10-08 00:00:00 |
|    3 | kaushik  |   1500 | 2009-10-08 00:00:00 |
|    2 | Khilan   |   1560 | 2009-11-20 00:00:00 |
|    4 | Chaitali |   2060 | 2008-05-20 00:00:00 |
+------+----------+--------+---------------------+

如果您希望过滤掉所有 AMOUNT 为 NULL 的记录，那么可以增加 WHERE 子句，如下所示：

sql> select  id, name, amount, date
     from customers
     full join orders
     on customers.id = orders.customer_id
     where amount is not null;

执行结果：

+------+----------+--------+---------------------+
| ID   | NAME     | AMOUNT | DATE                |
+------+----------+--------+---------------------+
|    2 | Khilan   |   1560 | 2009-11-20 00:00:00 |
|    3 | kaushik  |   3000 | 2009-10-08 00:00:00 |
|    3 | kaushik  |   1500 | 2009-10-08 00:00:00 |
|    4 | Chaitali |   2060 | 2008-05-20 00:00:00 |
|    3 | kaushik  |   3000 | 2009-10-08 00:00:00 |
|    3 | kaushik  |   1500 | 2009-10-08 00:00:00 |
|    2 | Khilan   |   1560 | 2009-11-20 00:00:00 |
|    4 | Chaitali |   2060 | 2008-05-20 00:00:00 |
+------+----------+--------+---------------------+

有的数据库不支持 full join，例如 mysql，此时可以使用 union all 将 left join 和 right join 组合起来，如下所示：

sql> select  id, name, amount, date
     from customers
     left join orders
     on customers.id = orders.customer_id
union all
     select  id, name, amount, date
     from customers
     right join orders
     on customers.id = orders.customer_id;