组合查询union

组合查询也叫复合查询

1、使用场景

以下两种情况中，可使用组合查询

• 在单个查询中从不同的表返回类似结构的数据
• 对单个表进行多次查询，按单次查询返回数据

多数情况下，组合相同表的多次查询，union与where能完成相同工作，但两者性能不同，应视具体情况二选择

2、使用举例

#查出职位是clerk或者在10部门工作的员工
#步骤1，查出clerk的员工

MariaDB> select ename,job,deptno from emp where job='clerk';
+--------+-------+--------+
| ename  | job   | deptno |
+--------+-------+--------+
| SIMITH | CLERK |     20 |
| ADAMS  | CLERK |     20 |
| JAMES  | CLERK |     30 |
| MILLER | CLERK |     10 |
+--------+-------+--------+
4 rows in set (0.000 sec)

#步骤2，查出10部分的员工

MariaDB> select ename,job,deptno from emp where deptno='10';
+--------+-----------+--------+
| ename  | job       | deptno |
+--------+-----------+--------+
| CLARK  | MANAGER   |     10 |
| KING   | PRESIDENT |     10 |
| MILLER | CLERK     |     10 |
+--------+-----------+--------+
3 rows in set (0.000 sec)

#直接组合查询
MariaDB> select ename,job,deptno from emp where job='clerk'
         union
         select ename,job,deptno from emp where deptno='10';
+--------+-----------+--------+
| ename  | job       | deptno |
+--------+-----------+--------+
| SIMITH | CLERK     |     20 |
| ADAMS  | CLERK     |     20 |
| JAMES  | CLERK     |     30 |
| MILLER | CLERK     |     10 |
| CLARK  | MANAGER   |     10 |
| KING   | PRESIDENT |     10 |
+--------+-----------+--------+
6 rows in set (0.001 sec)

#不去重，返回所有行，可以使用union all

MariaDB> select ename,job,deptno from emp where job='clerk'
         union all
         select ename,job,deptno from emp where deptno='10';
+--------+-----------+--------+
| ename  | job       | deptno |
+--------+-----------+--------+
| SIMITH | CLERK     |     20 |
| ADAMS  | CLERK     |     20 |
| JAMES  | CLERK     |     30 |
| MILLER | CLERK     |     10 |
| CLARK  | MANAGER   |     10 |
| KING   | PRESIDENT |     10 |
| MILLER | CLERK     |     10 |
+--------+-----------+--------+
7 rows in set (0.001 sec)

#使用where查询

MariaDB> select ename,job,deptno from emp where job='clerk' or deptno='10';
+--------+-----------+--------+
| ename  | job       | deptno |
+--------+-----------+--------+
| SIMITH | CLERK     |     20 |
| CLARK  | MANAGER   |     10 |
| KING   | PRESIDENT |     10 |
| ADAMS  | CLERK     |     20 |
| JAMES  | CLERK     |     30 |
| MILLER | CLERK     |     10 |
+--------+-----------+--------+
6 rows in set (0.000 sec)

从以上结果可以看出，union会自动过滤重复的记录，若想展示全部数据，则可以使用union all
另外，在一些where语句比较复杂的情况下，可以直接用union进行查询，可以简化语句，更利于程序的阅读