組合查詢union

組合查詢也叫復(fù)合查詢

1、使用場景

以下兩種情況中，可使用組合查詢

• 在單個查詢中從不同的表返回類似結(jié)構(gòu)的數(shù)據(jù)
• 對單個表進(jìn)行多次查詢，按單次查詢返回?cái)?shù)據(jù)

多數(shù)情況下，組合相同表的多次查詢，union與where能完成相同工作，但兩者性能不同，應(yīng)視具體情況二選擇

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)

從以上結(jié)果可以看出，union會自動過濾重復(fù)的記錄，若想展示全部數(shù)據(jù)，則可以使用union all
另外，在一些where語句比較復(fù)雜的情況下，可以直接用union進(jìn)行查詢，可以簡化語句，更利于程序的閱讀