您好,登录后才能下订单哦!
密码登录
登录注册
点击 登录注册 即表示同意《亿速云用户服务条款》
# SQL中UNION关键字怎么用
## 1. UNION概述
### 1.1 什么是UNION
UNION是SQL中用于合并两个或多个SELECT语句结果集的操作符。它能够将多个查询结果垂直堆叠成一个单一的结果集,同时自动去除重复行(除非使用UNION ALL)。
### 1.2 UNION的主要特点
- **结果集合并**:将多个SELECT语句的结果合并为一个结果集
- **列数相同**:所有SELECT语句必须有相同数量的列
- **数据类型兼容**:对应列的数据类型必须兼容
- **默认去重**:UNION会自动去除重复行(UNION ALL保留重复行)
- **排序应用**:ORDER BY子句只能出现在最后一个SELECT语句后
## 2. 基本语法结构
```sql
SELECT column1, column2, ... FROM table1
UNION [ALL]
SELECT column1, column2, ... FROM table2
[UNION [ALL]
SELECT column1, column2, ... FROM table3]
...
[ORDER BY column1, column2, ...];
UNION
:合并结果集并去除重复行UNION ALL
:合并结果集但保留重复行ORDER BY
:对整个合并后的结果集进行排序(只能出现在最后)假设我们有两个表:employees_east
和employees_west
,结构相同,都包含员工信息。
-- 合并东西部员工表,去除重复
SELECT employee_id, name, department
FROM employees_east
UNION
SELECT employee_id, name, department
FROM employees_west;
-- 合并结果并保留所有记录(包括重复的)
SELECT product_id, product_name
FROM current_products
UNION ALL
SELECT product_id, product_name
FROM discontinued_products;
-- 合并三个地区的销售数据
SELECT sale_id, amount, sale_date
FROM north_sales
UNION
SELECT sale_id, amount, sale_date
FROM east_sales
UNION
SELECT sale_id, amount, sale_date
FROM south_sales;
所有SELECT语句必须有相同数量的列,且对应列的数据类型必须兼容:
-- 错误的UNION使用(列数不同)
SELECT id, name, salary FROM employees
UNION
SELECT id, name FROM contractors; -- 报错
-- 正确的UNION使用
SELECT id, name, NULL AS salary FROM contractors
UNION
SELECT id, name, salary FROM employees;
UNION结果集的列名取自第一个SELECT语句的列名或别名:
-- 结果集列名为employee_id和full_name
SELECT emp_id AS employee_id, emp_name AS full_name FROM staff
UNION
SELECT person_id, CONCAT(first_name, ' ', last_name) FROM contractors;
ORDER BY只能出现在最后一个SELECT语句后,作用于整个结果集:
-- 正确排序方式
SELECT product_id, product_name FROM active_products
UNION
SELECT product_id, product_name FROM inactive_products
ORDER BY product_name;
-- 性能对比示例
-- 较慢(需要去重)
SELECT customer_id FROM online_orders
UNION
SELECT customer_id FROM in_store_orders;
-- 较快(已知没有重复或需要保留重复)
SELECT customer_id FROM online_orders
UNION ALL
SELECT customer_id FROM in_store_orders;
-- 使用UNION模拟数据透视表
SELECT 'Q1' AS quarter, SUM(q1_sales) AS total_sales FROM sales_data
UNION
SELECT 'Q2', SUM(q2_sales) FROM sales_data
UNION
SELECT 'Q3', SUM(q3_sales) FROM sales_data
UNION
SELECT 'Q4', SUM(q4_sales) FROM sales_data;
-- 分类统计
SELECT 'High Salary' AS category, COUNT(*) AS count
FROM employees WHERE salary > 100000
UNION
SELECT 'Medium Salary', COUNT(*)
FROM employees WHERE salary BETWEEN 50000 AND 100000
UNION
SELECT 'Low Salary', COUNT(*)
FROM employees WHERE salary < 50000;
-- 合并不同粒度的统计数据
SELECT 'Department' AS level, department, AVG(salary) AS avg_salary
FROM employees GROUP BY department
UNION
SELECT 'Company', NULL, AVG(salary) FROM employees;
特性 | UNION | JOIN |
---|---|---|
操作方向 | 垂直合并(行) | 水平合并(列) |
结果结构 | 相同列结构 | 扩展列结构 |
重复处理 | 默认去重 | 保留所有数据 |
特性 | UNION | UNION ALL |
---|---|---|
重复处理 | 去除重复行 | 保留所有行 |
性能 | 较慢(需排序) | 较快 |
结果集大小 | ≤输入集总和 | =输入集总和 |
-- 合并客户数据(来自不同系统)
SELECT customer_id, name, email, 'SystemA' AS source
FROM system_a_customers
UNION
SELECT client_id, full_name, email_address, 'SystemB'
FROM system_b_clients;
-- 生成月度综合报表
SELECT 'January' AS month, SUM(amount) AS total
FROM sales WHERE sale_date BETWEEN '2023-01-01' AND '2023-01-31'
UNION
SELECT 'February', SUM(amount)
FROM sales WHERE sale_date BETWEEN '2023-02-01' AND '2023-02-28'
-- ...其他月份...
ORDER BY total DESC;
-- 标准化不同格式的联系人数据
SELECT id,
LOWER(TRIM(email)) AS standardized_email,
REGEXP_REPLACE(phone, '[^0-9]', '') AS cleaned_phone
FROM contacts_primary
UNION ALL
SELECT contact_id,
LOWER(email_address),
REPLACE(REPLACE(phone_number, '-', ''), ' ', '')
FROM secondary_contacts;
(SELECT * FROM table1 LIMIT 10)
UNION
(SELECT * FROM table2 LIMIT 10)
LIMIT 15;
SELECT TOP 5 * FROM products1
UNION
SELECT TOP 5 * FROM products2;
SELECT * FROM (
SELECT * FROM table1 WHERE ROWNUM <= 100
UNION
SELECT * FROM table2 WHERE ROWNUM <= 100
) WHERE ROWNUM <= 150;
错误示例:
SELECT id, name, salary FROM employees
UNION
SELECT id, name FROM contractors; -- 列数不同
解决方案:
SELECT id, name, salary FROM employees
UNION
SELECT id, name, NULL AS salary FROM contractors;
错误示例:
SELECT id, name, CAST(salary AS VARCHAR) FROM employees
UNION
SELECT id, name, hire_date FROM employees; -- 第三列类型不匹配
解决方案:
SELECT id, name, CAST(salary AS VARCHAR) FROM employees
UNION
SELECT id, name, TO_CHAR(hire_date, 'YYYY-MM-DD') FROM employees;
错误示例:
SELECT * FROM table1 ORDER BY col1
UNION
SELECT * FROM table2; -- ORDER BY不能出现在第一个SELECT后
正确写法:
SELECT * FROM table1
UNION
SELECT * FROM table2
ORDER BY col1;
-- 优化示例:使用WHERE减少UNION数据量
SELECT id, name FROM large_table1 WHERE status = 'active'
UNION
SELECT id, name FROM large_table2 WHERE is_valid = 1;
UNION是SQL中强大的数据合并工具,掌握其使用方法可以:
关键要点: - 理解UNION和UNION ALL的区别 - 确保列数和数据类型兼容 - 注意性能影响,特别是大数据集 - 合理使用排序和限制条件
通过本文的详细讲解和丰富示例,您应该已经全面掌握了SQL中UNION关键字的使用方法和最佳实践。 “`
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。