SQL语法之union和union all 的区别？

示例场景：

假设我们有两个表，记录了不同来源的员工信息：

• employees_ny (纽约员工):

  id	name	department
  1	Alice	Sales
  2	Bob	IT
  3	Carol	HR
  4	David	Sales	(注意：David在NY表)
  5	Bob	IT	(注意：Bob在NY表重复出现，可能是录入错误)

• employees_sf (旧金山员工):

  id	name	department
  101	Emma	Marketing
  102	Frank	IT
  103	Alice	Sales	(注意：Alice在SF表)
  104	David	Engineering	(注意：David在SF表，部门不同)
  105	Grace	HR

目标： 获取一个包含所有地点员工的合并列表。

---

示例 1: 使用 UNION ALL (保留所有行，包括重复)

SELECT id, name, department, 'NY' AS location FROM employees_ny
UNION ALL
SELECT id, name, department, 'SF' AS location FROM employees_sf;

结果 (UNION ALL):

id	name	department	location
1	Alice	Sales	NY
2	Bob	IT	NY
3	Carol	HR	NY
4	David	Sales	NY	(David NY)
5	Bob	IT	NY	(Bob 重复行 - 保留)
101	Emma	Marketing	SF
102	Frank	IT	SF
103	Alice	Sales	SF	(Alice 重复行 - 保留，地点不同)
104	David	Engineering	SF	(David SF - 保留，部门不同)
105	Grace	HR	SF

解释:

• 结果集是 employees_ny 和 employees_sf 所有行的直接堆叠。

• 所有重复行都被保留：

  • Bob (id=2 和 id=5) 都在 NY，姓名和部门完全重复。

  • Alice (NY id=1 和 SF id=103) 姓名和部门重复，但来源地点不同（location 列区分）。

  • David (NY id=4 和 SF id=104) 姓名重复，但部门不同，地点也不同。

• 效率较高： 数据库引擎只需要简单地将两个查询结果按顺序拼接起来，不需要进行额外的排序和去重操作。

• 符合规则：两个 SELECT 都有 4 列 (id, name, department, location)，对应列数据类型兼容。

---

示例 2: 使用 UNION (自动去重)

SELECT id, name, department, 'NY' AS location FROM employees_ny
UNION -- 这里省略了 ALL，等同于 UNION DISTINCT
SELECT id, name, department, 'SF' AS location FROM employees_sf;

结果 (UNION):

id	name	department	location
1	Alice	Sales	NY
2	Bob	IT	NY	(只保留一个Bob)
3	Carol	HR	NY
4	David	Sales	NY	(David NY)
5	Bob	IT	NY	(这个Bob被去重掉了！)
101	Emma	Marketing	SF
102	Frank	IT	SF
103	Alice	Sales	SF	(Alice SF 保留，因为location不同)
104	David	Engineering	SF	(David SF 保留，因为department不同)
105	Grace	HR	SF

解释:

• 数据库引擎首先将两个表的所有行堆叠在一起（得到一个包含 10 行的临时结果集）。

• 然后对这个整个临时结果集执行去重 (DISTINCT) 操作。

• 去重规则： 只有当两行的 SELECT 列表中所有对应列的值都完全相同时，才会被认为是重复行并被移除一行。

  • Bob (NY id=2 和 id=5): id (2 vs 5) 不同，name (Bob vs Bob) 相同，department (IT vs IT) 相同，location (NY vs NY) 相同。关键点：id 列不同！ 虽然名字、部门、地点一样，但 id 不同，所以不被视为重复行。(注意：这个例子中 id 不同意味着它们是不同记录，UNION 不会仅根据 name 去重)

  • Alice (NY id=1 和 SF id=103): id (1 vs 103) 不同，name (Alice vs Alice) 相同，department (Sales vs Sales) 相同，location (NY vs SF) 不同。因为 location 值不同，所以这两行不是重复行，都被保留。

  • David (NY id=4 和 SF id=104): id (4 vs 104) 不同，name (David vs David) 相同，department (Sales vs Engineering) 不同，location (NY vs SF) 不同。因为 department 和 location 都不同，所以这两行不是重复行，都被保留。

  • (如果存在两行所有列值都完全一样，UNION 会只保留其中一行)

• 效率较低： 为了进行去重，数据库引擎通常需要对整个合并后的临时结果集进行排序（SORT）操作，以便将相同的行分组在一起，然后移除重复项。这个排序操作在数据量大时是非常消耗资源的。因此 UNION 通常比 UNION ALL 慢得多。

• 符合规则：列数和数据类型要求同 UNION ALL。

---

注意：

1. all是可以省略，但是如果省略掉之后将结果拼接起来执行去重操作，如果有all则不会去重。没有设置all的时候执行合并，先对合并的数据进行排序操作，然后再执行去重操作，所以union的效率要远远低于union all。

2. 合并的结果集必须要具有'相同的列数'。

3. 合并的结果集中对应的列的'数据类型必须要相同'。

4. 在实际工作过程中要用union all代替 union(一般实际工作中的数据都是优化过的数据，很少有重复的，但是也需要检查和以防万一)。去重请用其他的，distinct，group by 都可以。

补充注意点：

• 列名： 最终结果集的列名通常取自第一个 SELECT 语句。即使第二个 SELECT 的列名不同（只要数据类型兼容），结果集也会使用第一个查询的列名。

• 排序： 如果需要最终结果有序，必须在整个 UNION/UNION ALL 语句的最后加一个 ORDER BY 子句。在单个 SELECT 语句内加 ORDER BY 通常无效或会被覆盖（除非配合 LIMIT 等）。