数据库原理与选型 / 系统课时
多表连接、别名与连接条件
多表连接、别名与连接条件
多表查询真正的难点不是写更多 JOIN,而是保证每一次连接都说明“哪两张表、按什么关系匹配”。本节用订单、客户和订单明细三张表,完成连续连接、别名、复合条件与重复行控制,并学会验证结果是否被意外放大。
学习目标
- 使用表别名组织三张及以上表的连续连接。
- 正确编写单列与复合连接条件,避免笛卡尔积和错配。
- 判断重复行来自一对多关系还是错误连接,并选择合适的控制方式。
连续连接的核心规则
多表连接可以看成“前一次连接结果继续和下一张表连接”。每个 JOIN 都应有独立、可解释的 ON 条件。别名不是装饰,它能缩短字段引用,并在多表都有 id、name 等同名列时消除歧义。
下面建立客户、订单和订单明细。订单通过 customer_id 找客户,明细通过 order_id 找订单。
sql
DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS customers;
CREATE TABLE customers (
customer_id INTEGER PRIMARY KEY,
name TEXT NOT NULL
);
CREATE TABLE orders (
order_id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL,
status TEXT NOT NULL
);
CREATE TABLE order_items (
order_id INTEGER NOT NULL,
line_no INTEGER NOT NULL,
product TEXT NOT NULL,
qty INTEGER NOT NULL,
PRIMARY KEY (order_id, line_no)
);
INSERT INTO customers VALUES
(1, 'Alice'),
(2, 'Bob');
INSERT INTO orders VALUES
(101, 1, 'paid'),
(102, 1, 'pending'),
(103, 2, 'paid');
INSERT INTO order_items VALUES
(101, 1, 'Keyboard', 1),
(101, 2, 'Mouse', 2),
(103, 1, 'Monitor', 1);预期结果:语句全部执行成功,创建 3 张表并写入 8 行示例数据;建表与插入本身不会返回结果集。
text
(无输出)主查询把三张表按真实外键关系串起来,只保留已付款订单。
sql
SELECT
o.order_id,
c.name AS customer_name,
i.line_no,
i.product,
i.qty
FROM orders AS o
JOIN customers AS c
ON c.customer_id = o.customer_id
JOIN order_items AS i
ON i.order_id = o.order_id
WHERE o.status = 'paid'
ORDER BY o.order_id, i.line_no;预期结果:
text
order_id customer_name line_no product qty
-------- ------------- ------- -------- ---
101 Alice 1 Keyboard 1
101 Alice 2 Mouse 2
103 Bob 1 Monitor 1
复合连接条件要完整
当一行的身份由多列共同决定时,连接条件也必须覆盖这些列。例如分店商品表可能用 (store_id, product_id) 作为联合主键,此时只按 product_id 连接,会把不同分店的同名商品错误匹配。
片段示例,假设 sales 与 inventory 都包含 store_id 和 product_id:
sql
SELECT s.sale_id, i.stock
FROM sales AS s
JOIN inventory AS i
ON i.store_id = s.store_id
AND i.product_id = s.product_id;生效行为:每条销售记录只匹配同一分店、同一商品的库存行。验证时应检查联合键是否唯一,并确认结果行数没有因漏写条件而成倍增长。
重复行先判断来源
连接后出现重复,不代表必须加 DISTINCT。如果一张订单有两条明细,那么订单字段出现两次是正确的一对多展开。只有业务结果要求“每个订单一行”时,才应该改变查询粒度,例如聚合明细。
sql
SELECT
o.order_id,
c.name AS customer_name,
SUM(i.qty) AS total_qty
FROM orders AS o
JOIN customers AS c
ON c.customer_id = o.customer_id
JOIN order_items AS i
ON i.order_id = o.order_id
WHERE o.status = 'paid'
GROUP BY o.order_id, c.name
ORDER BY o.order_id;预期结果:
text
order_id customer_name total_qty
-------- ------------- ---------
101 Alice 3
103 Bob 1如果结果行数异常增大,优先检查是否缺少 ON、是否把复合键写成单列条件,以及被连接列是否本来就不唯一。DISTINCT 只能隐藏重复结果,不能修复错误的表关系。
按自己的节奏,确认真正理解。