UNION 和 UNION ALL

UNION 和 UNION ALL 的主要区别是 UNION ALL 是把结果集直接合并在一起，而 UNION 是将UNION ALL 后的结果进行一次DISTINCT，去除重复记录后的结果。来看下面例子，将 emp 和 dept 表中的部门编号的集合显示出来：

 

mysql> select * from emp;
+--------+------------+---------+--------+
| ename | hiredate | sal | deptno |
+--------+------------+---------+--------+
| zzx | 2000-01-01 | 100.00 | 1 |
| lisa | 2003-02-01 | 400.00 | 2 |
| bjguan | 2004-04-02 | 100.00 | 1 |
| dony | 2005-02-05 | 2000.00 | 4 |
+--------+------------+---------+--------+
4 rows in set (0.00 sec)
mysql> select * from dept;
+--------+----------+
| deptno | deptname |
+--------+----------+
| 1 | tech |
| 2 | sale |
| 5 | fin |
+--------+----------+
3 rows in set (0.00 sec)
mysql> select deptno from emp
-> union all
-> select deptno from dept;
+--------+
| deptno |
+--------+
| 1 |
| 2 |
| 1 |
| 4 |
| 1 |
| 2 |
| 5 |
+--------+
7 rows in set (0.00 sec)

mysql> select deptno from emp
-> union
-> select deptno from dept;
+--------+
| deptno |
+--------+
| 1 |
| 2 |
| 4 |
| 5 |
+--------+
4 rows in set (0.00 sec)