Mysql中Union和Join的分别应用（主讲Union）

一、MySQL UNION 必须遵循以下基本规则：

1.在您要使用的所有表中，列的数量和顺序应该相同。（未对齐的列可以用空列占位）
2.数据类型必须与每个选择查询的相应位置兼容。
3.在不同的 SELECT 查询中选择的列名必须具有相同的顺序。（列名不同最后会被第一个表覆盖）

二、UNION 与 JOIN 的区别

Union 和 Join 子句不同，因为 union 总是垂直组合结果集，而 join水平附加输出。我们可以通过以下图例来理解它：

三、UNION 与 UNION ALL 的区别

union的列会去重，union all则不会去重保留所有数据：

mysql> SELECT * FROM Websites;
+----+--------------+---------------------------+-------+---------+
| id | name         | url                       | alexa | country |
+----+--------------+---------------------------+-------+---------+
| 1  | Google      	| https://www.google.cm/    | 1     | USA     |
| 2  | 淘宝        	| https://www.taobao.com/   | 13    | CN      |
| 3  | 奔月教程     	| http://www.runoon.com/    | 4689  | CN      |
| 4  | 微博        	| http://weibo.com/         | 20    | CN      |
| 5  | Facebook     | https://www.facebook.com/ | 3     | USA     |
| 7  | stackoverflow| http://stackoverflow.com/ | 0     | IND     |
+----+--------------+---------------------------+-------+---------+

mysql> SELECT * FROM apps;
+----+------------+-------------------------+---------+
| id | app_name   | url                     | country |
+----+------------+-------------------------+---------+
|  1 | QQ APP     | http://im.qq.com/       | CN      |
|  2 | 微博 APP 	  | http://weibo.com/       | CN      |
|  3 | 淘宝 APP	  | https://www.taobao.com/ | CN      |
+----+------------+-------------------------+---------+

UNION：

# SELECT country FROM Websites
# UNION
# SELECT country FROM apps ORDER BY country;

mysql> SELECT country FROM websites UNION SELECT country FROM apps ORDER BY country;
+---------+
| country |
+---------+
| CN      |
| IND     |
| USA     |
+---------+
3 rows in set (0.00 sec)

UNION ALL：

# SELECT country FROM Websites
# UNION ALL
# SELECT country FROM apps ORDER BY country;

mysql> SELECT country FROM websites UNION ALL SELECT country FROM apps ORDER BY country;
+---------+
| country |
+---------+
| CN      |
| CN      |
| CN      |
| CN      |
| CN      |
| CN      |
| IND     |
| USA     |
| USA     |
+---------+
9 rows in set (0.00 sec)

Example1：

# SELECT country, name FROM websites 
# WHERE country='CN' 
# UNION ALL 
# SELECT country, app_name FROM apps 
# WHERE country='CN' 
# ORDER BY country;

mysql> SELECT country, name FROM websites
    -> WHERE country='CN'
    -> UNION ALL
    -> SELECT country, app_name FROM apps
    -> WHERE country='CN'
    -> ORDER BY country;
+---------+--------------+
| country | name         |
+---------+--------------+
| CN      | 微博         |
| CN      | 淘宝 APP     |
| CN      | 淘宝         |
| CN      | QQ APP       |
| CN      | 奔月教程     |
| CN      | 微博 APP     |
+---------+--------------+
6 rows in set (0.00 sec)

Example2:

SELECT
	o.platform,
	o.userAccount,
	o.warehouseSku,
	o.salesQty salesOrderQty,
	NULL AS salesRetundQty, # 空数据列占位
	ROUND( o.salesAmount, 4 ) salesOrderAmount,
	NULL AS salesRefundAmount, # 空数据列占位
	o.selling_fees,
	o.fba_fees,
	ROUND( o.unitTail, 4 ) orderUnitTail,
	NULL AS retundUnitTail  # 空数据列占位
FROM

参考文献：https://mp.weixin.qq.com/s?__biz=MzU5NzUxMzEzNQ==&mid=2247489778&idx=5&sn=47d9834c93e7c4cf8406392a0041b52b&chksm=fe531e32c92497241fd6ca5a289203e76c04bb9bd86917647eb63ab51d6a68c8b511a1003a14&scene=27