多表连接 #
一、JOIN概述 #
1.1 连接类型 #
text
JOIN 连接类型
┌─────────────────────────────────────────────────────────────┐
│ │
│ INNER JOIN (内连接) │
│ └── 只返回匹配的行 │
│ │
│ LEFT JOIN (左连接) │
│ └── 返回左表所有行，右表匹配或NULL │
│ │
│ RIGHT JOIN (右连接) │
│ └── 返回右表所有行，左表匹配或NULL │
│ │
│ FULL JOIN (全连接) │
│ └── 返回所有行，匹配或NULL │
│ │
│ CROSS JOIN (交叉连接) │
│ └── 笛卡尔积 │
│ │
│ NATURAL JOIN (自然连接) │
│ └── 自动匹配同名列 │
│ │
└─────────────────────────────────────────────────────────────┘
1.2 示例表 #
sql
-- 用户表
CREATE TABLE users (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name VARCHAR(100) NOT NULL,
email VARCHAR(100)
);
-- 订单表
CREATE TABLE orders (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID REFERENCES users(id),
order_no VARCHAR(50),
amount DECIMAL(10, 2),
status VARCHAR(20),
created_at TIMESTAMP DEFAULT NOW()
);
-- 订单项表
CREATE TABLE order_items (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
order_id UUID REFERENCES orders(id),
product_name VARCHAR(100),
quantity INT,
price DECIMAL(10, 2)
);
-- 插入测试数据
INSERT INTO users (id, name, email) VALUES
('11111111-1111-1111-1111-111111111111', 'Alice', 'alice@example.com'),
('22222222-2222-2222-2222-222222222222', 'Bob', 'bob@example.com'),
('33333333-3333-3333-3333-333333333333', 'Carol', 'carol@example.com');
INSERT INTO orders (id, user_id, order_no, amount, status) VALUES
('aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', '11111111-1111-1111-1111-111111111111', 'ORD001', 100.00, 'completed'),
('bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb', '11111111-1111-1111-1111-111111111111', 'ORD002', 200.00, 'pending'),
('cccccccc-cccc-cccc-cccc-cccccccccccc', '22222222-2222-2222-2222-222222222222', 'ORD003', 150.00, 'completed');
二、INNER JOIN #
2.1 基本语法 #
sql
-- INNER JOIN 只返回匹配的行
SELECT u.name, o.order_no, o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;
-- 简写 JOIN (默认是 INNER JOIN)
SELECT u.name, o.order_no, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id;
-- 使用 WHERE 代替 ON
SELECT u.name, o.order_no, o.amount
FROM users u, orders o
WHERE u.id = o.user_id;
2.2 多表连接 #
sql
-- 三表连接
SELECT
u.name,
o.order_no,
oi.product_name,
oi.quantity,
oi.price
FROM users u
INNER JOIN orders o ON u.id = o.user_id
INNER JOIN order_items oi ON o.id = oi.order_id;
-- 多表连接示例
SELECT
u.name AS customer,
o.order_no,
o.amount AS order_total,
oi.product_name,
oi.quantity * oi.price AS item_total
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN order_items oi ON o.id = oi.order_id
WHERE o.status = 'completed';
2.3 连接条件 #
sql
-- 单条件连接
SELECT u.name, o.order_no
FROM users u
JOIN orders o ON u.id = o.user_id;
-- 多条件连接
SELECT u.name, o.order_no, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id AND o.status = 'completed';
-- 使用 USING (列名相同)
SELECT u.name, o.order_no
FROM users u
JOIN orders o USING (user_id); -- 如果两表都有 user_id 列
三、LEFT JOIN #
3.1 基本语法 #
sql
-- LEFT JOIN 返回左表所有行
SELECT u.name, o.order_no, o.amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;
-- 结果包含没有订单的用户
-- Carol 没有订单，order_no 和 amount 为 NULL
3.2 过滤NULL值 #
sql
-- 找出没有订单的用户
SELECT u.name
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;
-- 等价于 NOT EXISTS
SELECT u.name
FROM users u
WHERE NOT EXISTS (
SELECT 1 FROM orders o
WHERE o.user_id = u.id
);
-- 等价于 NOT IN
SELECT u.name
FROM users u
WHERE u.id NOT IN (
SELECT user_id FROM orders
);
3.3 条件位置 #
sql
-- 条件在 ON 中
SELECT u.name, o.order_no, o.amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id AND o.status = 'completed';
-- 返回所有用户，但只显示 completed 订单
-- 条件在 WHERE 中
SELECT u.name, o.order_no, o.amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.status = 'completed';
-- 只返回有 completed 订单的用户，变成 INNER JOIN
四、RIGHT JOIN #
4.1 基本语法 #
sql
-- RIGHT JOIN 返回右表所有行
SELECT u.name, o.order_no, o.amount
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;
-- RIGHT JOIN 可以改写为 LEFT JOIN
SELECT u.name, o.order_no, o.amount
FROM orders o
LEFT JOIN users u ON u.id = o.user_id;
五、FULL JOIN #
5.1 基本语法 #
sql
-- FULL JOIN 返回所有行
SELECT u.name, o.order_no, o.amount
FROM users u
FULL JOIN orders o ON u.id = o.user_id;
-- 结果包含:
-- 1. 有用户的订单
-- 2. 没有订单的用户 (order_no, amount 为 NULL)
-- 3. 没有用户的订单 (name 为 NULL)
5.2 找出不匹配的行 #
sql
-- 找出不匹配的行
SELECT u.name, o.order_no
FROM users u
FULL JOIN orders o ON u.id = o.user_id
WHERE u.id IS NULL OR o.user_id IS NULL;
六、CROSS JOIN #
6.1 基本语法 #
sql
-- CROSS JOIN 笛卡尔积
SELECT u.name, o.order_no
FROM users u
CROSS JOIN orders o;
-- 等价于
SELECT u.name, o.order_no
FROM users u, orders o;
-- 结果行数 = users行数 × orders行数
6.2 使用场景 #
sql
-- 生成所有组合
-- 例如: 所有用户与所有产品的组合
SELECT u.name, p.product_name
FROM users u
CROSS JOIN products p;
-- 生成日期序列
SELECT
d.date,
u.name
FROM (
SELECT generate_series(
CURRENT_DATE - INTERVAL '6 days',
CURRENT_DATE,
INTERVAL '1 day'
)::date AS date
) d
CROSS JOIN users u;
七、自连接 #
7.1 基本语法 #
sql
-- 员工表
CREATE TABLE employees (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name VARCHAR(100),
manager_id UUID REFERENCES employees(id)
);
-- 自连接查找员工和经理
SELECT
e.name AS employee,
m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;
-- 查找同一经理下的员工
SELECT
e1.name,
e2.name AS colleague
FROM employees e1
JOIN employees e2 ON e1.manager_id = e2.manager_id
WHERE e1.id != e2.id;
八、连接性能 #
8.1 连接算法 #
text
JOIN 算法
┌─────────────────────────────────────────────────────────────┐
│ │
│ Hash Join: │
│ ├── 构建哈希表 │
│ ├── 适合大表连接 │
│ └── 等值连接 │
│ │
│ Merge Join: │
│ ├── 已排序数据 │
│ ├── 适合有序数据连接 │
│ └── 范围连接 │
│ │
│ Lookup Join: │
│ ├── 索引查找 │
│ ├── 适合小表驱动大表 │
│ └── 适合有索引的表 │
│ │
└─────────────────────────────────────────────────────────────┘
8.2 性能优化 #
sql
-- 查看执行计划
EXPLAIN SELECT u.name, o.order_no
FROM users u
JOIN orders o ON u.id = o.user_id;
-- 确保连接列有索引
CREATE INDEX idx_orders_user_id ON orders(user_id);
-- 小表驱动大表
-- 让小表作为驱动表
SELECT /*+ HASH_JOIN(o u) */ u.name, o.order_no
FROM orders o
JOIN users u ON u.id = o.user_id;
8.3 连接优化建议 #
text
JOIN 优化建议
┌─────────────────────────────────────────────────────────────┐
│ │
│ 1. 使用索引 │
│ ├── 连接列创建索引 │
│ └── 加速连接操作 │
│ │
│ 2. 减少连接表数量 │
│ ├── 避免过多表连接 │
│ └── 考虑反范式设计 │
│ │
│ 3. 选择合适的连接类型 │
│ ├── INNER JOIN 最快 │
│ └── LEFT/FULL JOIN 较慢 │
│ │
│ 4. 过滤条件放在 WHERE │
│ ├── 减少连接数据量 │
│ └── 提高查询效率 │
│ │
│ 5. 使用覆盖索引 │
│ ├── 避免回表 │
│ └── 提高查询速度 │
│ │
└─────────────────────────────────────────────────────────────┘
九、复杂连接示例 #
9.1 多表统计 #
sql
-- 用户订单统计
SELECT
u.name,
COUNT(o.id) AS order_count,
COALESCE(SUM(o.amount), 0) AS total_amount,
COALESCE(AVG(o.amount), 0) AS avg_amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name
ORDER BY total_amount DESC;
9.2 分层查询 #
sql
-- 订单明细统计
SELECT
u.name AS customer,
o.order_no,
COUNT(oi.id) AS item_count,
SUM(oi.quantity * oi.price) AS calculated_total,
o.amount AS order_amount
FROM users u
JOIN orders o ON u.id = o.user_id
LEFT JOIN order_items oi ON o.id = oi.order_id
GROUP BY u.id, u.name, o.id, o.order_no, o.amount
HAVING SUM(oi.quantity * oi.price) != o.amount;
十、总结 #
JOIN 语句要点：
| 类型 | 说明 |
|---|---|
| INNER JOIN | 只返回匹配行 |
| LEFT JOIN | 返回左表所有行 |
| RIGHT JOIN | 返回右表所有行 |
| FULL JOIN | 返回所有行 |
| CROSS JOIN | 笛卡尔积 |
| 自连接 | 表与自己连接 |
下一步，让我们学习子查询！
最后更新：2026-03-27