9、union 连接多个结果集到一个结果集中

union 本意：联盟 组合

作用：连接两个以上的 SELECT 语句的结果组合到一个结果集合中，多个 SELECT 语句会删除重复的数据。

注意点：union前面的语句和后面的语句 查询字段数需要相同

需求：找出工作是a(sellman)和b(manager)的员工

+-------+--------+-----------+------+------------+------+------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL  | COMM | DEPTNO |
+-------+--------+-----------+------+------------+------+------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800 | NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600 |  300 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250 |  500 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975 | NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250 | 1400 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850 | NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450 | NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000 | NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500 |    0 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100 | NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950 | NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000 | NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300 | NULL |     10 |
+-------+--------+-----------+------+------------+------+------+--------+

//第一种 or
select ename,job from emp where job = 'salesman' or job = 'manager';

//第二种 in
select ename,job from emp where job in('salesman','manager');

//第三种 union
select ename,job from emp where job = 'manager'
union
select ename,job from emp where job = 'salesman';

 

union 可以解决，但是or和in解决不了的场景

把两张不相干的表 数据拼到一起

select ename from emp
union
select dname from dept;

//把不相干的信息拼到一起了
+------------+
| ename      |
+------------+
| SMITH      | 员工名称
| ALLEN      |
| WARD       |
| JONES      |
| MARTIN     |
| BLAKE      |
| CLARK      |
| SCOTT      |
| KING       |
| TURNER     |
| ADAMS      |
| JAMES      |
| FORD       |
| MILLER     |
| ACCOUNTING | 部门名称
| RESEARCH   |
| SALES      |
| OPERATIONS |
+------------+

 

1.通过以上信息可以看出，UNION 操作符合并的结果集，不允许重复值；UNION ALL允许有重复值。
2.但是UNION 会将各查询子集的记录做比较，所以相对于UNION ALL来说 ，UNION 速度会慢上许多。一般来说，在确保查询数值不会重复的前提下，要用UNION ALL。