PostgreSQL 子查询表达式详解

子查询表达式是 PostgreSQL 中强大的查询工具，它们允许我们在一个查询中嵌套另一个查询，用于复杂的数据检索和条件判断。本章将详细介绍 PostgreSQL 中可用的 SQL 兼容子查询表达式。

什么是子查询表达式

子查询表达式是返回布尔值（true/false）结果的特殊表达式形式。它们通过在主查询中嵌入一个或多个 SELECT 语句来实现复杂的条件判断和数据筛选。

1. EXISTS 子查询

基本语法

sql

EXISTS (subquery)

工作原理

EXISTS 检查子查询是否返回至少一行数据。如果子查询返回一行或多行，结果为 true；如果子查询没有返回任何行，结果为 false。

TIP

性能优化 EXISTS 子查询只执行到确定是否有返回行为止，不会完整执行整个子查询，这使得它在性能上通常优于其他形式的子查询。

实际业务场景示例

示例 1：查找有订单的客户

问题陈述：在电商系统中，我们需要找出所有至少有一个订单的客户。

表结构：

sql

-- 客户表
CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    customer_name VARCHAR(100),
    email VARCHAR(100),
    city VARCHAR(50)
);

-- 订单表
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(customer_id),
    order_date DATE,
    total_amount DECIMAL(10,2)
);

解决方案：

sql

SELECT customer_id, customer_name, email, city
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

输入数据：

sql

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

INSERT INTO orders VALUES
(1, 1, '2024-01-15', 299.99),
(2, 1, '2024-02-20', 159.50),
(3, 2, '2024-03-10', 89.99);

输出结果： | customer_id | customer_name | email | city | |-------------|---------------|-------|------| | 1 | 张三 | [email protected] | 北京 | | 2 | 李四 | [email protected] | 上海 |

分析过程：

对于客户张三（ID=1）：子查询找到了两个订单，EXISTS 返回 true
对于客户李四（ID=2）：子查询找到了一个订单，EXISTS 返回 true
对于客户王五（ID=3）：子查询没有找到订单，EXISTS 返回 false，因此不包含在结果中

示例 2：查找特定时间段内有活动的客户

问题陈述：查找在 2024 年 2 月有购买活动的客户。

sql

SELECT customer_name, email
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
    AND o.order_date >= '2024-02-01'
    AND o.order_date < '2024-03-01'
);

WARNING

注意事项

子查询中的 SELECT 1 是常见的编码约定，因为 EXISTS 只关心是否有行返回，不关心返回的具体内容
避免在子查询中使用有副作用的函数（如序列函数），因为执行次数不可预测

2. IN 子查询

基本语法

sql

expression IN (subquery)
row_constructor IN (subquery)

工作原理

IN 检查左侧表达式的值是否存在于子查询返回的结果集中。子查询必须返回恰好一列数据。

实际业务场景示例

示例 1：查找购买了特定商品的客户

问题陈述：在电商系统中，查找购买了"笔记本电脑"或"平板电脑"的所有客户。

表结构扩展：

sql

-- 商品表
CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(100),
    category VARCHAR(50),
    price DECIMAL(10,2)
);

-- 订单详情表
CREATE TABLE order_items (
    order_item_id SERIAL PRIMARY KEY,
    order_id INTEGER REFERENCES orders(order_id),
    product_id INTEGER REFERENCES products(product_id),
    quantity INTEGER,
    unit_price DECIMAL(10,2)
);

解决方案：

sql

SELECT DISTINCT c.customer_name, c.email
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IN (
    SELECT oi.order_id
    FROM order_items oi
    JOIN products p ON oi.product_id = p.product_id
    WHERE p.product_name IN ('笔记本电脑', '平板电脑')
);

输入数据：

sql

INSERT INTO products VALUES
(1, '笔记本电脑', '电子产品', 5999.00),
(2, '平板电脑', '电子产品', 2999.00),
(3, '鼠标', '电子产品', 99.00);

INSERT INTO order_items VALUES
(1, 1, 1, 1, 5999.00),  -- 张三买了笔记本电脑
(2, 2, 3, 2, 99.00),    -- 张三买了鼠标
(3, 3, 2, 1, 2999.00);  -- 李四买了平板电脑

输出结果： | customer_name | email | |---------------|-------| | 张三 | [email protected] | | 李四 | [email protected] |

示例 2：多列 IN 子查询

问题陈述：查找在特定城市特定日期有订单的客户信息。

sql

SELECT customer_name, city, email
FROM customers c
WHERE (c.city, c.customer_id) IN (
    SELECT '北京', o.customer_id
    FROM orders o
    WHERE o.order_date = '2024-01-15'
    UNION
    SELECT '上海', o.customer_id
    FROM orders o
    WHERE o.order_date = '2024-03-10'
);

INFO

NULL 值处理如果左侧表达式为 NULL，或者子查询结果中包含 NULL 值，IN 表达式的结果可能为 NULL 而不是 false。这符合 SQL 中 NULL 值的三值逻辑规则。

3. NOT IN 子查询

基本语法

sql

expression NOT IN (subquery)
row_constructor NOT IN (subquery)

实际业务场景示例

示例：查找没有订单的客户

问题陈述：找出系统中还没有下过任何订单的客户，用于营销推广。

sql

SELECT customer_name, email, city
FROM customers
WHERE customer_id NOT IN (
    SELECT DISTINCT customer_id
    FROM orders
    WHERE customer_id IS NOT NULL
);

输出结果： | customer_name | email | city | |---------------|-------|------| | 王五 | [email protected] | 广州 |

DANGER

NULL 值陷阱 NOT IN 在处理 NULL 值时有特殊行为。如果子查询结果中包含 NULL 值，NOT IN 表达式可能返回 NULL 而不是期望的 true/false。建议在子查询中添加 WHERE column IS NOT NULL 条件。

安全的写法：

sql

-- 推荐写法：过滤 NULL 值
SELECT customer_name, email, city
FROM customers
WHERE customer_id NOT IN (
    SELECT customer_id
    FROM orders
    WHERE customer_id IS NOT NULL
);

-- 或者使用 NOT EXISTS（更安全）
SELECT customer_name, email, city
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

4. ANY/SOME 子查询

基本语法

sql

expression operator ANY (subquery)
expression operator SOME (subquery)

SOME 是 ANY 的同义词。= ANY 等价于 IN。

实际业务场景示例

示例：查找订单金额超过任一高价值客户的客户

问题陈述：在客户分析中，找出订单金额超过任一 VIP 客户（总消费超过 1000 元）最低订单金额的普通客户。

sql

-- 首先创建客户级别视图
WITH customer_total AS (
    SELECT
        c.customer_id,
        c.customer_name,
        COALESCE(SUM(o.total_amount), 0) as total_spent
    FROM customers c
    LEFT JOIN orders o ON c.customer_id = o.customer_id
    GROUP BY c.customer_id, c.customer_name
),
vip_customers AS (
    SELECT customer_id
    FROM customer_total
    WHERE total_spent > 1000
)
SELECT DISTINCT c.customer_name, o.total_amount
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE o.total_amount > ANY (
    SELECT o2.total_amount
    FROM orders o2
    WHERE o2.customer_id IN (SELECT customer_id FROM vip_customers)
);

分析过程：

首先识别 VIP 客户（总消费 > 1000 元）
获取所有 VIP 客户的订单金额
找出订单金额超过任一 VIP 客户订单金额的所有订单

比较运算符的使用

sql

-- 查找价格高于任一竞争对手产品的商品
SELECT product_name, price
FROM products
WHERE price > ANY (
    SELECT price
    FROM competitor_products
    WHERE category = products.category
);

-- 查找订单日期晚于任一特定客户订单日期的订单
SELECT order_id, order_date, customer_id
FROM orders
WHERE order_date > ANY (
    SELECT order_date
    FROM orders
    WHERE customer_id = 1
);

5. ALL 子查询

基本语法

sql

expression operator ALL (subquery)

<> ALL 等价于 NOT IN。

实际业务场景示例

示例：查找价格高于所有竞争对手的产品

问题陈述：在定价策略中，找出价格高于同类别所有竞争对手产品的自有产品。

sql

-- 竞争对手产品表
CREATE TABLE competitor_products (
    competitor_product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(100),
    category VARCHAR(50),
    price DECIMAL(10,2),
    competitor_name VARCHAR(100)
);

-- 查询解决方案
SELECT
    p.product_name,
    p.category,
    p.price,
    (SELECT AVG(price) FROM competitor_products cp WHERE cp.category = p.category) as avg_competitor_price
FROM products p
WHERE p.price > ALL (
    SELECT cp.price
    FROM competitor_products cp
    WHERE cp.category = p.category
    AND cp.price IS NOT NULL
);

输入数据：

sql

INSERT INTO competitor_products VALUES
(1, '竞品笔记本A', '电子产品', 5500.00, '竞争对手A'),
(2, '竞品笔记本B', '电子产品', 5800.00, '竞争对手B'),
(3, '竞品平板A', '电子产品', 2500.00, '竞争对手A'),
(4, '竞品平板B', '电子产品', 2800.00, '竞争对手B');

分析过程：

笔记本电脑（5999 元）vs 竞品笔记本（5500 元，5800 元）→ 5999 > ALL(5500,5800) = true
平板电脑（2999 元）vs 竞品平板（2500 元，2800 元）→ 2999 > ALL(2500,2800) = true

示例：查找订单金额低于所有历史平均值的订单

sql

SELECT
    o.order_id,
    o.order_date,
    o.total_amount,
    c.customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.total_amount < ALL (
    SELECT AVG(total_amount)
    FROM orders o2
    WHERE EXTRACT(MONTH FROM o2.order_date) != EXTRACT(MONTH FROM o.order_date)
    GROUP BY EXTRACT(MONTH FROM o2.order_date)
);

6. 单行比较

基本语法

sql

row_constructor operator (subquery)

子查询必须返回恰好一行数据，列数与左侧行构造器匹配。

实际业务场景示例

示例：比较客户信息与平均统计

问题陈述：查找订单数量和总金额都高于平均水平的客户。

sql

-- 使用行构造器进行多列比较
SELECT
    c.customer_name,
    COUNT(o.order_id) as order_count,
    COALESCE(SUM(o.total_amount), 0) as total_spent
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name
HAVING (COUNT(o.order_id), COALESCE(SUM(o.total_amount), 0)) > (
    SELECT
        AVG(order_count),
        AVG(total_spent)
    FROM (
        SELECT
            c2.customer_id,
            COUNT(o2.order_id) as order_count,
            COALESCE(SUM(o2.total_amount), 0) as total_spent
        FROM customers c2
        LEFT JOIN orders o2 ON c2.customer_id = o2.customer_id
        GROUP BY c2.customer_id
    ) customer_stats
);

性能优化与最佳实践

1. 选择合适的子查询类型

2. 性能对比表

子查询类型	性能特点	适用场景	注意事项
EXISTS	优秀，短路执行	检查存在性	推荐用于大数据集
IN	良好，但需要完整结果	精确匹配小结果集	注意 NULL 值处理
NOT IN	较差，需要完整扫描	排除匹配	必须处理 NULL 值
ANY/SOME	中等，取决于数据分布	部分条件满足	考虑索引优化
ALL	较差，需要完整扫描	全部条件满足	小数据集使用

3. 索引优化建议

sql

-- 为子查询创建合适的索引
CREATE INDEX idx_orders_customer_id ON orders(customer_id);
CREATE INDEX idx_orders_date_customer ON orders(order_date, customer_id);
CREATE INDEX idx_products_category_price ON products(category, price);

4. 替代方案

TIP

使用 JOIN 替代子查询在某些情况下，使用 JOIN 可能比子查询性能更好：

sql

-- 使用 EXISTS 子查询
SELECT c.customer_name
FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);

-- 使用 INNER JOIN（去重）
SELECT DISTINCT c.customer_name
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id;

常见陷阱与解决方案

1. NULL 值处理

sql

-- ❌ 错误：可能因 NULL 值导致意外结果
SELECT * FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders);

-- ✅ 正确：过滤 NULL 值
SELECT * FROM customers
WHERE customer_id NOT IN (
    SELECT customer_id FROM orders WHERE customer_id IS NOT NULL
);

-- ✅ 更好：使用 NOT EXISTS
SELECT * FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);

2. 性能问题

sql

-- ❌ 低效：子查询在每行都执行
SELECT * FROM customers c
WHERE (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.customer_id) > 5;

-- ✅ 高效：使用连接和分组
SELECT c.*
FROM customers c
INNER JOIN (
    SELECT customer_id
    FROM orders
    GROUP BY customer_id
    HAVING COUNT(*) > 5
) active_customers ON c.customer_id = active_customers.customer_id;

总结

子查询表达式是 PostgreSQL 中强大的工具，每种类型都有其特定的用途和性能特征：

EXISTS：用于存在性检查，性能最优
IN/NOT IN：用于集合包含判断，注意 NULL 值处理
ANY/SOME：用于部分条件满足的场景
ALL：用于全部条件满足的场景
单行比较：用于精确的单行多列比较

在实际应用中，选择合适的子查询类型、正确处理 NULL 值、合理设计索引是确保查询性能的关键因素。通过深入理解每种子查询表达式的特点和适用场景，可以编写出高效、可靠的 PostgreSQL 查询。

PostgreSQL 子查询表达式详解 ​

什么是子查询表达式 ​

1. EXISTS 子查询 ​

基本语法 ​

工作原理 ​

实际业务场景示例 ​

示例 1：查找有订单的客户 ​

示例 2：查找特定时间段内有活动的客户 ​

2. IN 子查询 ​

基本语法 ​

工作原理 ​

实际业务场景示例 ​

示例 1：查找购买了特定商品的客户 ​

示例 2：多列 IN 子查询 ​

3. NOT IN 子查询 ​

基本语法 ​

实际业务场景示例 ​

示例：查找没有订单的客户 ​

4. ANY/SOME 子查询 ​

基本语法 ​

实际业务场景示例 ​

示例：查找订单金额超过任一高价值客户的客户 ​

比较运算符的使用 ​

5. ALL 子查询 ​

基本语法 ​

实际业务场景示例 ​

示例：查找价格高于所有竞争对手的产品 ​

示例：查找订单金额低于所有历史平均值的订单 ​

6. 单行比较 ​

基本语法 ​

实际业务场景示例 ​

示例：比较客户信息与平均统计 ​

性能优化与最佳实践 ​

1. 选择合适的子查询类型 ​

2. 性能对比表 ​

3. 索引优化建议 ​

4. 替代方案 ​

常见陷阱与解决方案 ​

1. NULL 值处理 ​

2. 性能问题 ​

总结 ​

PostgreSQL 子查询表达式详解

什么是子查询表达式

1. EXISTS 子查询

基本语法

工作原理

实际业务场景示例

示例 1：查找有订单的客户

示例 2：查找特定时间段内有活动的客户

2. IN 子查询

基本语法

工作原理

实际业务场景示例

示例 1：查找购买了特定商品的客户

示例 2：多列 IN 子查询

3. NOT IN 子查询

基本语法

实际业务场景示例

示例：查找没有订单的客户

4. ANY/SOME 子查询

基本语法

实际业务场景示例

示例：查找订单金额超过任一高价值客户的客户

比较运算符的使用

5. ALL 子查询

基本语法

实际业务场景示例

示例：查找价格高于所有竞争对手的产品

示例：查找订单金额低于所有历史平均值的订单

6. 单行比较

基本语法

实际业务场景示例

示例：比较客户信息与平均统计

性能优化与最佳实践

1. 选择合适的子查询类型

2. 性能对比表

3. 索引优化建议

4. 替代方案

常见陷阱与解决方案

1. NULL 值处理

2. 性能问题

总结