MariaDB 教程 / 用 LEFT JOIN 保留没有匹配的记录

LEFT JOIN 保留左表记录,有匹配时填入右表的值,没有匹配时则让右侧结果列为 NULL。先确定必须保留的总体,再安排筛选条件,避免把没有符合条件订单的客户意外排除。

把需要保留的总体放在左侧

要查看所有注册客户及其订单,应从 customers 出发。右侧没有匹配时出现的 NULL 只是查询结果的表示,不会创建一条空订单,也不会把已保存的订单改成 NULL。若一位客户匹配多个订单,结果仍然会有多行。

问题 连接设计 含义
所有客户及其任意订单 customers LEFT JOIN orders 没有订单的客户也保留
所有订单及其客户 orders LEFT JOIN customers 保留的总体换成订单
没有已支付订单 在 ON 中限定 paid,再检查无匹配 包括只有取消订单的客户
完全没有订单 ON 不限制状态 只有不存在任何订单才符合

如果只需要匹配组合,可对照 INNER JOIN 教程。表之间为何使用这些键,可参阅 关系与外键设计。LEFT JOIN 的语法见 MariaDB 官方 JOIN 文档,但是否保留了正确的人群,需要结合业务问题与实际记录判断。

准备订单历史不同的客户样本

使用 course_user 连接学习服务器,先核对 SELECT VERSION(), DATABASE(); 与 SHOW TABLES;。本节独立使用 c27_customers、c27_products、c27_orders、c27_items 四表;如果已有同名对象,先查看表定义和数据,不要覆盖用途未知的表。

完整 SQL 在 MariaDB 客户端中执行。若保存文件,应使用 UTF-8,并按照 SOURCE 文件执行教程提供客户端电脑上的实际路径。四张表全部提供,不依赖上一节已经建好的对象。

USE shopcourse;
CREATE TABLE c27_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 c27_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 c27_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_c27_order_customer FOREIGN KEY (customer_id)
  REFERENCES c27_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c27_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_c27_item_order FOREIGN KEY (order_id)
  REFERENCES c27_orders(order_id) ON DELETE RESTRICT,
 CONSTRAINT fk_c27_item_product FOREIGN KEY (product_id)
  REFERENCES c27_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c27_customers VALUES
 (1,'김민수','서울'),(2,'이서연','부산'),
 (3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c27_products VALUES
 (1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
 (3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c27_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 c27_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 字面量不变。

客户 1 有两个已支付订单,客户 2 有一个已支付订单,客户 3 只有一个 canceled 订单,客户 4 完全没有订单。因此,“尚未购买”如果没有先定义状态含义,就可能指向两种不同人群。

观察被保留的客户与右侧 NULL

SELECT c.customer_id,c.customer_name,o.order_id,o.status
FROM c27_customers AS c LEFT JOIN c27_orders AS o
 ON o.customer_id=c.customer_id ORDER BY c.customer_id,o.order_id;

查询应返回五行:客户 1 两行,客户 2、3、4 各一行。只有客户 4 的 order_id 和 status 为 NULL,其客户编号与姓名仍然存在。结果不是恰好四行,因为它表示客户与订单组合,而不是已经聚合成每位客户一行。

客户 3 的取消订单仍是一个实际匹配,所以其订单列不是 NULL。检查“没有匹配”时,应依据连接条件,而不是把任何不想计入的状态都当成记录不存在。也可直接查询订单表,确认仍只有四条完整订单,LEFT JOIN 没有向其中插入合成记录。

比较 paid 条件放在 ON 与 WHERE 的差别

SELECT c.customer_id,COUNT(o.order_id) AS paid_orders
FROM c27_customers AS c LEFT JOIN c27_orders AS o
 ON o.customer_id=c.customer_id AND o.status='paid'
GROUP BY c.customer_id ORDER BY c.customer_id;
customer_id paid_orders
1 2
2 1
3 0
4 0

paid 条件属于 ON,所以右侧只匹配已支付订单,但左侧四位客户全部保留。客户 3 虽然有取消订单,却没有符合 ON 的 paid 记录,因此和客户 4 一样得到零次匹配。

再执行下面的对照查询,只把 paid 条件移到 WHERE,观察客户编号与数量,不要同时修改其他逻辑:

SELECT c.customer_id,COUNT(o.order_id) AS paid_orders
FROM c27_customers c LEFT JOIN c27_orders o
 ON o.customer_id=c.customer_id
WHERE o.status='paid'
GROUP BY c.customer_id ORDER BY c.customer_id;

这次只剩客户 1 的 2 和客户 2 的 1。WHERE 检查连接后的结果,NULL 状态不满足“等于 paid”,取消状态也不满足,所以客户 3、4 被排除。报表若要求显示所有客户及零次支付,应恢复前一条查询,把状态条件保留在 ON。

两条查询都能统计已支付订单,但它们的结果人群不同。不要只比较前两位客户的数字相同,就认为条件位置可以随意交换。把筛选移回 ON 后,应再次确认四个客户编号都出现。

统计右侧匹配键,并用它判断缺失

即使没有匹配订单,LEFT JOIN 仍为保留的客户生成一条结果行。因此在上面的 paid 查询中,COUNT(*) 会把客户 3、4 都计为 1。改成 COUNT(c.customer_id) 也不能解决,因为左侧编号始终存在。

应统计 COUNT(o.order_id):这个右侧主键在真实订单中不允许 NULL,没有匹配时才成为 NULL,因此能数出匹配订单。COUNT 与 NULL 教程解释了按行计数和按非 NULL 值计数的不同。

SELECT c.customer_id FROM c27_customers AS c
LEFT JOIN c27_orders AS o ON o.customer_id=c.customer_id
WHERE o.order_id IS NULL ORDER BY c.customer_id;

不限制订单状态时,这条无匹配查询只返回客户 4。若在 ON 里再加入 AND o.status='paid',检查的就变成“没有已支付订单”,结果为客户 3、4。只变动这个条件并比较两个人群,再恢复所需问题的定义。

缺失检查应使用右侧真实记录中不可为 NULL 的键。不要使用本身允许缺失的备注列,因为一条实际匹配订单也可能没有备注;那样会把“有订单但备注为空”误判成“没有订单”。

联合检查保留侧、条件位置与聚合单位

行数不符合预期时,先显示双方键和状态,核对三件事:左侧是不是要保留的总体,WHERE 是否排除了 NULL 扩展的行,以及一行到底代表客户还是客户—订单组合。不要先加 DISTINCT 隐藏重复现象,再猜测人数是否正确。

把 orders 放在左侧,会得到以订单为总体的查询;本样本四个订单都有有效客户,结果是四行,不会因此显示没有订单的客户 4。更换书写顺序不是表面调整,而是改变了要保留的起点。

所有查询都不需要改写源记录。重练或清理前,先检查四表定义和数据,并备份需要保留的内容。下面只删除已确认可丢弃的本节四表,先子表后父表;DROP TABLE 会移除定义和全部行,普通 ROLLBACK 无法恢复,应与事务实验分开。

DROP TABLE shopcourse.c27_items;
DROP TABLE shopcourse.c27_orders;
DROP TABLE shopcourse.c27_products;
DROP TABLE shopcourse.c27_customers;