MySQL_05union合并查询结果集
1.union的作用及用法
union关键字用于合并两个或多个 SELECT 语句的查询结果集。
用法:
select ...
union
select ...
案例:查询工作岗位是CLERK和MANAGER的员工,要求显示员工姓名和工作。
1.使用or或者in来实现:
#mysql> select ename,job from emp where job in(‘CLERK‘,‘MANAGER‘);
mysql> select ename,job from emp where job = ‘CLERK‘ or job = ‘MANAGER‘;
+--------+---------+
| ename | job |
+--------+---------+
| SMITH | CLERK |
| JONES | MANAGER |
| BLAKE | MANAGER |
| CLARK | MANAGER |
| ADAMS | CLERK |
| JAMES | CLERK |
| MILLER | CLERK |
+--------+---------+
7 rows in set (0.00 sec)
2.使用union实现:
mysql> select ename,job from emp where job = ‘CLERK‘
-> union
-> select ename,job from emp where job = ‘MANAGER‘;
+--------+---------+
| ename | job |
+--------+---------+
| SMITH | CLERK |
| ADAMS | CLERK |
| JAMES | CLERK |
| MILLER | CLERK |
| JONES | MANAGER |
| BLAKE | MANAGER |
| CLARK | MANAGER |
+--------+---------+
7 rows in set (0.03 sec)
2.union的效率高
对于表连接来说,每连接一次新表都满足笛卡尔积,匹配的次数成倍的翻。
而union可以减少匹配的次数,还可以完成两个结果集的拼接。
假设现在有a、b、c表各有10条记录
需要从三个表中查询数据
如果只是使用表连接:a表 连接 b表 连接 c表
- 总匹配次数:10 * 10 * 10 = 1000次
如果使用union:a表连接b表 拼接 a表连接c表
- a连接b的匹配次数:10 * 10 = 100次
- a连接c的匹配次数:10 * 10 = 100次
- 总匹配次数:100 + 100 =200次
- union把乘法变成加法
3.union使用时的注意事项
-
3.1每条SELECT语句必须拥有相同数量的列
-
mysql> select ename,job from emp where job = ‘clerk‘ -> union -> select ename from emp where job = ‘manager‘; ERROR 1222 (21000): The used SELECT statements have a different number of columns
-
-
3.2每条SELECT语句的列要求拥有相同的数据类型
-
#虽然在mysql中可以查询出来不同数据类型的,但是oracle要求严格,会报错。 mysql> select ename,job from emp where job = ‘clerk‘ -> union -> select ename,sal from emp where job = ‘clerk‘; +--------+-------+ | ename | job | +--------+-------+ | SMITH | CLERK | | ADAMS | CLERK | | JAMES | CLERK | | MILLER | CLERK | | SMITH | 800 | | ADAMS | 1100 | | JAMES | 950 | | MILLER | 1300 | +--------+-------+ 8 rows in set (0.01 sec)
-
-
3.3每条SELECT语句中的列的顺序要求相同
-
#在mysql中可以查询出来,但是在oracle中会报错 mysql> select ename,job from emp where job = ‘clerk‘ -> union -> select job,ename from emp where job = ‘manager‘; +---------+-------+ | ename | job | +---------+-------+ | SMITH | CLERK | | ADAMS | CLERK | | JAMES | CLERK | | MILLER | CLERK | | MANAGER | JONES | | MANAGER | BLAKE | | MANAGER | CLARK | +---------+-------+ 7 rows in set (0.01 sec)
-