39. SQL--union和union all子句

1. 前言

sql union 子句/运算符用于合并两个或者多个 select 语句的结果集。

默认地，union 运算符会过滤掉两个结果集中重复的记录，只保留其中一条，也就是对两个结果集进行并集操作；此外，union 还会按照默认规则对结果集进行排序。

如果您希望保留原始结果，请使用 union all。union all 只是对结果集进行简单粗暴的合并，不会过滤重复的记录，也不会进行排序。

2. 语法

union 子句的基本语法如下：

select column1, column2 ...
from table1, table2
[where condition1]

union / union all

select column1, column2 ...
from table1, table2
[where condition2]

union 运算符使用注意事项：

• 每个 select 语句都必须拥有相同数量的字段；
• 不同 select 语句的对应字段必须拥有相似的类型。

不同 select 语句的字段名不需要相同，sql 会将第一个 select 语句的字段名作为结果集的字段名。

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 |
+-----+---------------------+-------------+--------+

1) 使用 union 合并两个 select 语句的结果，如下所示：

sql> select  id, name, amount, date
   from customers
   left join orders
   on customers.id = orders.customer_id
union
   select  id, name, amount, date
   from customers
   right 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                |
+------+----------+--------+---------------------+

2) 使用 union all 合并两个 select 语句的结果，如下所示：

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;

执行结果：

+------+----------+--------+---------------------+
| 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 |
+------+----------+--------+---------------------+

您看，该结果和以下 FULL JOIN 语句的结果一致：

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

有的数据库不支持 full join，比如 mysql，此时就可以使用 union all 来替代 full join。