MariaDB 教程 / 用 INNER JOIN 查询相互关联的数据
INNER JOIN 返回连接条件为真的行组合。应在 ON 中明确关系键,并先确定结果的一行代表客户、订单还是明细,再解释一对多关系中重复出现的名称或编号。
定义连接条件与结果粒度
要在订单旁显示客户姓名,应把订单的 customer_id 与客户表主键匹配。两列恰好同名,不代表数据库自动知道正确的业务关系;连接条件必须写对。使用短别名,可以明确列属于哪张表,也能避免相同列名造成歧义。
| 组成部分 | 示例 | 作用 |
|---|---|---|
| 起始表 | orders AS o | 连接前一行代表一个订单 |
| 关联表 | customers AS c | 提供客户属性 |
| ON | c.customer_id=o.customer_id | 按关系键匹配所属客户 |
| WHERE | o.status='paid' | 只显示指定状态的订单 |
表关系与外键教程中的关系图说明了为什么要使用这些键。INNER JOIN 只保留匹配组合;如果查询要求连没有订单的客户也显示,就需要考虑保留未匹配记录的外连接,而不是把缺少客户误判成删除。
语法形式可对照 MariaDB 官方的 JOIN 语法文档。本节从最明确的 ON 等值条件开始,不依赖列名自动匹配。
准备完整且独立的关联数据
使用 course_user 连接学习服务器,先运行 SELECT VERSION(), DATABASE(); 与 SHOW TABLES; 核对目标。本节独立使用 c26_customers、c26_products、c26_orders、c26_items 四张表。若同名对象存在,应先检查定义和数据,不要覆盖用途未明的表。
下面代码在 MariaDB 客户端执行,也可保存为 UTF-8 文件,按照 SQL 文件与 SOURCE 教程用实际路径加载,例如 SOURCE C:/mariadb-lab/lesson.sql;。先创建并填充父表,再创建需要引用它们的记录。
USE shopcourse;
CREATE TABLE c26_customers (
customer_id INT NOT NULL PRIMARY KEY,
customer_name VARCHAR(50) NOT NULL,
city VARCHAR(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c26_products (
product_id INT NOT NULL PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,
category VARCHAR(30) NOT NULL,
price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
stock INT NOT NULL CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c26_orders (
order_id INT NOT NULL PRIMARY KEY,
customer_id INT NOT NULL,
ordered_at DATETIME NOT NULL,
status VARCHAR(20) NOT NULL,
shipping_fee DECIMAL(10,2) NOT NULL DEFAULT 1000,
CONSTRAINT fk_c26_order_customer FOREIGN KEY (customer_id)
REFERENCES c26_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c26_items (
order_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL CHECK (quantity > 0),
unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
PRIMARY KEY (order_id, product_id),
CONSTRAINT fk_c26_item_order FOREIGN KEY (order_id)
REFERENCES c26_orders(order_id) ON DELETE RESTRICT,
CONSTRAINT fk_c26_item_product FOREIGN KEY (product_id)
REFERENCES c26_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c26_customers VALUES
(1,'김민수','서울'),(2,'이서연','부산'),
(3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c26_products VALUES
(1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
(3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c26_orders
(order_id,customer_id,ordered_at,status,shipping_fee) VALUES
(101,1,'2026-01-10 10:00:00','paid',1000),
(102,1,'2026-01-20 11:00:00','paid',1000),
(103,2,'2026-02-05 12:00:00','paid',1000),
(104,3,'2026-02-10 13:00:00','canceled',1000);
INSERT INTO c26_items VALUES
(101,1,2,3500),(101,2,3,1500),(102,3,1,8000),
(103,1,1,3500),(103,3,2,8000),(104,2,1,1500);
姓名、城市与商品名保留原始韩文字符串,以编号核对结果。商品 1~4 分别表示笔记本、圆珠笔、马克杯与台灯;客户 1、3 的城市表示首尔,2 表示釜山,4 表示大田。请原样保存 UTF-8 字面量,避免翻译输入造成不一致。
这份初始化包含全部四张表,所以不依赖上一节已建的对象。主要查询使用客户与订单,商品和明细用于保持完整的订单样本。预期四表记录数依次为 4、4、4、6;清理时也只针对本节的 c26_ 表。
把每个订单连接到所属客户
SELECT o.order_id,c.customer_id,c.customer_name,o.status
FROM c26_orders AS o
INNER JOIN c26_customers AS c ON c.customer_id=o.customer_id
ORDER BY o.order_id;
| order_id | customer_id | status |
|---|---|---|
| 101 | 1 | paid |
| 102 | 1 | paid |
| 103 | 2 | paid |
| 104 | 3 | canceled |
实际查询还包含客户姓名。客户 1 的姓名出现两次,因为它分别属于订单 101、102,这不是同一个订单被重复插入。这里一行代表一个订单,四行只涉及三位不同客户,不能把结果行数直接当作客户人数。
客户表的 customer_id 唯一,因此每个订单的这个引用最多匹配一位客户。若改用非唯一的姓名连接,可能出现多条匹配,结果粒度也会改变。检查重复值时,应同时显示订单编号与客户编号,先判断是否符合关系的预期数量。
如果真正需要的是“至少有一个订单的客户列表”,应明确使用存在性判断或经过设计的分组结果。不要只因为客户名重复就加 DISTINCT,把本来需要保留的不同订单隐藏掉。
把关系匹配与业务筛选分别表达
SELECT o.order_id,c.customer_name,o.ordered_at
FROM c26_orders AS o
INNER JOIN c26_customers AS c ON c.customer_id=o.customer_id
WHERE o.status='paid' ORDER BY o.order_id;
结算状态为 paid 的订单编号是 101、102、103,canceled 的订单 104 被排除。ON 说明哪个客户属于这个订单,WHERE 说明要显示哪些订单。这样即使以后增加筛选条件,也能更清楚地检查关系是否仍然正确。
只把状态字面量从 paid 改为 canceled,应只看到订单 104;核对后恢复 paid,应重新得到前三个订单。除了编号,还应检查姓名与 ordered_at,确认显示的是正确的整条关联结果,而不是只碰巧得到相同数量。
对于这里的 INNER JOIN,把相应状态条件放入 ON 也可以得到相同结果,但不能因此推断所有外连接都能随意交换条件位置。关系条件与业务过滤的责任应保持清楚,尤其是在需要保留未匹配记录时。
与不限制关系的全部组合比较
SELECT COUNT(*) AS unconstrained_pairs
FROM c26_customers AS c CROSS JOIN c26_orders AS o;
四位客户与四个订单做不带限制条件的 CROSS JOIN,产生 4×4=16 个组合;按所属客户键连接则只有四行。CROSS JOIN 在确实需要全部组合时有用途,但若只是遗漏关联条件,就会把不相关的客户和订单配在一起。
连接条件还可能写成语法合法却含义错误的表达式。例如把 customer_id 与 order_id 比较,本样本客户编号是 1~4,订单编号是 101~104,结果将没有匹配。这不是表里没有数据,而是比较了两种不同标识。
本样本的外键与 NOT NULL 保证每个订单都有有效客户,因此以订单为起点时,四个订单都能通过 INNER JOIN。客户 4 没有订单,不出现在结果中,但其客户记录仍然保存在表里。其他设计若允许未匹配记录,INNER JOIN 会排除这些组合;应根据要保留的总体选择连接方式。
排查模糊列名与异常行数
如果出现 Column is ambiguous,检查是否同时存在同名列却没有指定所属表。这里 customers 和 orders 都有 customer_id,应写成 c.customer_id 或 o.customer_id。不要仅删掉某张表的必要列来隐藏问题,先明确表达式实际想引用哪个值。
结果过多时,检查连接键是否唯一、条件是否遗漏、关系是否本来就是一对多。结果过少时,检查状态等过滤条件、键值与未匹配记录。需要固定结果顺序时使用显式 ORDER BY,不能依赖存储位置或一次执行看到的顺序;相关规则见 排序与 LIMIT 教程。
这些查询不需要改写任何源记录。重练前先检查四表定义和数据,并备份应保留的内容。下面只清理已确认可丢弃的本节四表,先删子表再删父表;DROP TABLE 会删除定义和记录,普通 ROLLBACK 无法恢复,应放在事务实验之外。
DROP TABLE shopcourse.c26_items;
DROP TABLE shopcourse.c26_orders;
DROP TABLE shopcourse.c26_products;
DROP TABLE shopcourse.c26_customers;




