跳到主要内容

PostgreSQL 集合操作

在PostgreSQL中，集合操作允许我们将多个查询的结果组合在一起，或者比较它们之间的差异。这些操作包括 UNION、INTERSECT 和 EXCEPT。它们可以帮助我们从多个表中提取数据，或者对查询结果进行逻辑上的组合和筛选。

什么是集合操作？​

集合操作是基于集合论的SQL操作，用于处理多个查询结果集之间的关系。它们的主要目的是将两个或多个查询的结果组合成一个结果集，或者找出它们之间的共同部分或差异部分。

PostgreSQL支持以下集合操作：

  • UNION：将两个查询的结果集合并，并去除重复的行。
  • UNION ALL：将两个查询的结果集合并，但保留重复的行。
  • INTERSECT：返回两个查询结果集的交集，即同时存在于两个结果集中的行。
  • EXCEPT：返回第一个查询结果集中存在但第二个查询结果集中不存在的行。

UNION 操作​

UNION 操作用于将两个查询的结果集合并，并自动去除重复的行。如果希望保留重复的行，可以使用 UNION ALL。

语法​

SELECT column1, column2, ...
FROM table1
UNION
SELECT column1, column2, ...
FROM table2;

示例​

假设我们有两个表 employees 和 contractors，它们都包含员工的姓名和职位信息。

-- 查询所有员工和合同工的姓名
SELECT name FROM employees
UNION
SELECT name FROM contractors;

输出：

 name
---------
 Alice
 Bob
 Charlie
 David
 Eve
备注

UNION 会自动去除重复的行。如果 employees 和 contractors 中有相同的名字，结果集中只会出现一次。

UNION ALL 操作​

UNION ALL 与 UNION 类似，但它不会去除重复的行。

语法​

SELECT column1, column2, ...
FROM table1
UNION ALL
SELECT column1, column2, ...
FROM table2;

示例​

-- 查询所有员工和合同工的姓名，保留重复项
SELECT name FROM employees
UNION ALL
SELECT name FROM contractors;

输出：

 name
---------
 Alice
 Bob
 Charlie
 David
 Eve
 Alice
提示

如果你确定两个查询的结果集不会有重复项，或者你希望保留重复项，可以使用 UNION ALL，因为它比 UNION 更高效。

INTERSECT 操作​

INTERSECT 操作用于返回两个查询结果集的交集，即同时存在于两个结果集中的行。

语法​

SELECT column1, column2, ...
FROM table1
INTERSECT
SELECT column1, column2, ...
FROM table2;

示例​

-- 查询既是员工又是合同工的姓名
SELECT name FROM employees
INTERSECT
SELECT name FROM contractors;

输出：

 name
---------
 Alice
警告

INTERSECT 只会返回在两个查询结果集中都存在的行。如果两个结果集没有共同的行，结果将为空。

EXCEPT 操作​

EXCEPT 操作用于返回第一个查询结果集中存在但第二个查询结果集中不存在的行。

语法​

SELECT column1, column2, ...
FROM table1
EXCEPT
SELECT column1, column2, ...
FROM table2;

示例​

-- 查询是员工但不是合同工的姓名
SELECT name FROM employees
EXCEPT
SELECT name FROM contractors;

输出：

 name
---------
 Bob
 Charlie
注意

EXCEPT 操作的结果集只包含第一个查询结果集中独有的行。如果第一个查询结果集中的所有行都存在于第二个查询结果集中，结果将为空。

实际应用场景​

场景1：合并多个表的数据​

假设你有一个系统，其中员工和合同工分别存储在不同的表中。你需要生成一个包含所有人员的报告。这时可以使用 UNION 或 UNION ALL 来合并两个表的数据。

SELECT name, 'Employee' AS type FROM employees
UNION ALL
SELECT name, 'Contractor' AS type FROM contractors;

输出：

 name    |    type
---------+------------
 Alice   | Employee
 Bob     | Employee
 Charlie | Employee
 David   | Contractor
 Eve     | Contractor
 Alice   | Contractor

场景2：查找共同客户​

假设你有两个表 customers_2022 和 customers_2023，分别存储了2022年和2023年的客户信息。你需要找出在这两年中都存在的客户。

SELECT customer_id FROM customers_2022
INTERSECT
SELECT customer_id FROM customers_2023;

输出：

 customer_id
-------------
 101
 102

场景3：查找新客户​

假设你想找出2023年新增的客户，即那些在2023年存在但在2022年不存在的客户。

SELECT customer_id FROM customers_2023
EXCEPT
SELECT customer_id FROM customers_2022;

输出：

 customer_id
-------------
 103
 104

总结​

PostgreSQL的集合操作（UNION、INTERSECT、EXCEPT）是处理多个查询结果集的强大工具。它们可以帮助我们合并、比较和筛选数据，适用于各种实际场景，如数据合并、查找共同项或差异项等。

提示

在使用集合操作时，确保两个查询的列数和数据类型是兼容的，否则会导致错误。

附加资源与练习​

  • 练习1：创建一个包含两个表的数据库，分别存储学生和教师的姓名。使用 UNION 操作生成一个包含所有人员的列表。
  • 练习2：使用 INTERSECT 操作找出既是学生又是教师的姓名。
  • 练习3：使用 EXCEPT 操作找出只是学生但不是教师的姓名。

通过这些练习，你将更好地掌握PostgreSQL中的集合操作。继续探索和实践，你会发现它们在数据处理中的强大之处！