组合查询

在 PostgreSQL 中，组合查询（Set Operations）是一种强大的功能，允许我们将两个或多个查询的结果进行集合运算。本章将详细介绍三种主要的集合操作：UNION（并集）、INTERSECT（交集）和 EXCEPT（差集）。

基本概念

组合查询使用集合操作来组合两个查询的结果，语法格式如下：

sql

query1 UNION [ALL] query2
query1 INTERSECT [ALL] query2
query1 EXCEPT [ALL] query2

其中 query1 和 query2 是可以使用任何 SQL 特性的完整查询语句。

重要概念集合操作的核心是对查询结果进行数学集合运算，类似于数学中的并集、交集和差集概念。 :::

1. UNION（并集）操作

1.1 基本语法和概念

UNION 操作将两个查询的结果合并，返回所有出现在任一查询中的行。

1.2 UNION vs UNION ALL

UNION: 合并结果并自动去除重复行（类似于 DISTINCT）
UNION ALL: 合并结果但保留所有重复行

1.3 实际示例

示例 1：基本 UNION 操作

问题陈述: 获取所有客户和供应商的联系信息。

解决方案:

sql

-- 创建示例表
CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100),
    city VARCHAR(50)
);

CREATE TABLE suppliers (
    id SERIAL PRIMARY KEY,
    company_name VARCHAR(100),
    contact_email VARCHAR(100),
    location VARCHAR(50)
);

-- 插入测试数据
INSERT INTO customers (name, email, city) VALUES
('张三', '[email protected]', '北京'),
('李四', '[email protected]', '上海'),
('王五', '[email protected]', '广州');

INSERT INTO suppliers (company_name, contact_email, location) VALUES
('ABC公司', '[email protected]', '深圳'),
('XYZ科技', '[email protected]', '北京'),
('123贸易', '[email protected]', '上海');

-- UNION查询：获取所有联系人信息
SELECT name AS contact_name, email, city AS location, 'Customer' AS type
FROM customers
UNION
SELECT company_name, contact_email, location, 'Supplier' AS type
FROM suppliers
ORDER BY type, contact_name;

输入数据:

customers 表: 3 行客户数据
suppliers 表: 3 行供应商数据

输出结果:

contact_name | email                | location | type
-------------|---------------------|----------|----------
张三         | [email protected]  | 北京     | Customer
李四         | [email protected]      | 上海     | Customer
王五         | [email protected]    | 广州     | Customer
123贸易      | [email protected]       | 上海     | Supplier
ABC公司      | [email protected]     | 深圳     | Supplier
XYZ科技      | [email protected]        | 北京     | Supplier

分析过程:

第一个查询选择所有客户信息，添加'Customer'标识
第二个查询选择所有供应商信息，添加'Supplier'标识
UNION 操作合并两个结果集，自动去除重复行
ORDER BY 对最终结果进行排序

示例 2：UNION ALL 与重复数据处理

问题陈述: 比较 UNION 和 UNION ALL 在处理重复数据时的差异。

解决方案:

sql

-- 创建重复数据的示例
WITH dataset1 AS (
    SELECT 'Product A' AS product, 100 AS price
    UNION ALL
    SELECT 'Product B', 200
    UNION ALL
    SELECT 'Product C', 150
),
dataset2 AS (
    SELECT 'Product A' AS product, 100 AS price  -- 重复数据
    UNION ALL
    SELECT 'Product B', 250  -- 相同产品，不同价格
    UNION ALL
    SELECT 'Product D', 300  -- 新产品
)

-- 使用UNION（去重）
SELECT product, price, 'UNION Result' AS source
FROM (
    SELECT * FROM dataset1
    UNION
    SELECT * FROM dataset2
) combined

UNION ALL

-- 使用UNION ALL（保留重复）
SELECT product, price, 'UNION ALL Result' AS source
FROM (
    SELECT * FROM dataset1
    UNION ALL
    SELECT * FROM dataset2
) combined
ORDER BY source, product, price;

输出结果分析:

product    | price | source
-----------|-------|---------------
Product A  | 100   | UNION Result
Product B  | 200   | UNION Result
Product B  | 250   | UNION Result
Product C  | 150   | UNION Result
Product D  | 300   | UNION Result
Product A  | 100   | UNION ALL Result
Product A  | 100   | UNION ALL Result  -- 重复保留
Product B  | 200   | UNION ALL Result
Product B  | 250   | UNION ALL Result
Product C  | 150   | UNION ALL Result
Product D  | 300   | UNION ALL Result

性能提示 UNION ALL 通常比 UNION 性能更好，因为它不需要执行去重操作。如果确定没有重复数据或需要保留重复数据，优先使用 UNION ALL。 :::

2. INTERSECT（交集）操作

2.1 基本概念

INTERSECT 返回同时存在于两个查询结果中的行。

2.1 基本概念

INTERSECT 返回同时存在于两个查询结果中的行。

交集操作特点

数学概念: INTERSECT 操作相当于数学中的集合交集（A ∩ B）
结果特性: 只返回同时满足两个查询条件的记录
重复处理: 自动去除重复行，类似于 UNION 的去重行为
常见用途: 查找共同客户、重叠数据、满足多个条件的记录 :::

2.2 实际示例

示例 3：查找共同客户

问题陈述: 找出既购买了产品 A 又购买了产品 B 的客户。

解决方案:

sql

-- 创建订单表
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INTEGER,
    product_name VARCHAR(100),
    order_date DATE
);

-- 插入测试数据
INSERT INTO orders (customer_id, product_name, order_date) VALUES
(1, 'Product A', '2023-01-15'),
(2, 'Product A', '2023-01-16'),
(3, 'Product A', '2023-01-17'),
(1, 'Product B', '2023-01-18'),
(2, 'Product B', '2023-01-19'),
(4, 'Product B', '2023-01-20'),
(1, 'Product C', '2023-01-21'),
(5, 'Product C', '2023-01-22');

-- 使用INTERSECT查找同时购买Product A和Product B的客户
SELECT customer_id
FROM orders
WHERE product_name = 'Product A'
INTERSECT
SELECT customer_id
FROM orders
WHERE product_name = 'Product B';

输入数据: 8 条订单记录，涉及 5 个客户和 3 种产品

输出结果:

customer_id
-----------
1
2

分析过程:

第一个查询找出购买 Product A 的所有客户 ID
第二个查询找出购买 Product B 的所有客户 ID
INTERSECT 操作返回两个结果集的交集
结果显示客户 1 和客户 2 都购买了这两种产品

示例 4：复杂条件的交集查询

问题陈述: 找出在 2023 年 1 月和 2 月都有订单的客户。

解决方案:

sql

-- 扩展订单数据
INSERT INTO orders (customer_id, product_name, order_date) VALUES
(1, 'Product D', '2023-02-15'),
(3, 'Product E', '2023-02-16'),
(6, 'Product F', '2023-02-17');

-- 查找在1月和2月都有订单的客户
SELECT DISTINCT customer_id, 'Active in both months' AS status
FROM orders
WHERE EXTRACT(MONTH FROM order_date) = 1  -- 1月
INTERSECT
SELECT DISTINCT customer_id, 'Active in both months' AS status
FROM orders
WHERE EXTRACT(MONTH FROM order_date) = 2; -- 2月

输出结果:

customer_id | status
------------|----------------------
1           | Active in both months
3           | Active in both months

3. EXCEPT（差集）操作

3.1 基本概念

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

3.2 实际示例

示例 5：查找未购买特定产品的客户

问题陈述: 找出购买了产品但从未购买过 Product A 的客户。

解决方案:

sql

-- 查找所有有订单的客户
SELECT DISTINCT customer_id
FROM orders
EXCEPT
-- 减去购买过Product A的客户
SELECT DISTINCT customer_id
FROM orders
WHERE product_name = 'Product A';

输出结果:

customer_id
-----------
4
5
6

分析过程:

第一个查询获取所有有订单的客户 ID
第二个查询获取购买过 Product A 的客户 ID
EXCEPT 操作返回第一个结果集中不在第二个结果集中的客户
结果显示客户 4、5、6 从未购买过 Product A

示例 6：数据验证和清理

问题陈述: 找出客户表中存在但在订单表中没有订单记录的客户。

解决方案:

sql

-- 查找没有下过订单的客户
SELECT id AS customer_id, name, 'No orders' AS status
FROM customers
EXCEPT
SELECT DISTINCT o.customer_id, c.name, 'No orders' AS status
FROM orders o
JOIN customers c ON o.customer_id = c.id;

应用场景: 数据清理、客户分析、营销活动目标客户筛选。

4. 组合操作规则和约束

4.1 兼容性要求

重要约束为了执行集合操作，两个查询必须是"并集兼容的"：

返回相同数量的列
对应列具有兼容的数据类型 :::

示例 7：兼容性检查

sql

-- ✅ 正确：列数和类型兼容
SELECT name, age FROM employees
UNION
SELECT customer_name, customer_age FROM customers;

-- ❌ 错误：列数不匹配
SELECT name, age, department FROM employees
UNION
SELECT customer_name, customer_age FROM customers;  -- 缺少第三列

-- ❌ 错误：数据类型不兼容
SELECT name, age FROM employees        -- age是INTEGER
UNION
SELECT customer_name, signup_date FROM customers;  -- signup_date是DATE

4.2 操作优先级和组合

优先级规则

INTERSECT 具有最高优先级
UNION 和 EXCEPT 具有相同优先级，从左到右结合

示例 8：复杂组合操作

问题陈述: 演示多个集合操作的组合使用。

解决方案:

sql

-- 创建更多测试数据
CREATE TABLE product_categories (
    product_name VARCHAR(100),
    category VARCHAR(50)
);

INSERT INTO product_categories VALUES
('Product A', 'Electronics'),
('Product B', 'Electronics'),
('Product C', 'Clothing'),
('Product D', 'Electronics'),
('Product E', 'Books'),
('Product F', 'Clothing');

-- 复杂查询：找出电子产品客户，但排除只买书的客户，并包含服装客户
WITH electronics_customers AS (
    SELECT DISTINCT o.customer_id
    FROM orders o
    JOIN product_categories pc ON o.product_name = pc.product_name
    WHERE pc.category = 'Electronics'
),
books_only_customers AS (
    SELECT customer_id
    FROM orders o
    JOIN product_categories pc ON o.product_name = pc.product_name
    GROUP BY customer_id
    HAVING COUNT(DISTINCT pc.category) = 1 AND MAX(pc.category) = 'Books'
),
clothing_customers AS (
    SELECT DISTINCT o.customer_id
    FROM orders o
    JOIN product_categories pc ON o.product_name = pc.product_name
    WHERE pc.category = 'Clothing'
)

-- 使用组合操作
SELECT customer_id, 'Target Customer' AS segment
FROM electronics_customers
EXCEPT
SELECT customer_id, 'Target Customer' AS segment
FROM books_only_customers
UNION
SELECT customer_id, 'Target Customer' AS segment
FROM clothing_customers
ORDER BY customer_id;

分析过程:

首先找出购买电子产品的客户
排除只购买书籍的客户
添加购买服装的客户
最终得到目标客户群体

4.3 括号控制执行顺序

示例 9：使用括号控制优先级

sql

-- 不同的括号组合产生不同结果

-- 情况1：默认优先级
query1 UNION query2 INTERSECT query3
-- 等价于：query1 UNION (query2 INTERSECT query3)

-- 情况2：使用括号改变优先级
(query1 UNION query2) INTERSECT query3

-- 情况3：复杂嵌套
((query1 UNION query2) EXCEPT query3) INTERSECT query4

实际应用示例

sql

-- 查找活跃客户：在1月或2月有订单，但在3月也有订单的客户
(
    SELECT DISTINCT customer_id
    FROM orders
    WHERE EXTRACT(MONTH FROM order_date) = 1
    UNION
    SELECT DISTINCT customer_id
    FROM orders
    WHERE EXTRACT(MONTH FROM order_date) = 2
)
INTERSECT
SELECT DISTINCT customer_id
FROM orders
WHERE EXTRACT(MONTH FROM order_date) = 3;

5. 子查询中的集合操作

5.1 带有 LIMIT 和 ORDER BY 的子查询

语法注意事项当子查询需要使用 LIMIT、ORDER BY 等子句时，必须用括号括起来。 :::

示例 10：子查询限制

sql

-- ✅ 正确：使用括号包围带LIMIT的子查询
SELECT customer_id, product_name
FROM orders
WHERE customer_id IN (
    (SELECT DISTINCT customer_id FROM orders WHERE product_name = 'Product A' LIMIT 5)
    UNION ALL
    (SELECT DISTINCT customer_id FROM orders WHERE product_name = 'Product B' LIMIT 5)
);

-- ❌ 错误语法
SELECT a FROM b UNION SELECT x FROM y LIMIT 10;
-- 这会被理解为：(SELECT a FROM b UNION SELECT x FROM y) LIMIT 10

-- ✅ 正确：如果想限制第二个查询
SELECT a FROM b UNION (SELECT x FROM y LIMIT 10);

示例 11：复杂子查询组合

问题陈述: 获取每个产品类别的前 3 名客户和每个月的前 2 名活跃客户。

解决方案:

sql

-- 每个产品类别的前3名客户（按订单数量）
(
    SELECT
        o.customer_id,
        pc.category,
        COUNT(*) as order_count,
        'Top Category Customer' as type
    FROM orders o
    JOIN product_categories pc ON o.product_name = pc.product_name
    WHERE pc.category = 'Electronics'
    GROUP BY o.customer_id, pc.category
    ORDER BY order_count DESC
    LIMIT 3
)
UNION ALL
(
    SELECT
        o.customer_id,
        pc.category,
        COUNT(*) as order_count,
        'Top Category Customer' as type
    FROM orders o
    JOIN product_categories pc ON o.product_name = pc.product_name
    WHERE pc.category = 'Clothing'
    GROUP BY o.customer_id, pc.category
    ORDER BY order_count DESC
    LIMIT 3
)
ORDER BY category, order_count DESC;

6. 性能优化和最佳实践

6.1 性能考量

性能对比表

操作	性能特点	适用场景
UNION ALL	最快，无需去重	确定无重复或需要保留重复
UNION	需要排序去重，较慢	需要去除重复数据
INTERSECT	需要构建哈希表，中等	查找共同元素
EXCEPT	类似 INTERSECT，中等	查找差异元素

示例 12：性能优化技巧

sql

-- 优化前：使用UNION去重
SELECT customer_id FROM orders WHERE product_name = 'Product A'
UNION
SELECT customer_id FROM orders WHERE product_name = 'Product B';

-- 优化后：使用UNION ALL + DISTINCT（在某些情况下更快）
SELECT DISTINCT customer_id FROM (
    SELECT customer_id FROM orders WHERE product_name = 'Product A'
    UNION ALL
    SELECT customer_id FROM orders WHERE product_name = 'Product B'
) combined;

-- 或者使用EXISTS（避免集合操作）
SELECT DISTINCT o1.customer_id
FROM orders o1
WHERE o1.product_name = 'Product A'
  AND EXISTS (
      SELECT 1 FROM orders o2
      WHERE o2.customer_id = o1.customer_id
        AND o2.product_name = 'Product B'
  );

6.2 索引优化

sql

-- 为经常用于集合操作的列创建索引
CREATE INDEX idx_orders_customer_product ON orders(customer_id, product_name);
CREATE INDEX idx_orders_date ON orders(order_date);
CREATE INDEX idx_product_categories_name ON product_categories(product_name);

-- 使用复合索引优化多列查询
CREATE INDEX idx_orders_comprehensive ON orders(customer_id, product_name, order_date);

6.3 最佳实践

最佳实践清单

选择合适的操作符：确定不需要去重时使用 UNION ALL
使用索引：为参与集合操作的列创建适当索引
限制结果集：在子查询中使用 WHERE 条件减少数据量
避免不必要的排序：UNION 操作会自动排序，如不需要可考虑其他方案
使用括号明确优先级：复杂查询中明确指定操作顺序
考虑替代方案：某些情况下 EXISTS、JOIN 可能比集合操作更高效 :::

7. 实际业务应用场景

7.1 数据分析场景

示例 13：客户分析报告

问题陈述: 创建一个综合客户分析报告，包含不同客户群体的统计信息。

解决方案:

sql

-- 综合客户分析报告
WITH customer_stats AS (
    -- 高价值客户（订单金额超过平均值）
    SELECT 'High Value' as segment, COUNT(DISTINCT customer_id) as count
    FROM orders o
    JOIN (SELECT product_name,
                 CASE
                     WHEN product_name LIKE '%A%' THEN 1000
                     WHEN product_name LIKE '%B%' THEN 1500
                     ELSE 800
                 END as price
          FROM product_categories) p ON o.product_name = p.product_name
    GROUP BY customer_id
    HAVING SUM(p.price) > (
        SELECT AVG(total_value) FROM (
            SELECT customer_id, SUM(
                CASE
                    WHEN o.product_name LIKE '%A%' THEN 1000
                    WHEN o.product_name LIKE '%B%' THEN 1500
                    ELSE 800
                END
            ) as total_value
            FROM orders o
            GROUP BY customer_id
        ) avg_calc
    )

    UNION ALL

    -- 新客户（最近30天首次购买）
    SELECT 'New Customer' as segment, COUNT(DISTINCT customer_id) as count
    FROM orders
    WHERE customer_id IN (
        SELECT customer_id
        FROM orders
        GROUP BY customer_id
        HAVING MIN(order_date) >= CURRENT_DATE - INTERVAL '30 days'
    )

    UNION ALL

    -- 流失客户（90天内无订单）
    SELECT 'Churned Customer' as segment, COUNT(DISTINCT customer_id) as count
    FROM customers
    WHERE id NOT IN (
        SELECT DISTINCT customer_id
        FROM orders
        WHERE order_date >= CURRENT_DATE - INTERVAL '90 days'
    )
)

SELECT segment, count,
       ROUND(count * 100.0 / SUM(count) OVER (), 2) as percentage
FROM customer_stats
ORDER BY count DESC;

7.2 数据清理和验证

示例 14：数据一致性检查

sql

-- 检查数据一致性问题
SELECT 'Orphan Orders' as issue_type, COUNT(*) as count
FROM orders o
WHERE NOT EXISTS (SELECT 1 FROM customers c WHERE c.id = o.customer_id)

UNION ALL

SELECT 'Invalid Products' as issue_type, COUNT(*) as count
FROM orders o
WHERE NOT EXISTS (SELECT 1 FROM product_categories pc WHERE pc.product_name = o.product_name)

UNION ALL

SELECT 'Future Orders' as issue_type, COUNT(*) as count
FROM orders
WHERE order_date > CURRENT_DATE;

8. 总结

PostgreSQL 的组合查询功能提供了强大的数据集合操作能力：

核心要点

UNION: 合并两个结果集，去除重复行
UNION ALL: 合并两个结果集，保留所有行
INTERSECT: 返回两个结果集的交集
EXCEPT: 返回第一个结果集中不在第二个结果集中的行

关键注意事项

查询必须具有兼容的列结构
注意操作符的优先级和结合性
合理使用括号控制执行顺序
考虑性能影响，适当使用索引

组合查询 ​

基本概念 ​

1. UNION（并集）操作 ​

1.1 基本语法和概念 ​

1.2 UNION vs UNION ALL ​

1.3 实际示例 ​

示例 1：基本 UNION 操作 ​

示例 2：UNION ALL 与重复数据处理 ​

2. INTERSECT（交集）操作 ​

2.1 基本概念 ​

2.1 基本概念 ​

2.2 实际示例 ​

示例 3：查找共同客户 ​

示例 4：复杂条件的交集查询 ​

3. EXCEPT（差集）操作 ​

3.1 基本概念 ​

3.2 实际示例 ​

示例 5：查找未购买特定产品的客户 ​

示例 6：数据验证和清理 ​

4. 组合操作规则和约束 ​

4.1 兼容性要求 ​

示例 7：兼容性检查 ​

4.2 操作优先级和组合 ​

优先级规则 ​

示例 8：复杂组合操作 ​

4.3 括号控制执行顺序 ​

示例 9：使用括号控制优先级 ​

实际应用示例 ​

5. 子查询中的集合操作 ​

5.1 带有 LIMIT 和 ORDER BY 的子查询 ​

示例 10：子查询限制 ​

示例 11：复杂子查询组合 ​

6. 性能优化和最佳实践 ​

6.1 性能考量 ​

性能对比表 ​

示例 12：性能优化技巧 ​

6.2 索引优化 ​

6.3 最佳实践 ​

7. 实际业务应用场景 ​

7.1 数据分析场景 ​

示例 13：客户分析报告 ​

7.2 数据清理和验证 ​

示例 14：数据一致性检查 ​

8. 总结 ​

核心要点 ​

关键注意事项 ​

组合查询

基本概念

1. UNION（并集）操作

1.1 基本语法和概念

1.2 UNION vs UNION ALL

1.3 实际示例

示例 1：基本 UNION 操作

示例 2：UNION ALL 与重复数据处理

2. INTERSECT（交集）操作

2.1 基本概念

2.1 基本概念

2.2 实际示例

示例 3：查找共同客户

示例 4：复杂条件的交集查询

3. EXCEPT（差集）操作

3.1 基本概念

3.2 实际示例

示例 5：查找未购买特定产品的客户

示例 6：数据验证和清理

4. 组合操作规则和约束

4.1 兼容性要求

示例 7：兼容性检查

4.2 操作优先级和组合

优先级规则

示例 8：复杂组合操作

4.3 括号控制执行顺序

示例 9：使用括号控制优先级

实际应用示例

5. 子查询中的集合操作

5.1 带有 LIMIT 和 ORDER BY 的子查询

示例 10：子查询限制

示例 11：复杂子查询组合

6. 性能优化和最佳实践

6.1 性能考量

性能对比表

示例 12：性能优化技巧

6.2 索引优化

6.3 最佳实践

7. 实际业务应用场景

7.1 数据分析场景

示例 13：客户分析报告

7.2 数据清理和验证

示例 14：数据一致性检查

8. 总结

核心要点

关键注意事项