union:可以将查询结果集相加;
案例:找出工作岗位是SALESMAN和MANAGER的员工。
方法一:使用 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 = 'SALESMAN'
union
select ename, job from emp where job = 'MANAGER' ;
三种方法的查询结果是相同的。
+--------+----------+
| ename | job |
+--------+----------+
| ALLEN | SALESMAN |
| WARD | SALESMAN |
| MARTIN | SALESMAN |
| TURNER | SALESMAN |
| JONES | MANAGER |
| BLAKE | MANAGER |
| CLARK | MANAGER |
+--------+----------+
两张不想干的表中的数据拼接在一起显示:
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 |
+------------+
虽然以上结果并没有太大意义,但是这拼接的优势是 or 和 in 关键字所没有的;
使用union需要满足:
(1)要求多条查询语句的查询列数是一致的;
select ename,sal from emp
union
select dname from dept;
如上查询语句,第一句查询了两列,第二句查询了一列,这种方式是要报错的
ERROR 1222 (21000): The used SELECT statements have a different number of columns
(2)要求多条查询语句的查询的每一列的类型和顺序最好一致!