Mysql中Union 和 Union all精解

1. Union

1.1 什么是Union?

UNION is used to combine the result from multiple SELECT statements into a single result set.

1.2 语法要求

The column names from the first SELECT statement are used as the column names for the results
returned.

第一个select语句的列名将会作为结果集的列名返回

Selected columns listed in corresponding positions of each SELECT statement should have the
same data type.

每个select语句选择的对应的列名应该有一个相同的数据格式

The default behavior for UNION is that duplicate rows are removed from the result. With the
optional ALL keyword, duplicate-row removal does not occur and the result includes all matching rows from
all the SELECT statements

Union默认的行为会从结果集中去除相同的行。如果带上可选的ALL关键字，移除重复行这个操作不会发生，并且结果集包含所有的 select语句。

2. 实战案例

2.1 union
  • sql
select 
a from 
(
	select 
	1 as a
)t
union 
select 1 as a
;
  • 执行结果
+---+
| a |
+---+
| 1 |
+---+
1 row in set (0.03 sec)

可以看到结果集中只有一行。

2.2 union all
  • sql
select 
a from 
(
	select 
	1 as a
)t
union all
select 1 as a
;
  • 执行结果
+---+
| a |
+---+
| 1 |
| 1 |
+---+
2 rows in set (0.00 sec)