PostgreSQL / 系统课时

使用 LEFT JOIN

使用 LEFT JOIN

本课解决“即使右表没有数据,也要保留左表记录”的问题。你将通过客户与订单的完整查询,学会识别右表产生的 NULL,并避免筛选条件把左连接意外变成内连接。

学习目标

  • 使用 LEFT JOIN 保留左表全部记录
  • 正确处理右表缺失产生的 NULL
  • 避免在 WHERE 中错误过滤未匹配行

LEFT JOIN 的结果规则

LEFT JOIN 以左表的每一行作为保留起点。右表匹配成功时返回对应字段;匹配失败时仍输出该左表记录,右表字段统一为 NULL。

因此,左表应放置“必须完整保留”的数据。例如查询所有客户及其订单时,应把客户表放在左侧,即使某个客户从未下单,也能出现在结果中。

需要注意,保留左表全部记录不等于结果行数一定等于左表行数。如果一个客户匹配两笔订单,结果就会包含该客户的两行记录;只有未匹配时,才为该客户补出一行右表字段为 NULL 的记录。

LEFT JOIN 左表保留规则与未匹配行的形成过程

完整查询示例

下面创建两张临时表,插入三位客户和两笔订单。其中客户“王敏”没有订单,但仍会出现在查询结果中。将整个 SQL 脚本放在同一个 PostgreSQL 会话中执行即可。

sql
-- 使用临时表演示,不影响已有业务表
CREATE TEMP TABLE left_customer (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL
);

CREATE TEMP TABLE left_order (
    id INT PRIMARY KEY,
    customer_id INT NOT NULL REFERENCES left_customer(id),
    amount NUMERIC(10, 2) NOT NULL
);

INSERT INTO left_customer (id, name) VALUES
    (1, '李雷'),
    (2, '韩梅梅'),
    (3, '王敏');

INSERT INTO left_order (id, customer_id, amount) VALUES
    (201, 1, 88.00),
    (202, 2, 120.50);

-- 客户是必须保留的主体,放在左侧
SELECT
    c.name AS customer_name,
    o.id AS order_id,
    o.amount
FROM left_customer AS c
LEFT JOIN left_order AS o
    ON o.customer_id = c.id
ORDER BY c.id;

最后一条 SELECT 的预期输出:

text
 customer_name | order_id | amount
---------------+----------+--------
 李雷          |      201 |  88.00
 韩梅梅        |      202 | 120.50
 王敏          |          |
(3 rows)

结果按客户编号排列。李雷和韩梅梅分别匹配到一笔订单,王敏没有匹配记录,因此订单编号和金额均为 NULL。本例恰好返回三行,是因为每位客户最多只有一笔订单,而不是 LEFT JOIN 强制结果与左表行数相等。

最后一行的空白表示 PostgreSQL 返回的是 NULL,不是数字 0 或空字符串。展示时可以使用 COALESCE(o.amount, 0) 替换空值,但应先确认业务上是否允许把“没有订单”解释为金额 0。

由于 left_order.id 是非空主键,判断 o.id IS NULL 可以可靠识别没有匹配订单的客户。不要随意用右表中的可空业务字段判断是否匹配成功,因为真实存在的订单也可能包含空值。

筛选条件放在哪里

假设只想关联金额大于 100 的订单,同时仍保留所有客户,应把条件写进 ON。这个条件限制哪些订单能够成功匹配,而不改变客户必须保留的规则。

继续在同一数据库会话中执行:

sql
SELECT c.name, o.amount
FROM left_customer AS c
LEFT JOIN left_order AS o
    ON o.customer_id = c.id
   AND o.amount > 100 -- 只限制右表的匹配资格
ORDER BY c.id;

预期输出:

text
  name  | amount
--------+--------
 李雷   |
 韩梅梅 | 120.50
 王敏   |
(3 rows)

李雷虽然有一笔 88.00 的订单,但不满足 ON 中的金额条件,因此本次连接将其视为未匹配,并在金额列返回 NULL。韩梅梅的订单符合条件,王敏仍然没有订单,三个客户都得到保留。

如果把 o.amount > 100 从 ON 移到 WHERE,情况就不同了。数据库会先完成左连接,再对结果行进行筛选:李雷的 88.00 不满足条件,王敏的 NULL > 100 得到未知值,二者都会被过滤,最终只剩韩梅梅和 120.50。

这类针对右表字段的比较条件写在 WHERE 中,会过滤掉未匹配行,查询效果就接近内连接。但并非所有 WHERE 条件都会如此,例如 WHERE o.id IS NULL 恰好可以筛选没有订单的客户。

验证时,可以在第二条查询中仅将 AND o.amount > 100 移到 WHERE,再次执行并对比行数:放在 ON 中返回 3 行,放在 WHERE 中只返回 1 行。注意临时表只在创建它的数据库会话中有效,重新建立连接后需要重新运行建表和插入语句。

实际编写查询时,先明确哪些左表记录必须保留,再决定条件的位置:限制右表参与匹配的资格,通常写在 ON;需要对最终查询结果整体筛选时,才使用 WHERE,并检查它是否会排除右表为 NULL 的记录。

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

目录与阅读设置

阅读设置

17

记录一个想法

使用 LEFT JOIN

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

登录哪都通