MySQL 集合操作

UNION [DISTINCT]

UNION ALL

语法：

SELECT ... 
UNION [ALL | DISTINCT] 
SELECT ... 
[UNION [ALL | DISTINCT] 
SELECT ...]

union用于把两个或者多个select查询的结果集合并成一个。

进行合并的两个查询，其SELECT列表必须在数量和对应列的数据类型上保持一致
默认会去掉两个查询结果集中的重复行
默认结果集不排序
最终结果集的列名来自于第一个查询的SELECT列表
UNION = UNION DISTINCT
例：得到那些有罚款或者担任队长，或者两个条件都符合的球员的编号？

root@TENNIS 16:18  mysql>SELECT playerno
    ->    FROM TEAMS
    -> UNION
    ->   SELECT playerno
    ->     FROM PENALTIES;
+----------+
| playerno |
+----------+
|        6 |
|       27 |
|        7 |
|       39 |
|      112 |
|       44 |
|      104 |
|        8 |
+----------+
8 rows in set (0.01 sec)


root@TENNIS 16:18  mysql>SELECT playerno
    ->    FROM PENALTIES
    -> UNION
    ->   SELECT playerno,division
    ->     FROM TEAMS;
ERROR 1222 (21000): The used SELECT statements have a different number of columns


# 得到那些有罚款或者担任队长，或者住在Stratford的球员的编号。
root@TENNIS 16:22  mysql>SELECT playerno
    ->   FROM TEAMS
    -> UNION
    -> SELECT playerno
    ->   FROM PENALTIES
    -> UNION 
    -> SELECT playerno
    ->   FROM PLAYERS
    ->   WHERE town='Stratford';
+----------+
| playerno |
+----------+
|        6 |
|       27 |
|        7 |
|       39 |
|      112 |
|       44 |
|      104 |
|        8 |
|        2 |
|       83 |
|      100 |
+----------+
11 rows in set (0.00 sec)

如果要对合并后的整个结果集进行排序，ORDER BY子句只能出现在最后面的查询中。

root@TENNIS 16:19  mysql>SELECT playerno
    ->    FROM TEAMS
    -> UNION
    -> SELECT playerno
    ->    FROM PENALTIES
    ->    ORDER BY playerno;
+----------+
| playerno |
+----------+
|        6 |
|        7 |
|        8 |
|       27 |
|       39 |
|       44 |
|      104 |
|      112 |
+----------+
8 rows in set (0.00 sec)

如果要合并的某个查询需要单独进行排序，那么该查询必须用小括号括起来。
例：第一个和第二个查询的结果集分别排序后再进行合并，最终的结果集不排序

root@TENNIS 16:20  mysql>(SELECT playerno
    ->    FROM TEAMS
    ->    ORDER BY playerno)
    -> UNION
    -> (SELECT playerno
    ->    FROM PENALTIES
    ->    ORDER BY playerno);
+----------+
| playerno |
+----------+
|        6 |
|       27 |
|        7 |
|       39 |
|      112 |
|       44 |
|      104 |
|        8 |
+----------+
8 rows in set (0.00 sec)

UNION  ALL和UNION的区别是：前者不去掉结果集中重复的行。

root@TENNIS 16:21  mysql>SELECT playerno
    ->    FROM TEAMS
    -> UNION ALL
    ->   SELECT playerno
    ->     FROM PENALTIES;
+----------+
| playerno |
+----------+
|        6 |
|       27 |
|        7 |
|       27 |
|       39 |
|      112 |
|        6 |
|       44 |
|       27 |
|      104 |
|       44 |
|        8 |
|       44 |
|       27 |
|        6 |
|       44 |
|       27 |
|       27 |
+----------+
18 rows in set (0.00 sec)

集合运算符和NULL值
在去重操作时，如果列值中包含NULL值，认为它们是相等的。
例：最终结果集中只有1行

root@TENNIS 16:22  mysql>SELECT playerno,leagueno
    ->    FROM PLAYERS
    ->    WHERE playerno=7   
    -> UNION
    -> SELECT playerno,leagueno
    ->    FROM PLAYERS 
    ->    WHERE playerno=7;
+----------+----------+
| playerno | leagueno |
+----------+----------+
|        7 | NULL     |
+----------+----------+
1 row in set (0.00 sec)