数据库原理与选型 / 系统课时
LEFT JOIN 与缺失关联数据
LEFT JOIN 与缺失关联数据
有些查询不仅要看“已经有关联”的记录,还要保留暂时没有关联数据的主记录。LEFT JOIN 会完整保留左表,并在右表匹配失败时补出 NULL,因此很适合查询未下单用户、未分配负责人或可选配置。
学习目标
- 使用
LEFT JOIN保留左表中的全部记录。 - 正确理解右表匹配失败后出现的
NULL。 - 用
IS NULL找出缺失关联的数据,并避免错误过滤。
LEFT JOIN 保留左表
LEFT JOIN 先以左表为基准逐行查找右表匹配项。匹配成功时返回组合后的列;匹配失败时,左表列照常返回,右表列统一为 NULL。
这与 INNER JOIN 的关键差异是:INNER JOIN 会丢弃没有匹配项的左表行,而 LEFT JOIN 不会。因此,哪张表写在左侧会直接影响查询语义。
查询所有用户及其订单
下面延续“用户—订单”场景,并特意保留一个没有订单的用户:
CREATE TABLE users (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL
);
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
user_id INTEGER NOT NULL,
amount REAL NOT NULL
);
INSERT INTO users (id, name) VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Carol');
INSERT INTO orders (id, user_id, amount) VALUES
(101, 1, 59.9),
(102, 2, 42.5);
SELECT
u.name,
o.id AS order_id,
o.amount
FROM users AS u
LEFT JOIN orders AS o
ON u.id = o.user_id
ORDER BY u.id;预期输出:
Alice|101|59.9
Bob|102|42.5
Carol||在 SQLite 命令行默认输出中,NULL 常显示为空字段;不同客户端也可能直接显示 NULL。关键是 Carol 这一行仍然存在,而 order_id 与 amount 没有匹配值。
用 IS NULL 找出缺失关联
要查“没有订单的用户”,不要写 o.id = NULL,因为 SQL 中 NULL 表示未知值,不能用普通等号判断。应使用 IS NULL:
SELECT u.id, u.name
FROM users AS u
LEFT JOIN orders AS o
ON u.id = o.user_id
WHERE o.id IS NULL;预期输出:
3|Carol这里检查右表主键 o.id 很稳妥:它在真实订单中不会为 NULL,因此结果中的 NULL 明确表示“连接失败”。
小心 WHERE 把 LEFT JOIN 变成内连接
连接后如果在 WHERE 中直接要求右表列满足普通条件,例如 WHERE o.amount > 50,所有右表为 NULL 的行都会被过滤掉,效果会接近 INNER JOIN。
如果业务要求“保留所有用户,但只关联金额大于 50 的订单”,应把条件写进 ON:ON u.id = o.user_id AND o.amount > 50。这样条件只影响右表能否匹配,不会把左表用户删除。
掌握这个边界后,可以用一句规则判断写法:决定能否匹配的条件放在 ON,决定最终是否保留整行的条件放在 WHERE。