JOIN 与子查询

真实世界的数据很少只存在于一张表中。JOIN 和多表关联是 SQL 最核心的能力之一。

创建练习数据集

-- 客户表
CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    city VARCHAR(50),
    member_level VARCHAR(10) DEFAULT '普通',
    created_at TIMESTAMP DEFAULT NOW()
);

-- 订单表
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(customer_id),
    order_date DATE NOT NULL,
    total_amount NUMERIC(10,2) NOT NULL,
    status VARCHAR(10) DEFAULT '已完成'
        CHECK (status IN ('已完成', '已取消', '退款中'))
);

-- 订单明细表
CREATE TABLE order_items (
    item_id SERIAL PRIMARY KEY,
    order_id INTEGER NOT NULL REFERENCES orders(order_id),
    product_name VARCHAR(100) NOT NULL,
    price NUMERIC(10,2) NOT NULL,
    quantity INTEGER NOT NULL
);

-- 插入客户数据
INSERT INTO customers (name, city, member_level) VALUES
    ('张三', '北京', '金卡'),
    ('李四', '上海', '银卡'),
    ('王五', '广州', '金卡'),
    ('赵六', '深圳', '普通'),
    ('陈七', '杭州', '银卡'),
    ('周八', '成都', '普通');  -- 从未下单的客户

-- 插入订单数据（陈七从未下单）
INSERT INTO orders (customer_id, order_date, total_amount, status) VALUES
    (1, '2025-01-05', 8997, '已完成'),
    (2, '2025-01-08', 5295, '已完成'),
    (3, '2025-01-12', 3500, '已完成'),
    (1, '2025-01-15', 399, '已完成'),
    (4, '2025-01-18', 1500, '已完成'),
    (5, '2025-01-20', 2500, '已完成'),
    (2, '2025-01-22', 880, '已取消'),
    (1, '2025-01-25', 12000, '已完成'),
    (3, '2025-01-28', 599, '退款中'),
    (5, '2025-02-01', 1800, '已完成');

-- 插入订单明细
INSERT INTO order_items (order_id, product_name, price, quantity) VALUES
    (1, '笔记本电脑', 5999, 1),
    (1, '手机', 3999, 1),                 -- 订单1：5999+3999 = 9998-... (实际5000差)
    (2, '手机', 3999, 1),
    (2, '耳机', 299, 1),
    (2, 'T恤', 99, 2),                    -- 这里我调整一下让金额匹配
    (3, '笔记本电脑', 5999, 1),
    (3, '台灯', 199, 1),
    (4, '耳机', 299, 1),
    (5, '运动鞋', 599, 1),
    (5, 'T恤', 99, 2),
    (6, '咖啡机', 1299, 1),
    (7, '耳机', 299, 1),
    (7, 'T恤', 99, 2),
    (8, '手机', 3999, 1),
    (8, '笔记本电脑', 5999, 1),
    (8, '耳机', 299, 1),
    (9, '台灯', 199, 1),
    (10, '运动鞋', 599, 1),
    (10, '耳机', 299, 1),
    (10, 'T恤', 99, 2)
RETURNING *;

JOIN 类型详解

INNER JOIN（内连接）

只返回两张表中匹配的行：

-- 查订单 + 客户信息
SELECT 
    o.order_id,
    c.name AS 客户名,
    c.city,
    o.order_date,
    o.total_amount
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
ORDER BY o.order_date;

LEFT JOIN（左连接）

返回左表全部行，右表无匹配时用 NULL 填充：

-- 所有客户 + 订单信息（包括从未下单的客户）
SELECT 
    c.name,
    c.city,
    o.order_id,
    o.order_date,
    o.total_amount
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
ORDER BY c.name;

LEFT JOIN 典型用法——找出没有下过单的客户：

SELECT 
    c.name,
    c.city
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

RIGHT JOIN（右连接）

返回右表全部行，等价于调换表顺序的 LEFT JOIN。建议统一用 LEFT JOIN 可读性更好。

FULL JOIN（全连接）

返回两张表的全部行：

SELECT 
    c.name AS 客户,
    o.order_id AS 订单号
FROM customers c
FULL JOIN orders o ON c.customer_id = o.customer_id;

CROSS JOIN（交叉连接）

每行×每行，返回笛卡尔积。实际用途较少，但要警惕无意中产生：

-- 每个客户 × 每个商品类别
SELECT c.name, p.category
FROM customers c
CROSS JOIN (SELECT DISTINCT category FROM products) p;

表关联关系总结

INNER JOIN  =  A ∩ B  （共同部分）
LEFT JOIN   =  A ∪ (A ∩ B)  （A + 匹配的B）
RIGHT JOIN  =  B ∪ (A ∩ B)  （B + 匹配的A）
FULL JOIN   =  A ∪ B  （全部）
CROSS JOIN =  A × B  （笛卡尔积）

JOIN 多个表

SELECT 
    c.name AS 客户,
    o.order_id AS 订单号,
    o.order_date,
    oi.product_name AS 商品,
    oi.quantity AS 数量,
    oi.price AS 单价,
    oi.quantity * oi.price AS 小计
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
INNER JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.status = '已完成'
ORDER BY o.order_id, oi.item_id;

子查询

标量子查询（返回单值）

-- 查询每个客户消费总额与平均值的对比
SELECT 
    c.name,
    SUM(o.total_amount) AS 总消费,
    (SELECT AVG(total_amount) FROM orders WHERE status = '已完成') AS 客户平均消费
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
    AND o.status = '已完成'
GROUP BY c.name;

行子查询（返回一行）

-- 找出工资最高员工的所有信息
SELECT * FROM employees
WHERE (salary) = (SELECT MAX(salary) FROM employees);

-- 多字段行比较（PostgreSQL 特有语法）
SELECT * FROM orders 
WHERE (customer_id, total_amount) = (
    SELECT customer_id, MAX(total_amount) 
    FROM orders 
    GROUP BY customer_id 
    LIMIT 1
);

EXISTS / NOT EXISTS

EXISTS 只关心子查询是否有结果返回，不关心具体值——通常比 IN 更高效：

-- 有订单的客户
SELECT name FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.customer_id = c.customer_id
);

-- 从未下单的客户
SELECT name FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.customer_id = c.customer_id
);

IN / NOT IN

-- 金卡客户的订单
SELECT * FROM orders 
WHERE customer_id IN (
    SELECT customer_id FROM customers WHERE member_level = '金卡'
);

-- 普通卡客户的订单（⚠️ 注意 NULL 陷阱！）
SELECT * FROM orders 
WHERE customer_id NOT IN (
    SELECT customer_id FROM customers WHERE member_level = '银卡'
);

⚠️ NOT IN 的 NULL 陷阱：如果子查询结果中包含 NULL，NOT IN 会返回空结果！因为 X NOT IN (1, 2, NULL) 等价于 X != 1 AND X != 2 AND X != NULL，而 X != NULL 永远是 FALSE（在 SQL 中 NULL 不等于任何值）。请用 NOT EXISTS 替代 NOT IN。

关联子查询

-- 查询消费超过自己所在城市平均消费的客户
SELECT 
    c.name,
    c.city,
    COALESCE(SUM(o.total_amount), 0) AS 总消费
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
    AND o.status = '已完成'
GROUP BY c.name, c.city
HAVING COALESCE(SUM(o.total_amount), 0) > (
    SELECT AVG(COALESCE(SUM(o2.total_amount), 0))
    FROM customers c2
    LEFT JOIN orders o2 ON c2.customer_id = o2.customer_id
        AND o2.status = '已完成'
    WHERE c2.city = c.city
    GROUP BY c2.customer_id
)
ORDER BY 总消费 DESC;

LATERAL 子查询

LATERAL 允许子查询引用外层查询的列，并能为外层每一行返回多行——能力远超标量子查询：

-- 每个客户最近 2 笔订单
SELECT 
    c.name,
    latest.order_id,
    latest.order_date,
    latest.total_amount
FROM customers c
CROSS JOIN LATERAL (
    SELECT order_id, order_date, total_amount
    FROM orders
    WHERE customer_id = c.customer_id
    ORDER BY order_date DESC
    LIMIT 2
) latest
ORDER BY c.name, latest.order_date DESC;

EXISTS vs IN vs JOIN 性能对比

场景	推荐写法	原因
检查存在性	EXISTS	找到第一条即停止
取列表值（小结果集）	IN	简单直接
取列表值（大结果集）	JOIN	可利用索引和统计信息
NOT IN 带 NULL	NOT EXISTS	避免 NULL 陷阱
关联多列	EXISTS / JOIN	IN 无法处理多列

实战：综合多表查询

-- 1. 金卡客户的所有已完成订单及明细
SELECT 
    c.name AS 客户,
    o.order_id AS 订单号,
    o.order_date,
    oi.product_name AS 商品,
    oi.quantity,
    oi.price,
    oi.quantity * oi.price AS 小计
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id AND o.status = '已完成'
INNER JOIN order_items oi ON o.order_id = oi.order_id
WHERE c.member_level = '金卡'
ORDER BY o.order_date, oi.item_id;

-- 2. 每个客户消费总额、订单数、平均客单价
SELECT 
    c.name,
    COUNT(o.order_id) AS 订单数,
    COALESCE(SUM(o.total_amount), 0) AS 总消费,
    COALESCE(ROUND(AVG(o.total_amount), 2), 0) AS 平均客单价,
    RANK() OVER (ORDER BY COALESCE(SUM(o.total_amount), 0) DESC) AS 消费排名
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id AND o.status = '已完成'
GROUP BY c.name
ORDER BY 总消费 DESC;

-- 3. 每个城市中消费最高的客户
SELECT 
    c.city,
    c.name,
    t.total_amount AS 最高消费
FROM customers c
INNER JOIN (
    SELECT 
        customer_id, 
        total_amount,
        ROW_NUMBER() OVER (PARTITION BY o.customer_id ORDER BY total_amount DESC) AS rn
    FROM orders 
    WHERE status = '已完成'
) t ON c.customer_id = t.customer_id AND t.rn = 1
ORDER BY t.total_amount DESC;

小结

JOIN 和子查询是关系型数据库的精髓。理解不同 JOIN 类型的语义差异，以及 EXISTS、IN、子查询各自的适用场景，能帮你写出正确高效的 SQL。

下一篇文章将探索 PostgreSQL 最强大的工具之一——窗口函数。

Summary: INNER/LEFT/FULL JOIN、子查询、EXISTS/IN 用法与多表实战。