Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；
Union All：对两个结果集进行并集操作，包括重复行，不进行排序；

1.创建表

Create table Table1(  
    id int not null,  
    name varchar(20) not null  
)；

Create table Table2(  
    id int not null,  
    name varchar(20) not null  
)；

2.插入测试数据

Insert into Table1 values (1,'heshuyu');
Insert into Table1 values (2,'zhangsan');
Insert into Table1 values (3,'lisi');

Insert into Table2 values (1,'heshuyu');  
Insert into Table2 values (2,'liudehua');
Insert into Table2 values (3,'zhangchao'); 
Insert into Table2 values (4,'zhangxueyou');

3.查看表http://www.cndba.cn/hbhe0316/article/22200http://www.cndba.cn/hbhe0316/article/22200

mysql> select * from Table1;
+----+----------+
| id | name     |
+----+----------+
|  1 | heshuyu  |
|  2 | zhangsan |
|  3 | lisi     |
+----+----------+
3 rows in set (0.00 sec)

mysql> select * from Table2;
+----+-------------+
| id | name        |
+----+-------------+
|  1 | heshuyu     |
|  2 | liudehua    |
|  3 | zhangchao   |
|  4 | zhangxueyou |
+----+-------------+
4 rows in set (0.00 sec)

4.Union查询

mysql> select * from Table1 union select * from Table2;
+----+-------------+
| id | name        |
+----+-------------+
|  1 | heshuyu     |
|  2 | zhangsan    |
|  3 | lisi        |
|  2 | liudehua    |
|  3 | zhangchao   |
|  4 | zhangxueyou |
+----+-------------+
6 rows in set (0.00 sec)

5.Union ALL查询http://www.cndba.cn/hbhe0316/article/22200

mysql> select * from Table1 union all select * from Table2;
+----+-------------+
| id | name        |
+----+-------------+
|  1 | heshuyu     |
|  2 | zhangsan    |
|  3 | lisi        |
|  1 | heshuyu     |
|  2 | liudehua    |
|  3 | zhangchao   |
|  4 | zhangxueyou |
+----+-------------+
7 rows in set (0.00 sec)

版权声明：本文为博主原创文章，未经博主允许不得转载。

MYSQL