2025-06-25 19:21:02 +08:00

11 KiB

Raw Permalink Blame History

MySQL UNION 语法使用文档

UNION 基础语法

UNION 用于合并两个或多个 SELECT 语句的结果集。

SELECT column1, column2, ... FROM table1
UNION [ALL]
SELECT column1, column2, ... FROM table2
[UNION [ALL]
SELECT column1, column2, ... FROM table3]
...
[ORDER BY column_name]

UNION 规则和限制

列数相同：每个 SELECT 语句必须拥有相同数量的列
数据类型兼容：对应位置的列必须具有兼容的数据类型
列名：结果集使用第一个 SELECT 语句的列名
去重：UNION 默认去除重复记录，UNION ALL 保留所有记录
ORDER BY：只能在最后使用，对整个结果集排序

示例数据准备

创建示例表

-- 创建员工表
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    department VARCHAR(30),
    salary DECIMAL(10,2),
    city VARCHAR(30)
);

-- 创建前员工表
CREATE TABLE former_employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    department VARCHAR(30),
    last_salary DECIMAL(10,2),
    city VARCHAR(30),
    leave_date DATE
);

-- 创建管理层表
CREATE TABLE managers (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    level VARCHAR(20),
    salary DECIMAL(10,2),
    region VARCHAR(30)
);

插入示例数据

-- 插入员工数据
INSERT INTO employees (id, name, department, salary, city) VALUES
(1, '张三', '技术部', 8000.00, '北京'),
(2, '李四', '销售部', 6000.00, '上海'),
(3, '王五', '技术部', 9000.00, '深圳'),
(4, '赵六', '人事部', 5500.00, '北京'),
(5, '钱七', '财务部', 7000.00, '广州'),
(6, '孙八', '技术部', 8500.00, '杭州'),
(7, '周九', '市场部', 6500.00, '成都');

-- 插入前员工数据
INSERT INTO former_employees (id, name, department, last_salary, city, leave_date) VALUES
(8, '吴十', '技术部', 7500.00, '北京', '2023-08-15'),
(9, '郑一', '销售部', 5800.00, '上海', '2023-09-20'),
(10, '王五', '技术部', 8000.00, '深圳', '2023-07-10'),
(11, '刘二', '人事部', 5200.00, '广州', '2023-10-05');

-- 插入管理层数据
INSERT INTO managers (id, name, level, salary, region) VALUES
(12, '陈总', '高级经理', 15000.00, '华北'),
(13, '林总', '部门经理', 12000.00, '华东'),
(14, '张三', '项目经理', 10000.00, '华南'),
(15, '黄总', '区域经理', 11000.00, '华西');

UNION 基础使用示例

1. 简单合并查询

合并当前员工和前员工的姓名：

SELECT name FROM employees
UNION
SELECT name FROM former_employees;

结果：

+------+
| name |
+------+
| 张三 |
| 李四 |
| 王五 |
| 赵六 |
| 钱七 |
| 孙八 |
| 周九 |
| 吴十 |
| 郑一 |
| 刘二 |
+------+

2. 多列合并查询

合并员工和管理层的姓名和薪资：

SELECT name, salary FROM employees
UNION
SELECT name, salary FROM managers
ORDER BY salary DESC;

结果：

+------+----------+
| name | salary   |
+------+----------+
| 陈总 | 15000.00 |
| 林总 | 12000.00 |
| 黄总 | 11000.00 |
| 张三 | 10000.00 |
| 王五 |  9000.00 |
| 孙八 |  8500.00 |
| 张三 |  8000.00 |
| 钱七 |  7000.00 |
| 周九 |  6500.00 |
| 李四 |  6000.00 |
| 赵六 |  5500.00 |
+------+----------+

3. 使用别名统一列名

SELECT name, department AS work_area, salary FROM employees
UNION
SELECT name, region AS work_area, salary FROM managers
ORDER BY work_area;

结果：

+------+-----------+----------+
| name | work_area | salary   |
+------+-----------+----------+
| 钱七 | 财务部    |  7000.00 |
| 陈总 | 华北      | 15000.00 |
| 林总 | 华东      | 12000.00 |
| 张三 | 华南      | 10000.00 |
| 黄总 | 华西      | 11000.00 |
| 赵六 | 人事部    |  5500.00 |
| 李四 | 销售部    |  6000.00 |
| 周九 | 市场部    |  6500.00 |
| 张三 | 技术部    |  8000.00 |
| 王五 | 技术部    |  9000.00 |
| 孙八 | 技术部    |  8500.00 |
+------+-----------+----------+

UNION vs UNION ALL

UNION（去重）

SELECT city FROM employees
UNION
SELECT city FROM former_employees;

结果：

+------+
| city |
+------+
| 北京 |
| 上海 |
| 深圳 |
| 广州 |
| 杭州 |
| 成都 |
+------+

UNION ALL（保留重复）

SELECT city FROM employees
UNION ALL
SELECT city FROM former_employees;

结果：

+------+
| city |
+------+
| 北京 |
| 上海 |
| 深圳 |
| 北京 |
| 广州 |
| 杭州 |
| 成都 |
| 北京 |
| 上海 |
| 深圳 |
| 广州 |
+------+

性能对比示例

-- 统计重复记录数量
SELECT 
    'UNION' AS query_type,
    COUNT(*) AS record_count
FROM (
    SELECT city FROM employees
    UNION
    SELECT city FROM former_employees
) AS union_result

UNION ALL

SELECT 
    'UNION ALL' AS query_type,
    COUNT(*) AS record_count
FROM (
    SELECT city FROM employees
    UNION ALL
    SELECT city FROM former_employees
) AS union_all_result;

结果：

+------------+--------------+
| query_type | record_count |
+------------+--------------+
| UNION      |            6 |
| UNION ALL  |           11 |
+------------+--------------+

复杂查询示例

1. 条件过滤与UNION

查询高薪员工和所有管理层：

SELECT name, salary, '高薪员工' AS category
FROM employees 
WHERE salary > 8000

UNION

SELECT name, salary, '管理层' AS category
FROM managers

ORDER BY salary DESC;

结果：

+------+----------+----------+
| name | salary   | category |
+------+----------+----------+
| 陈总 | 15000.00 | 管理层   |
| 林总 | 12000.00 | 管理层   |
| 黄总 | 11000.00 | 管理层   |
| 张三 | 10000.00 | 管理层   |
| 王五 |  9000.00 | 高薪员工 |
| 孙八 |  8500.00 | 高薪员工 |
+------+----------+----------+

2. 聚合查询与UNION

各部门薪资统计：

SELECT department AS name, AVG(salary) AS avg_salary, '部门平均' AS type
FROM employees 
GROUP BY department

UNION ALL

SELECT '公司总体' AS name, AVG(salary) AS avg_salary, '总体平均' AS type
FROM employees

UNION ALL

SELECT '管理层' AS name, AVG(salary) AS avg_salary, '管理平均' AS type
FROM managers

ORDER BY avg_salary DESC;

结果：

+----------+-------------+----------+
| name     | avg_salary  | type     |
+----------+-------------+----------+
| 管理层   | 12000.00000 | 管理平均 |
| 技术部   |  8500.00000 | 部门平均 |
| 公司总体 |  7142.85714 | 总体平均 |
| 财务部   |  7000.00000 | 部门平均 |
| 市场部   |  6500.00000 | 部门平均 |
| 销售部   |  6000.00000 | 部门平均 |
| 人事部   |  5500.00000 | 部门平均 |
+----------+-------------+----------+

3. 多表复杂联合查询

创建完整的人员名册：

SELECT 
    id,
    name,
    department,
    salary,
    city,
    '在职' AS status
FROM employees

UNION ALL

SELECT 
    id,
    name,
    department,
    last_salary AS salary,
    city,
    '离职' AS status
FROM former_employees

UNION ALL

SELECT 
    id,
    name,
    '管理层' AS department,
    salary,
    region AS city,
    '管理' AS status
FROM managers

ORDER BY status, salary DESC;

4. 使用子查询的UNION

查询每个城市的最高薪资员工：

SELECT name, city, salary, '当前最高薪' AS note
FROM employees e1
WHERE salary = (
    SELECT MAX(salary) 
    FROM employees e2 
    WHERE e2.city = e1.city
)

UNION

SELECT name, city, last_salary AS salary, '前员工最高薪' AS note
FROM former_employees f1
WHERE last_salary = (
    SELECT MAX(last_salary) 
    FROM former_employees f2 
    WHERE f2.city = f1.city
)

ORDER BY salary DESC;

最佳实践和注意事项

1. 性能优化建议

-- ❌ 避免：不必要的UNION，可以用OR替代
SELECT * FROM employees WHERE department = '技术部'
UNION
SELECT * FROM employees WHERE department = '销售部';

-- ✅ 推荐：使用OR条件
SELECT * FROM employees 
WHERE department IN ('技术部', '销售部');

2. 数据类型兼容性

-- ✅ 正确：确保数据类型兼容
SELECT id, name, salary FROM employees
UNION
SELECT id, name, CAST(last_salary AS DECIMAL(10,2)) FROM former_employees;

-- ❌ 错误：数据类型不兼容可能导致错误
SELECT id, name, salary FROM employees
UNION
SELECT id, name, leave_date FROM former_employees; -- leave_date是DATE类型

3. 使用索引优化

-- 为UNION查询中的过滤条件创建索引
CREATE INDEX idx_employees_dept ON employees(department);
CREATE INDEX idx_employees_salary ON employees(salary);
CREATE INDEX idx_former_employees_dept ON former_employees(department);

-- 优化后的查询
SELECT name, department FROM employees WHERE department = '技术部'
UNION
SELECT name, department FROM former_employees WHERE department = '技术部';

4. 内存使用注意事项

-- 对于大型结果集，考虑使用LIMIT
SELECT name, salary FROM employees
UNION ALL
SELECT name, salary FROM managers
ORDER BY salary DESC
LIMIT 10;

5. 常见错误和解决方案

-- ❌ 错误：列数不匹配
SELECT name, department FROM employees
UNION
SELECT name FROM managers; -- 缺少一列

-- ✅ 修正：补齐列数
SELECT name, department FROM employees
UNION
SELECT name, level AS department FROM managers;

-- ❌ 错误：ORDER BY位置错误
SELECT name FROM employees ORDER BY name
UNION
SELECT name FROM managers ORDER BY name;

-- ✅ 修正：ORDER BY只能在最后
SELECT name FROM employees
UNION
SELECT name FROM managers
ORDER BY name;

6. 实用查询模式

数据完整性检查

-- 检查是否有重复的员工记录
SELECT name, COUNT(*) as count
FROM (
    SELECT name FROM employees
    UNION ALL
    SELECT name FROM former_employees
    UNION ALL
    SELECT name FROM managers
) AS all_people
GROUP BY name
HAVING COUNT(*) > 1;

数据对比分析

-- 对比不同表中的数据分布
SELECT 
    '当前员工' AS source,
    department,
    COUNT(*) AS count,
    AVG(salary) AS avg_salary
FROM employees
GROUP BY department

UNION ALL

SELECT 
    '前员工' AS source,
    department,
    COUNT(*) AS count,
    AVG(last_salary) AS avg_salary
FROM former_employees
GROUP BY department

ORDER BY source, department;

总结：

UNION 是合并查询结果的强大工具
注意列数、数据类型的匹配
根据需求选择 UNION 或 UNION ALL
合理使用索引和LIMIT提升性能
避免不必要的复杂UNION操作

11 KiB Raw Permalink Blame History Unescape Escape

MySQL UNION 语法使用文档

目录

UNION 基础语法

UNION 规则和限制

示例数据准备

创建示例表

插入示例数据

UNION 基础使用示例

1. 简单合并查询

2. 多列合并查询

3. 使用别名统一列名

UNION vs UNION ALL

UNION（去重）

UNION ALL（保留重复）

性能对比示例

复杂查询示例

1. 条件过滤与UNION

2. 聚合查询与UNION

3. 多表复杂联合查询

4. 使用子查询的UNION

最佳实践和注意事项

1. 性能优化建议

2. 数据类型兼容性

3. 使用索引优化

4. 内存使用注意事项

5. 常见错误和解决方案

6. 实用查询模式

数据完整性检查

数据对比分析

11 KiB

Raw Permalink Blame History