PostgreSQL中JOIN ON的语法详解与应用场景-牛站云

JOIN ON 是 PostgreSQL 多表查询中最常用的连接方式之一，它负责定义两张或多张表之间“按照什么条件进行匹配”。理解 JOIN 与 ON 的关系，不仅有助于写出正确的 SQL，也能避免因为连接条件位置不当导致的数据遗漏、重复甚至查询性能问题。

一、JOIN ON的基本语法

PostgreSQL 中最常见的 JOIN ON 语法如下：

SQL
SELECT 列名
FROM 表1
JOIN 表2
ON 表1.关联字段 = 表2.关联字段;

例如有两张表：

SQL
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50),
    department_id INT
);

CREATE TABLE departments (
    id SERIAL PRIMARY KEY,
    department_name VARCHAR(100)
);

查询用户及其所属部门：

SQL
SELECT
    u.username,
    d.department_name
FROM users u
JOIN departments d
ON u.department_id = d.id;

这里的 ON u.department_id = d.id 就是连接条件。

PostgreSQL 会根据这个条件寻找两张表中能够匹配的记录。默认情况下，JOIN 等价于 INNER JOIN，因此只有连接条件成立的数据才会出现在结果集中。

二、JOIN和ON分别负责什么

理解 JOIN ON，可以把它拆成两个部分。

JOIN 表示需要把另一张表加入当前查询。

SQL
FROM users u
JOIN departments d

这部分告诉 PostgreSQL：“我要把 users 和 departments 连接起来。”

ON 则负责描述两张表如何匹配：

SQL
ON u.department_id = d.id

也就是说，JOIN 决定“连接谁”，ON 决定“怎么连接”。

如果连接条件错误，例如：

SQL
ON u.id = d.id

虽然 SQL 语法完全正确，但业务含义可能已经发生变化，最终得到的数据也可能完全不是预期结果。

三、INNER JOIN ON：只查询匹配数据

INNER JOIN 是最常见的连接类型。

SQL
SELECT
    u.id,
    u.username,
    d.department_name
FROM users u
INNER JOIN departments d
ON u.department_id = d.id;

假设 users 中存在以下数据：

id	username	department_id
1	张三	10
2	李四	20
3	王五	30

departments 数据：

id	department_name
10	技术部
20	市场部

执行连接后，王五因为没有对应的部门记录，不会出现在结果中。

因此：


INNER JOIN = 只保留连接条件能够匹配的数据

这种方式适合查询“必须存在关联关系”的业务数据，例如订单必须关联客户、员工必须关联部门、商品必须关联分类等。

四、LEFT JOIN ON：保留左表全部数据

如果希望即使没有匹配记录，也保留左表数据，可以使用 LEFT JOIN：

SQL
SELECT
    u.username,
    d.department_name
FROM users u
LEFT JOIN departments d
ON u.department_id = d.id;

此时 users 是左表。

即使某个用户没有对应部门，该用户仍然会被返回：


张三    技术部
李四    市场部
王五    NULL

LEFT JOIN 特别适合“查询主体必须全部展示”的场景。

例如查询所有用户以及用户的订单：

SQL
SELECT
    u.id,
    u.username,
    o.id AS order_id
FROM users u
LEFT JOIN orders o
ON u.id = o.user_id;

即使某个用户从未下过订单，该用户仍然会出现在结果中，只是 order_id 为 NULL。

五、RIGHT JOIN ON：保留右表全部数据

RIGHT JOIN 与 LEFT JOIN 的逻辑相反：

SQL
SELECT
    u.username,
    d.department_name
FROM users u
RIGHT JOIN departments d
ON u.department_id = d.id;

这里 departments 是右表，因此所有部门都会被保留。

不过在实际开发中，RIGHT JOIN 使用频率通常低于 LEFT JOIN。很多情况下，将两张表的顺序调整后改写成 LEFT JOIN，可读性会更好。

例如：

SQL
SELECT
    u.username,
    d.department_name
FROM departments d
LEFT JOIN users u
ON u.department_id = d.id;

两种写法表达的是相同方向的关联关系。

六、FULL OUTER JOIN ON：保留两边所有数据

FULL OUTER JOIN 会保留左右两张表中的全部记录。

SQL
SELECT
    u.username,
    d.department_name
FROM users u
FULL OUTER JOIN departments d
ON u.department_id = d.id;

如果左表没有匹配项，右表字段为 NULL；如果右表没有匹配项，左表字段为 NULL。

这种连接方式适合数据核对、数据同步以及差异分析。

例如比较两个系统中的用户数据：

SQL
SELECT
    a.user_id AS system_a_id,
    b.user_id AS system_b_id
FROM system_a_users a
FULL OUTER JOIN system_b_users b
ON a.user_id = b.user_id;

通过判断哪一侧字段为空，可以找出只存在于某个系统中的数据。

七、JOIN ON支持多个连接条件

ON 后面并不只能写一个条件，可以使用 AND、OR 等逻辑运算符。

例如订单表和订单明细表除了要求订单 ID 一致，还要求数据状态符合条件：

SQL
SELECT
    o.id,
    oi.product_id,
    oi.quantity
FROM orders o
JOIN order_items oi
ON o.id = oi.order_id
AND oi.status = 'valid';

这里：

SQL
ON o.id = oi.order_id
AND oi.status = 'valid'

意味着两个条件必须同时满足。

也可以连接多个字段：

SQL
SELECT *
FROM table_a a
JOIN table_b b
ON a.company_id = b.company_id
AND a.user_id = b.user_id;

这种写法适合联合主键、组合业务键等场景。

八、ON条件与WHERE条件有什么区别

这是使用 JOIN ON 时最容易遇到的问题之一。

考虑下面的 SQL：

SQL
SELECT
    u.username,
    o.id AS order_id
FROM users u
LEFT JOIN orders o
ON u.id = o.user_id
WHERE o.status = 'paid';

虽然使用的是 LEFT JOIN，但 WHERE o.status = 'paid' 会过滤掉没有订单的用户，因为这些用户对应的 o.status 是 NULL。

因此，最终效果很接近：

SQL
INNER JOIN

如果目标是保留所有用户，只关联已支付订单，应该把条件放到 ON 中：

SQL
SELECT
    u.username,
    o.id AS order_id
FROM users u
LEFT JOIN orders o
ON u.id = o.user_id
AND o.status = 'paid';

这两个查询的语义明显不同。

条件放在ON中

SQL
LEFT JOIN orders o
ON u.id = o.user_id
AND o.status = 'paid'

含义是：

用户全部保留，只寻找满足条件的订单。

条件放在WHERE中

SQL
LEFT JOIN orders o
ON u.id = o.user_id
WHERE o.status = 'paid'

含义是：

先进行左连接，再把没有支付订单的结果过滤掉。

掌握这一点对于正确使用 LEFT JOIN 非常重要。

九、JOIN ON与表别名结合使用

多表查询中建议给表设置简洁明确的别名：

SQL
SELECT
    u.username,
    d.department_name,
    o.amount
FROM users u
JOIN departments d
ON u.department_id = d.id
LEFT JOIN orders o
ON u.id = o.user_id;

相比重复书写完整表名：

SQL
users.username
departments.department_name
orders.amount

使用：


u.username
d.department_name
o.amount

可以明显提高 SQL 的可读性。

尤其是表名较长或者存在多个自关联时，表别名几乎是必需的。

十、JOIN ON连接三张及以上的表

PostgreSQL 支持连续使用多个 JOIN：

SQL
SELECT
    u.username,
    d.department_name,
    o.id AS order_id,
    o.amount
FROM users u
JOIN departments d
ON u.department_id = d.id
LEFT JOIN orders o
ON u.id = o.user_id;

这里存在两次连接：


users → departments
users → orders

每次 JOIN 都可以拥有独立的 ON 条件。

还可以继续连接其他表：

SQL
SELECT
    u.username,
    d.department_name,
    o.id AS order_id,
    p.product_name
FROM users u
JOIN departments d
ON u.department_id = d.id
JOIN orders o
ON u.id = o.user_id
JOIN products p
ON o.product_id = p.id;

实际开发中，复杂查询通常就是由多个简单的 JOIN ON 组合而成。

十一、JOIN ON中的条件不一定是等值条件

很多示例都是：

SQL
ON a.id = b.id

但 PostgreSQL 的 ON 条件并不要求必须使用等号。

例如范围连接：

SQL
SELECT
    o.id,
    o.amount,
    l.level_name
FROM orders o
JOIN discount_levels l
ON o.amount >= l.min_amount
AND o.amount < l.max_amount;

这种写法可以根据订单金额匹配不同的折扣区间。

也可以使用其他比较运算符：

SQL
ON a.start_time < b.end_time
AND a.end_time > b.start_time

例如检测两个时间区间是否存在交集。

十二、自连接中的JOIN ON

同一张表也可以进行连接，这称为自连接。

假设 employees 表中保存员工和上级关系：


id
name
manager_id

查询员工及其直属主管：

SQL
SELECT
    e.name AS employee_name,
    m.name AS manager_name
FROM employees e
LEFT JOIN employees m
ON e.manager_id = m.id;

这里实际上是同一张表出现了两次：


employees e
employees m

其中 e 代表员工，m 代表主管。

自连接特别适合组织架构、分类树、评论回复、上下级关系等数据模型。

十三、多表JOIN中如何避免字段歧义

当多个表拥有相同字段名时，直接写字段可能产生歧义。

例如：

SQL
SELECT id
FROM users u
JOIN departments d
ON u.department_id = d.id;

由于 users 和 departments 都存在 id，PostgreSQL 可能报错：


column reference "id" is ambiguous

正确写法是明确指定表别名：

SQL
SELECT
    u.id AS user_id,
    d.id AS department_id
FROM users u
JOIN departments d
ON u.department_id = d.id;

不仅可以解决歧义，还能让查询结果的字段含义更加明确。

十四、JOIN ON导致结果重复的原因

JOIN 查询中一个非常常见的问题是“数据莫名其妙变多”。

例如 users 表有 1 条用户记录，而 orders 表中该用户存在 5 条订单：

SQL
SELECT
    u.username,
    o.id
FROM users u
JOIN orders o
ON u.id = o.user_id;

结果自然会得到 5 行。

这不是 PostgreSQL 重复返回数据，而是连接关系本身就是一对多。

如果进一步连接 order_items：


用户 1
  └── 订单 5
        └── 明细若干

最终结果行数还可能继续增长。

因此，遇到 JOIN 后数据数量异常增加时，应重点检查：

两张表之间是一对一还是一对多。
ON 条件是否完整。
是否遗漏了组合关联字段。
是否真的需要连接明细表。
是否应该使用 DISTINCT 或聚合。

不要一看到重复就直接使用：

SQL
SELECT DISTINCT ...

DISTINCT 有时只是隐藏了错误的连接逻辑。

十五、JOIN ON的性能优化方法

JOIN ON 的正确性是第一位，但数据量较大时，连接性能同样值得关注。

1. 为关联字段建立合适的索引

例如：

SQL
CREATE INDEX idx_users_department_id
ON users(department_id);

CREATE INDEX idx_orders_user_id
ON orders(user_id);

索引是否真正有效，需要结合数据分布和执行计划判断。

2. 使用EXPLAIN分析执行计划

可以使用：

SQL
EXPLAIN
SELECT
    u.username,
    d.department_name
FROM users u
JOIN departments d
ON u.department_id = d.id;

需要更详细的运行信息时：

SQL
EXPLAIN ANALYZE
SELECT
    u.username,
    d.department_name
FROM users u
JOIN departments d
ON u.department_id = d.id;

重点关注扫描方式、连接方式、估算行数与实际行数等信息。

PostgreSQL 可能选择 Nested Loop、Hash Join 或 Merge Join 等不同的连接算法。具体采用哪一种，应由优化器根据统计信息、数据规模和成本模型决定。

3. 避免无意义的大表连接

如果查询只需要少量字段，不建议简单使用：

SQL
SELECT *

而应该明确指定所需字段：

SQL
SELECT
    u.id,
    u.username,
    d.department_name

这样不仅提高可读性，也有助于减少不必要的数据处理。

4. 保持统计信息及时更新

数据发生大量变化后，可以通过：

SQL
ANALYZE users;
ANALYZE orders;

更新统计信息，帮助 PostgreSQL 更准确地估算数据分布，从而选择更合理的执行计划。

十六、JOIN ON的常见错误

错误一：忘记写ON条件

例如：

SQL
SELECT *
FROM users
JOIN departments;

普通 JOIN 需要连接条件。若业务确实需要产生笛卡尔积，应明确使用：

SQL
CROSS JOIN

避免让代码意图变得模糊。

错误二：关联字段写错

例如本来应该：

SQL
ON u.department_id = d.id

却写成：

SQL
ON u.id = d.id

这种问题通常不会触发 SQL 语法错误，却可能产生严重的数据逻辑错误，因此测试结果时不能只看 SQL 是否执行成功。

错误三：LEFT JOIN后在WHERE中错误过滤右表

例如：

SQL
FROM users u
LEFT JOIN orders o
ON u.id = o.user_id
WHERE o.amount > 100;

如果需要保留没有订单的用户，这种写法就不符合要求。

可以考虑：

SQL
FROM users u
LEFT JOIN orders o
ON u.id = o.user_id
AND o.amount > 100;

具体应该使用哪种方式，取决于业务语义。

十七、JOIN ON与USING的区别

当两张表使用相同名称的关联字段时，PostgreSQL 还支持 USING：

SQL
SELECT *
FROM users
JOIN departments
USING (department_id);

它与：

SQL
SELECT *
FROM users u
JOIN departments d
ON u.department_id = d.department_id;

表达的核心连接逻辑类似。

不过 USING 要求连接字段在两张表中具有相同名称，因此适用范围不如 ON 灵活。

如果关联条件比较复杂，例如：

SQL
ON u.department_id = d.id
AND u.company_id = d.company_id

就应该继续使用 ON。

十八、JOIN ON的典型应用场景

实际项目中，JOIN ON 的应用非常广泛。

用户与部门

SQL
SELECT u.username, d.department_name
FROM users u
JOIN departments d
ON u.department_id = d.id;

订单与用户

SQL
SELECT o.id, u.username, o.amount
FROM orders o
JOIN users u
ON o.user_id = u.id;

商品与分类

SQL
SELECT p.product_name, c.category_name
FROM products p
JOIN categories c
ON p.category_id = c.id;

查询没有订单的用户

SQL
SELECT
    u.id,
    u.username
FROM users u
LEFT JOIN orders o
ON u.id = o.user_id
WHERE o.id IS NULL;

这种写法是典型的“反连接”查询，可以用于查找不存在关联数据的记录。

查询每个用户的订单数量

SQL
SELECT
    u.id,
    u.username,
    COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o
ON u.id = o.user_id
GROUP BY u.id, u.username;

使用 LEFT JOIN 可以保证没有订单的用户也会被统计出来，其订单数量为 0。

十九、如何选择合适的JOIN类型

可以根据业务需求快速判断：

JOIN类型	结果特点	常见场景
INNER JOIN	只保留两边匹配数据	查询存在关联关系的数据
LEFT JOIN	保留左表全部数据	查询主体及其关联信息
RIGHT JOIN	保留右表全部数据	特定方向的数据保留
FULL OUTER JOIN	两边数据全部保留	数据差异比对
CROSS JOIN	产生笛卡尔积	组合所有可能的数据

其中，实际业务开发中最常用的通常是 INNER JOIN 和 LEFT JOIN。

二十、JOIN ON的实战建议

编写 PostgreSQL 多表查询时，可以遵循几个简单原则。

第一，先确定业务关系，再编写 ON 条件。不要为了让 SQL 执行成功而随意选择关联字段。

第二，明确一对一、一对多、多对多关系。理解数据基数后，才能正确判断 JOIN 后的结果数量。

第三，区分 ON 与 WHERE 的职责。尤其使用 LEFT JOIN 时，要特别注意过滤条件的位置。

第四，多表查询统一使用表别名，并为同名字段明确添加前缀。

第五，发现 JOIN 后数据量异常增加时，优先检查连接关系，而不是立即使用 DISTINCT。

第六，对于大数据量查询，应结合索引、统计信息和 EXPLAIN ANALYZE 分析实际执行计划，而不是仅凭 SQL 结构判断性能。

JOIN ON 看似只是 SQL 中的一小部分语法，实际上承担着多表数据关联的核心职责。掌握 INNER JOIN、LEFT JOIN、FULL OUTER JOIN 等不同连接方式，并正确理解 ON 与 WHERE 的区别，就能更准确地构建 PostgreSQL 多表查询。对于复杂业务，还需要进一步结合表之间的基数关系、索引设计以及执行计划进行优化，才能兼顾查询结果的准确性和数据库性能。

PostgreSQL中JOIN ON的语法详解与应用场景

一、JOIN ON的基本语法

二、JOIN和ON分别负责什么

三、INNER JOIN ON：只查询匹配数据

四、LEFT JOIN ON：保留左表全部数据

五、RIGHT JOIN ON：保留右表全部数据

六、FULL OUTER JOIN ON：保留两边所有数据

七、JOIN ON支持多个连接条件

八、ON条件与WHERE条件有什么区别

条件放在ON中

条件放在WHERE中

九、JOIN ON与表别名结合使用

十、JOIN ON连接三张及以上的表

十一、JOIN ON中的条件不一定是等值条件

十二、自连接中的JOIN ON

十三、多表JOIN中如何避免字段歧义

十四、JOIN ON导致结果重复的原因

十五、JOIN ON的性能优化方法

1. 为关联字段建立合适的索引

2. 使用EXPLAIN分析执行计划

3. 避免无意义的大表连接

4. 保持统计信息及时更新

十六、JOIN ON的常见错误

错误一：忘记写ON条件

错误二：关联字段写错

错误三：LEFT JOIN后在WHERE中错误过滤右表

十七、JOIN ON与USING的区别

十八、JOIN ON的典型应用场景

用户与部门

订单与用户

商品与分类

查询没有订单的用户

查询每个用户的订单数量

十九、如何选择合适的JOIN类型

二十、JOIN ON的实战建议

推荐模板

推荐服务

PostgreSQL中JOIN ON的语法详解与应用场景

一、JOIN ON的基本语法

二、JOIN和ON分别负责什么

三、INNER JOIN ON：只查询匹配数据

四、LEFT JOIN ON：保留左表全部数据

五、RIGHT JOIN ON：保留右表全部数据

六、FULL OUTER JOIN ON：保留两边所有数据

七、JOIN ON支持多个连接条件

八、ON条件与WHERE条件有什么区别

条件放在ON中

条件放在WHERE中

九、JOIN ON与表别名结合使用

十、JOIN ON连接三张及以上的表

十一、JOIN ON中的条件不一定是等值条件

十二、自连接中的JOIN ON

十三、多表JOIN中如何避免字段歧义

十四、JOIN ON导致结果重复的原因

十五、JOIN ON的性能优化方法

1. 为关联字段建立合适的索引

2. 使用EXPLAIN分析执行计划

3. 避免无意义的大表连接

4. 保持统计信息及时更新

十六、JOIN ON的常见错误

错误一：忘记写ON条件

错误二：关联字段写错

错误三：LEFT JOIN后在WHERE中错误过滤右表

十七、JOIN ON与USING的区别

十八、JOIN ON的典型应用场景

用户与部门

订单与用户

商品与分类

查询没有订单的用户

查询每个用户的订单数量

十九、如何选择合适的JOIN类型

二十、JOIN ON的实战建议

相关文章

推荐阅读

推荐模板

推荐服务