PHP小码农

Appearance

自然连接与 USING ​

本节讲解 NATURAL JOIN 的自动列匹配语义、USING 子句的列合并特性，以及为什么生产代码应一律使用显式 ON。学完本节，你将能解释自然连接的隐式契约风险，并在维护老代码时正确评估其隐患。

前置知识

阅读本节前，建议先了解：交叉连接（CROSS JOIN）

基础概念 ​

自然连接（Natural Join） 是 INNER JOIN 的语法糖：它自动找出两表中同名的所有列，并用这些列做等值连接。USING 子句则是"手动指定连接列名"的版本，适用于连接列同名但不想连接所有同名列的场景。

概念术语说明
自然连接NATURAL JOIN自动按全部同名列做等值连接
隐式契约Implicit Contract依赖列名相同，结构变更会静默改变语义
列合并Column Coalescing连接列在输出中只保留一份
限定名Qualified Name表名.列名 形式的引用
哑列Dummy Column无关但同名的列，会破坏自然连接

三种连接语法的对比：

语法连接列来源输出列显式程度推荐度
A JOIN B ON A.id = B.aid手写表达式两侧列都保留最高✅ 强烈推荐
A JOIN B USING (id)指定的同名列连接列合并为一列中⚠️ 有限使用
A NATURAL JOIN B全部同名列连接列合并为一列最低❌ 不推荐

关键认识：NATURAL JOIN 的连接条件由表结构决定，而不是由 SQL 语句决定。一旦表加了新的同名列，连接行为就会静默改变——这就是它最危险的地方。

语法与用法 ​

三种写法：

text
-- 自然连接：自动匹配所有同名列
SELECT 列列表 FROM 左表 NATURAL JOIN 右表;

-- USING：显式指定连接列
SELECT 列列表 FROM 左表 JOIN 右表 USING (列1 [, 列2]);

-- ON：完全显式
SELECT 列列表 FROM 左表 JOIN 右表 ON 左表.列 = 右表.列;

准备本节贯穿使用的示例表：

sql
-- 用户表
CREATE TABLE users (
  id     INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name   TEXT NOT NULL,
  city   TEXT NOT NULL,
  status TEXT NOT NULL           -- 注意：与 orders 表有同名列
);

-- 订单表：同样有 id 与 status 列（这是自然连接的事故源头）
CREATE TABLE orders (
  id       INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id  INTEGER NOT NULL,
  amount   NUMERIC(10, 2) NOT NULL,
  status   TEXT NOT NULL,        -- 与 users.status 同名但含义不同！
  order_no TEXT NOT NULL UNIQUE
);

INSERT INTO users (name, city, status) VALUES
  ('alice', 'beijing',  'active'),
  ('bob',   'shanghai', 'active'),
  ('carol', 'shenzhen', 'frozen');

INSERT INTO orders (user_id, amount, status, order_no) VALUES
  (1, 607.00, 'paid',    'SO-1001'),
  (1, 518.00, 'pending', 'SO-1003'),
  (2, 199.00, 'paid',    'SO-1002'),
  (3, 195.00, 'pending', 'SO-1004');

三种写法的对照：

sql
-- 写法一：NATURAL JOIN —— 会自动按 id 和 status 两列连接！
SELECT *
FROM users AS u
NATURAL JOIN orders AS o;
-- 等价于 ON u.id = o.id AND u.status = o.status （几乎永远不是你想要的结果）

-- 写法二：USING —— 指定按 id 连接（仍然是错的，因为 id 不是连接键）
SELECT *
FROM users AS u
JOIN orders AS o USING (id);

-- 写法三：ON —— 显式、正确
SELECT u.name, o.order_no, o.amount
FROM users AS u
JOIN orders AS o ON o.user_id = u.id;
text
 name  | order_no | amount
-------+----------+--------
 alice | SO-1001  | 607.00
 alice | SO-1003  | 518.00
 bob   | SO-1002  | 199.00
 carol | SO-1004  | 195.00
(4 rows)

NATURAL JOIN 在这张表上返回 0 行，因为它同时按 id 与 status 连接：只有"订单 id 等于用户 id 且订单状态等于用户状态"的行才匹配，而这几乎不可能存在。

详细说明 ​

NATURAL JOIN 的隐式匹配规则 ​

NATURAL JOIN 的匹配规则是：取两表所有同名列，全部用等值条件连接。等价改写如下：

sql
-- NATURAL JOIN 的等价展开（假设同名列有 id 和 status）
SELECT *
FROM users AS u
NATURAL JOIN orders AS o;

-- 完全等价的显式写法
SELECT u.id, u.name, u.city, u.status, o.user_id, o.amount, o.order_no
FROM users AS u
JOIN orders AS o ON o.id = u.id AND o.status = u.status;

如果两表没有任何同名列，NATURAL JOIN 就退化为 CROSS JOIN：

sql
-- 两张没有任何同名列的表做 NATURAL JOIN = 笛卡尔积
SELECT count(*)
FROM (SELECT 1 AS a, 2 AS b) AS t1
NATURAL JOIN (SELECT 3 AS c, 4 AS d) AS t2;
text
 count
-------
     1
sql
-- 验证：与 CROSS JOIN 结果一致
SELECT count(*)
FROM (SELECT 1 AS a, 2 AS b) AS t1
CROSS JOIN (SELECT 3 AS c, 4 AS d) AS t2;

为什么强烈不推荐 NATURAL JOIN ​

自然连接的问题不是"语法不好看"，而是它在结构演进时是静默失败的。

风险说明后果
隐式契约连接条件由列名决定代码审查看不出连接键
结构脆弱新增同名列即改变连接条件SQL 不改，结果变了
同名不同义status、type、created_at 等通用列名遍地都是连接到完全无关的列
调试困难报错/结果异常时难定位需要 \d 逐表核对列
跨库移植其他数据库行为有差异迁移时行为改变
sql
-- 场景演示：为表新增一个同名列，原有的 NATURAL JOIN 静默改变语义

-- 初始状态：orders 只有 user_id，两表没有同名列
-- SELECT * FROM users NATURAL JOIN orders;   -- 此时是笛卡尔积（也是错的）

-- 5 个月后，需求变更，为 orders 加了 created_at 列
ALTER TABLE orders ADD COLUMN created_at DATE NOT NULL DEFAULT CURRENT_DATE;

-- 同时 users 表也加了 created_at 列用于记录注册时间
ALTER TABLE users ADD COLUMN created_at DATE NOT NULL DEFAULT CURRENT_DATE;

-- 现在 NATURAL JOIN 开始按 created_at 连接 —— SQL 一个字都没改，结果却变了

这种"代码不变、结果改变"的行为是最危险的一类缺陷，因为它不会报错，只会在某天被用户发现数据不对。

USING 语法与输出列合并 ​

USING (col) 是"显式指定连接列名"的写法，比 NATURAL JOIN 安全得多，但仍有两个特性需要留意。

特性一：输出列合并。 同名的连接列在结果中只输出一次，且列的顺序会调整到最前面。

sql
-- USING 的输出：id 列只出现一次，且排在最前
SELECT *
FROM (SELECT 1 AS id, 'a' AS x, 'p' AS k) AS t1
JOIN (SELECT 1 AS id, 'b' AS y, 'q' AS k) AS t2 USING (id);
text
 id | x | k | y | k
----+---+---+---+---

（实际输出为 id, x, k, y, k，第二列的 k 会被重命名以避免冲突）

sql
-- 更清晰的验证：显式列出要看的列
SELECT id, t1.x, t2.y
FROM (SELECT 1 AS id, 'a' AS x) AS t1
JOIN (SELECT 1 AS id, 'b' AS y) AS t2 USING (id);
text
 id | x | y
----+---+---
  1 | a | b
(1 row)

特性二：合并后的列不能用限定名引用。 这是 USING 最容易踩的坑。

sql
-- 正确：合并后的 id 只能用裸列名
SELECT id, t1.x, t2.y
FROM (SELECT 1 AS id, 'a' AS x) AS t1
JOIN (SELECT 1 AS id, 'b' AS y) AS t2 USING (id);

-- 错误！合并后的列不能加表限定
-- SELECT t1.id, t2.id FROM t1 JOIN t2 USING (id);
-- 报错：column reference "id" is ambiguous

特性三：别名会改变合并行为。 如果对表做别名，USING 引用的是别名下的列：

sql
-- 别名与 USING 配合：引用的是原始列名，不是别名
SELECT id, a.x, b.y
FROM (SELECT 1 AS id, 'a' AS x) AS a
JOIN (SELECT 1 AS id, 'b' AS y) AS b USING (id);

-- 若 USING 的列在子查询中被改了名，就找不到同名列
-- 错误！子查询里 id 被改名成 key，USING (id) 找不到
-- SELECT * FROM (SELECT 1 AS key) AS a JOIN (SELECT 1 AS id) AS b USING (id);

USING 与 ON 的差异对照 ​

对比项USING (col)ON t1.col = t2.col
连接列个数只能是同名列，可以多列任意表达式
非等值连接不支持支持（>, <, <> 等）
输出列合并为一列，排在最前两侧列都保留
列引用合并列不能加限定名必须加限定名区分
JOIN ... USING + SELECT *无重复列有重复列
NATURAL 的组合NATURAL 可视为自动 USING—
可读性短，但隐含"同名"前提长，但完全自解释
结构演进风险中（指定了列名，但列名可能被改）无
sql
-- USING 支持多列
SELECT id, a.x, b.y
FROM (SELECT 1 AS id, 2 AS seq, 'a' AS x) AS a
JOIN (SELECT 1 AS id, 2 AS seq, 'b' AS y) AS b USING (id, seq);

-- ON 可以做非等值连接，USING 不行
SELECT o1.order_no, o2.order_no
FROM orders AS o1
JOIN orders AS o2 ON o2.amount > o1.amount
ORDER BY o1.order_no, o2.order_no;

JOIN ... USING 的列引用规则 ​

USING 合并后的列在 SELECT、WHERE、ORDER BY、GROUP BY 中都可以直接用裸列名：

sql
-- 合并列可以作为过滤条件与排序键
SELECT id, t1.x, t2.y
FROM (SELECT 1 AS id, 'a' AS x) AS t1
JOIN (SELECT 1 AS id, 'b' AS y) AS t2 USING (id)
WHERE id = 1
ORDER BY id;

-- 也可以用在 GROUP BY 中
SELECT id, count(*)
FROM (SELECT 1 AS id, 'a' AS x) AS t1
JOIN (SELECT 1 AS id, 'b' AS y) AS t2 USING (id)
GROUP BY id;

但要注意：如果两表还有其他同名列，SELECT * 仍会输出两份，此时 USING 只合并了指定的那一列。

sql
-- USING 只合并指定列；未在 USING 中指定的同名列不会合并
SELECT *
FROM (SELECT 1 AS id, 'a' AS status, 'x' AS p) AS t1
JOIN (SELECT 1 AS id, 'b' AS status, 'y' AS q) AS t2 USING (id);
-- 输出中 status 出现两次（来自两表），需要显式改名或加限定

实战示例 ​

对比 NATURAL JOIN、USING、ON 三种写法在同一查询上的表现。

sql
-- 先看清两张表的列结构（这一步是使用 NATURAL JOIN 前必须做的）
SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name IN ('users', 'orders')
  AND table_schema = 'public'
ORDER BY table_name, ordinal_position;
text
 table_name | column_name | data_type
------------+-------------+-----------------------
 orders     | id          | integer
 orders     | user_id     | integer
 orders     | amount      | numeric
 orders     | status      | text
 orders     | order_no    | text
 users      | id          | integer
 users      | name        | text
 users      | city        | text
 users      | status      | text
(9 rows)
sql
-- 写法一：NATURAL JOIN —— 按 (id, status) 连接，结果错误
SELECT u.name, o.order_no, o.amount, o.status
FROM users AS u
NATURAL JOIN orders AS o;
text
 name | order_no | amount | status
------+----------+--------+--------
(0 rows)
sql
-- 写法二：USING (id) —— 仍是错的，id 不是连接键
SELECT u.name, o.order_no, o.amount
FROM users AS u
JOIN orders AS o USING (id);
text
 name | order_no | amount
------+----------+--------
(0 rows)
sql
-- 写法三：ON 显式 —— 正确
SELECT u.name, o.order_no, o.amount, o.status AS order_status, u.status AS user_status
FROM users AS u
JOIN orders AS o ON o.user_id = u.id
ORDER BY o.order_no;
text
 name  | order_no | amount | order_status | user_status
-------+----------+--------+--------------+-------------
 alice | SO-1001  | 607.00 | paid         | active
 bob   | SO-1002  | 199.00 | paid         | active
 alice | SO-1003  | 518.00 | pending      | active
 carol | SO-1004  | 195.00 | pending      | frozen
(4 rows)
sql
-- 实战：为 legacy 代码审查 NATURAL JOIN 的隐患
-- 找出库中所有使用 NATURAL JOIN 的视图定义
SELECT schemaname, viewname, definition
FROM pg_views
WHERE definition ILIKE '%natural join%';
sql
-- 更安全的替代：显式 ON + 明确别名，语义自解释、不怕结构演进
CREATE VIEW v_user_orders AS
SELECT
  u.id          AS user_id,
  u.name        AS user_name,
  u.status      AS user_status,
  o.id          AS order_id,
  o.order_no,
  o.amount,
  o.status      AS order_status
FROM users AS u
JOIN orders AS o ON o.user_id = u.id;

注意事项 ​

  • 不要在生产代码中使用 NATURAL JOIN。 它让连接条件隐式依赖表结构，结构变更会导致结果静默变化。
sql
-- 不推荐
SELECT u.name, o.order_no FROM users AS u NATURAL JOIN orders AS o;

-- 推荐
SELECT u.name, o.order_no FROM users AS u JOIN orders AS o ON o.user_id = u.id;
  • USING 合并后的列不能加表限定名。 写 t1.id 会报歧义错误。
sql
-- 错误！
-- SELECT t1.id FROM t1 JOIN t2 USING (id);

  • USING 只合并指定的列，其他同名列仍会出现两份。 SELECT * 时需注意输出中的重复列名。

  • NATURAL JOIN 与 USING 的输出列顺序会变化。 合并列被提到最前，依赖列位置的代码（如 INSERT ... SELECT、COPY）可能出错。

sql
-- 危险：依赖列位置的写法
-- CREATE TABLE snapshot AS SELECT * FROM users NATURAL JOIN orders;   -- 列顺序不可控

-- 安全：显式列出列名与顺序
CREATE TABLE snapshot AS
SELECT u.id AS user_id, u.name, o.id AS order_id, o.order_no
FROM users AS u
JOIN orders AS o ON o.user_id = u.id;

  • NATURAL LEFT JOIN 同样不推荐。 外连接下补空行为叠加列合并，出错时更难定位。

  • USING 不能表达非等值连接。 需要 >、<、<> 等条件时必须用 ON。

  • 列名大小写敏感。 PostgreSQL 会把未加引号的标识符折叠为小写，USING ("ID") 与 USING (id) 引用的是不同列。

  • 视图定义中使用 NATURAL JOIN 影响面更大。 底层表加列时，所有依赖该视图的查询都会受影响，且不会有任何提示。

最佳实践 ​

  1. 一律使用显式 JOIN ... ON。 这是 PostgreSQL 官方与业界一致的建议：语义清晰、结构无关、可移植。
sql
-- 生产代码标准写法
SELECT u.name, o.order_no
FROM users AS u
JOIN orders AS o ON o.user_id = u.id;

  1. 只在明确需要"列合并输出"时使用 USING。 例如 SELECT * 输出时不希望出现重复的连接列。即使如此，也建议显式列出列名。

  2. 若必须维护使用 NATURAL JOIN 的老代码，先做结构核对。 用 information_schema.columns 确认两表当前的同名列集合。

sql
-- 检查两表的所有同名列（这些都会成为自然连接的连接条件）
SELECT a.column_name, a.data_type, b.data_type
FROM information_schema.columns AS a
JOIN information_schema.columns AS b
  ON b.column_name = a.column_name
 AND b.table_name = 'orders'
WHERE a.table_name = 'users'
  AND a.table_schema = 'public'
ORDER BY a.column_name;
text
 column_name | data_type | data_type
-------------+-----------+-----------
 id          | integer   | integer
 status      | text      | text
(2 rows)

同名列有 id 与 status —— 这正是自然连接会出错的证据。

  1. 为视图与存储过程加连接条件的注释或 COMMENT。 让后来者一眼看懂连接意图。
sql
COMMENT ON VIEW v_user_orders IS '用户订单视图：按 orders.user_id = users.id 内连接，仅保留有用户信息的订单';
  1. 重构 NATURAL JOIN 时用 ON 逐列等价替换。 替换前先用 count(*) 验证改写后的结果与原查询一致（若原查询结果本身就是错的，改写会"修好"它，此时需要与业务确认预期）。
sql
-- 改写前：记录原结果行数
SELECT count(*) FROM users NATURAL JOIN orders;    -- 假设为 0

-- 改写后：确认新结果符合业务预期
SELECT count(*) FROM users AS u JOIN orders AS o ON o.user_id = u.id;   -- 4
  1. 在代码评审中把 NATURAL JOIN 列为阻断项。 它几乎总是 bug 的温床，几乎没有正当使用场景。

下一节

继续学习：自连接（Self Join）

参考链接 ​