数据库原理与选型 / 系统课时

LEFT JOIN 与缺失关联数据

LEFT JOIN 与缺失关联数据

有些查询不仅要看“已经有关联”的记录,还要保留暂时没有关联数据的主记录。LEFT JOIN 会完整保留左表,并在右表匹配失败时补出 NULL,因此很适合查询未下单用户、未分配负责人或可选配置。

学习目标

  • 使用 LEFT JOIN 保留左表中的全部记录。
  • 正确理解右表匹配失败后出现的 NULL。
  • 用 IS NULL 找出缺失关联的数据,并避免错误过滤。

LEFT JOIN 保留左表

LEFT JOIN 先以左表为基准逐行查找右表匹配项。匹配成功时返回组合后的列;匹配失败时,左表列照常返回,右表列统一为 NULL。

这与 INNER JOIN 的关键差异是:INNER JOIN 会丢弃没有匹配项的左表行,而 LEFT JOIN 不会。因此,哪张表写在左侧会直接影响查询语义。

查询所有用户及其订单

下面延续“用户—订单”场景,并特意保留一个没有订单的用户:

sql
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;

预期输出:

text
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:

sql
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;

预期输出:

text
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。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

LEFT JOIN 与缺失关联数据

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通