SQL基础（廿一）---UNION

UNION 规则：

1. 每个结果集必须有相同的列数；
2. 每个结果集的列必须类型相容。

初始化条件：

mysql> CREATE TABLE T_TempEmployee (FIdCardNumber VARCHAR(20),FName VARCHAR(20),FAge
INT ,PRIMARY KEY (FIdCardNumber))
    -> ;
Query OK, 0 rows affected

mysql> INSERT INTO T_TempEmployee(FIdCardNumber,FName,FAge)
VALUES('1234567890121','Sarani',33);
INSERT INTO T_TempEmployee(FIdCardNumber,FName,FAge)
VALUES('1234567890122','Tom',26);
INSERT INTO T_TempEmployee(FIdCardNumber,FName,FAge)
VALUES('1234567890123','Yalaha',38);
INSERT INTO T_TempEmployee(FIdCardNumber,FName,FAge)
VALUES('1234567890124','Tina',26);
INSERT INTO T_TempEmployee(FIdCardNumber,FName,FAge)
VALUES('1234567890125','Konkaya',29);
INSERT INTO T_TempEmployee(FIdCardNumber,FName,FAge)
VALUES('1234567890126','Fo fa' ,46) ;
Query OK, 1 row affected

Query OK, 1 row affected

Query OK, 1 row affected

Query OK, 1 row affected

Query OK, 1 row affected

Query OK, 1 row affected

mysql> select * from t_tempemployee;
+---------------+---------+------+
| FIdCardNumber | FName   | FAge |
+---------------+---------+------+
| 1234567890121 | Sarani  |   33 |
| 1234567890122 | Tom     |   26 |
| 1234567890123 | Yalaha  |   38 |
| 1234567890124 | Tina    |   26 |
| 1234567890125 | Konkaya |   29 |
| 1234567890126 | Fo fa   |   46 |
+---------------+---------+------+
6 rows in set

简单结果集联合：

mysql> select * from t_tempemployee;
+---------------+---------+------+
| FIdCardNumber | FName   | FAge |
+---------------+---------+------+
| 1234567890121 | Sarani  |   33 |
| 1234567890122 | Tom     |   26 |
| 1234567890123 | Yalaha  |   38 |
| 1234567890124 | Tina    |   26 |
| 1234567890125 | Konkaya |   29 |
| 1234567890126 | Fo fa   |   46 |
+---------------+---------+------+
6 rows in set

mysql> select * from t_employee;
+----------+-------+------+---------+-------------+---------------+
| fnumber  | fname | fage | fsalary | fsubcompany | fdepartment   |
+----------+-------+------+---------+-------------+---------------+
| DEV001   | Tom   |   25 | 8300    | Beijing     | Development   |
| DEV002   | Jerry |   28 | 2300.8  | ShenZhen    | Development   |
| HR001    | Jane  |   23 | 2200.88 | Beijing     | HumanResource |
| HR002    | Tina  |   25 | 5200.36 | Beijing     | HumanResource |
| IT001    | Smith |   28 | 3900    | Beijing     | InfoTech      |
| IT002    | NULL  |   27 | 2800    | ShenZhen    | InfoTech      |
| SALES001 | John  |   23 | 5000    | Beijing     | Sales         |
| SALES002 | Kerry |   28 | 6200    | Beijing     | Sales         |
| SALES003 | Stone |   22 | 1200    | ShenZhen    | Sales         |
+----------+-------+------+---------+-------------+---------------+
9 rows in set

mysql> select fnumber,fname,fage from t_employee
    -> union
    -> select fidcardnumber,fname,fage from t_tempemployee;
+---------------+---------+------+
| fnumber       | fname   | fage |
+---------------+---------+------+
| DEV001        | Tom     |   25 |
| DEV002        | Jerry   |   28 |
| HR001         | Jane    |   23 |
| HR002         | Tina    |   25 |
| IT001         | Smith   |   28 |
| IT002         | NULL    |   27 |
| SALES001      | John    |   23 |
| SALES002      | Kerry   |   28 |
| SALES003      | Stone   |   22 |
| 1234567890121 | Sarani  |   33 |
| 1234567890122 | Tom     |   26 |
| 1234567890123 | Yalaha  |   38 |
| 1234567890124 | Tina    |   26 |
| 1234567890125 | Konkaya |   29 |
| 1234567890126 | Fo fa   |   46 |
+---------------+---------+------+
15 rows in set

结果集按条件联合：

mysql> select fnumber,fname,fage from t_employee where fage<30
    -> union
    -> select fidcardnumber,fname,fage from t_tempemployee where fage>40
    -> union
    -> select fidcardnumber,fname,fage from t_tempemployee where fage<30;
+---------------+---------+------+
| fnumber       | fname   | fage |
+---------------+---------+------+
| DEV001        | Tom     |   25 |
| DEV002        | Jerry   |   28 |
| HR001         | Jane    |   23 |
| HR002         | Tina    |   25 |
| IT001         | Smith   |   28 |
| IT002         | NULL    |   27 |
| SALES001      | John    |   23 |
| SALES002      | Kerry   |   28 |
| SALES003      | Stone   |   22 |
| 1234567890126 | Fo fa   |   46 |
| 1234567890122 | Tom     |   26 |
| 1234567890124 | Tina    |   26 |
| 1234567890125 | Konkaya |   29 |
+---------------+---------+------+
13 rows in set

规则一实例：

mysql> select fnumber,fname,fage,fdepartment from t_employee
    -> union
    -> select fidcardnumber,fname,fage from t_tempemployee;
1222 - The used SELECT statements have a different number of columns
mysql> select fnumber,fname,fage,fdepartment from t_employee
    -> union
    -> select fidcardnumber,fname,fage,'临时工，不属于任何一个部门' from t_tempemployee;
+---------------+---------+------+----------------------------+
| fnumber       | fname   | fage | fdepartment                |
+---------------+---------+------+----------------------------+
| DEV001        | Tom     |   25 | Development                |
| DEV002        | Jerry   |   28 | Development                |
| HR001         | Jane    |   23 | HumanResource              |
| HR002         | Tina    |   25 | HumanResource              |
| IT001         | Smith   |   28 | InfoTech                   |
| IT002         | NULL    |   27 | InfoTech                   |
| SALES001      | John    |   23 | Sales                      |
| SALES002      | Kerry   |   28 | Sales                      |
| SALES003      | Stone   |   22 | Sales                      |
| 1234567890121 | Sarani  |   33 | 临时工，不属于任何一个部门 |
| 1234567890122 | Tom     |   26 | 临时工，不属于任何一个部门 |
| 1234567890123 | Yalaha  |   38 | 临时工，不属于任何一个部门 |
| 1234567890124 | Tina    |   26 | 临时工，不属于任何一个部门 |
| 1234567890125 | Konkaya |   29 | 临时工，不属于任何一个部门 |
| 1234567890126 | Fo fa   |   46 | 临时工，不属于任何一个部门 |
+---------------+---------+------+----------------------------+
15 rows in set

规则二实例：

mysql> select fnumber,fname,fage from t_employee
    -> union
    -> select fidcardnumber,fage,fname from t_tempemployee;
+---------------+-------+---------+
| fnumber       | fname | fage    |
+---------------+-------+---------+
| DEV001        | Tom   | 25      |
| DEV002        | Jerry | 28      |
| HR001         | Jane  | 23      |
| HR002         | Tina  | 25      |
| IT001         | Smith | 28      |
| IT002         | NULL  | 27      |
| SALES001      | John  | 23      |
| SALES002      | Kerry | 28      |
| SALES003      | Stone | 22      |
| 1234567890121 | 33    | Sarani  |
| 1234567890122 | 26    | Tom     |
| 1234567890123 | 38    | Yalaha  |
| 1234567890124 | 26    | Tina    |
| 1234567890125 | 29    | Konkaya |
| 1234567890126 | 46    | Fo fa   |
+---------------+-------+---------+
15 rows in set

注：MYSQL将FAGE转换为文本类型