原创

Java 开发日记：SQL 左连接、右连接与内连接的使用与区别

发布时间：2026-03-04 16:00:34 (有修改)
本文热度：浏览 12 赞 0 评论 0
文章标签： Java MySQL SQL
全文共1字，阅读约需1分钟

关系型数据库的查询，本质是在“集合”之间做运算：先确定参与的集合（表与过滤条件），再决定集合如何组合（连接类型），最后决定输出什么（投影列与聚合）。JOIN 的价值在于把多个表的行按照某种规则对齐；它的风险在于一旦对齐规则或过滤位置（ON / WHERE）写错，就会产生“行数膨胀”“丢行”“把外连接写成内连接”等问题。

下面围绕内连接（INNER JOIN）、左连接（LEFT JOIN）、**右连接（RIGHT JOIN）**的使用与区别，结合 MySQL 的执行与常见坑，给出一套可复用的思考框架与工程化写法。

连接的语义：匹配、保留与补空

把两张表的连接抽象成两件事：

匹配规则：用 ON 表达“哪些行能对齐”。
保留规则：决定“哪些行即使没匹配也要保留”。
- INNER JOIN：只保留匹配成功的行（交集）。
- LEFT JOIN：保留左表全部行；右表匹配不到时用 NULL 补齐。
- RIGHT JOIN：保留右表全部行；左表匹配不到时用 NULL 补齐（工程上更常用 LEFT JOIN 反写代替）。

理解连接类型，最有效的方式是把它们放到同一张“语义表”里：

内连接：A ∩ B
左连接：A ⟕ B（保留 A 全部）
右连接：A ⟖ B（保留 B 全部）
全外连接（MySQL 不直接支持）：A ⟗ B（保留两边全部）

MySQL 的 RIGHT JOIN 只是语法糖：A RIGHT JOIN B 等价于 B LEFT JOIN A（列顺序不同而已）。生产环境通常统一使用 LEFT JOIN，减少认知负担和维护成本。

一套可运行的示例表结构（完整建表 SQL）

为了把问题讲清楚，用一个典型业务域：用户、订单、支付。连接的差异会非常直观。

下面 SQL 在 MySQL 8.0 可直接执行（字符集、引擎、约束、索引都给全）。

CREATE DATABASE IF NOT EXISTS join_demo
  DEFAULT CHARACTER SET utf8mb4
  DEFAULT COLLATE utf8mb4_0900_ai_ci;

USE join_demo;

-- 用户表：主表
DROP TABLE IF EXISTS users;
CREATE TABLE users (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  username      VARCHAR(64) NOT NULL,
  status        TINYINT NOT NULL DEFAULT 1 COMMENT '1=正常 0=禁用',
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_username (username),
  KEY idx_status_created (status, created_at)
) ENGINE=InnoDB;

-- 订单表：从表，属于用户
DROP TABLE IF EXISTS orders;
CREATE TABLE orders (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id       BIGINT UNSIGNED NOT NULL,
  order_no      VARCHAR(64) NOT NULL,
  amount_cents  BIGINT NOT NULL COMMENT '金额，单位分',
  state         VARCHAR(16) NOT NULL COMMENT 'CREATED/PAID/CANCELLED',
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_order_no (order_no),
  KEY idx_user_created (user_id, created_at),
  KEY idx_state_created (state, created_at),
  CONSTRAINT fk_orders_user
    FOREIGN KEY (user_id) REFERENCES users(id)
    ON UPDATE RESTRICT ON DELETE RESTRICT
) ENGINE=InnoDB;

-- 支付表：某些订单可能没有支付记录（例如未支付、失败未落库、或离线场景）
DROP TABLE IF EXISTS payments;
CREATE TABLE payments (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  order_id      BIGINT UNSIGNED NOT NULL,
  pay_channel   VARCHAR(16) NOT NULL COMMENT 'ALIPAY/WECHAT/BANK',
  pay_state     VARCHAR(16) NOT NULL COMMENT 'SUCCESS/FAILED/PENDING',
  paid_at       DATETIME NULL,
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_order_id (order_id),
  KEY idx_pay_state_created (pay_state, created_at),
  CONSTRAINT fk_payments_order
    FOREIGN KEY (order_id) REFERENCES orders(id)
    ON UPDATE RESTRICT ON DELETE RESTRICT
) ENGINE=InnoDB;

插入一组刻意构造的数据：有用户没订单、有订单没支付、有支付失败、有订单取消。

TRUNCATE TABLE payments;
TRUNCATE TABLE orders;
TRUNCATE TABLE users;

INSERT INTO users (username, status) VALUES
('alice', 1),
('bob',   1),
('cathy', 1),
('david', 0);

INSERT INTO orders (user_id, order_no, amount_cents, state, created_at) VALUES
(1, 'O1001',  9900, 'CREATED',  '2026-03-01 10:00:00'),
(1, 'O1002', 19900, 'PAID',     '2026-03-01 11:00:00'),
(2, 'O2001',  5000, 'PAID',     '2026-03-01 12:00:00'),
(2, 'O2002',  3000, 'CANCELLED','2026-03-02 09:00:00');

INSERT INTO payments (order_id, pay_channel, pay_state, paid_at, created_at) VALUES
(2, 'ALIPAY', 'SUCCESS', '2026-03-01 11:05:00', '2026-03-01 11:05:00'),
(3, 'WECHAT', 'FAILED',  NULL,                 '2026-03-01 12:10:00');

这组数据具备四种“缺失关系”：

cathy：有用户无订单
alice 的 O1001：有订单无支付
bob 的 O2001：有支付但失败
bob 的 O2002：订单已取消、且无支付

INNER JOIN：只要匹配成功的行（交集）

内连接最适合“只关心关联完整”的场景，例如：只看有订单的用户、只看有支付记录的订单。

1）用户与订单的内连接：只返回有订单的用户

SELECT
  u.id, u.username, o.order_no, o.state
FROM users u
INNER JOIN orders o
  ON o.user_id = u.id
ORDER BY u.id, o.id;

结果不会包含 cathy（没订单）和 david（禁用与否不影响连接，但他本身没订单）。

2）订单与支付的内连接：只返回“有支付记录”的订单

SELECT
  o.order_no, o.state AS order_state,
  p.pay_state, p.pay_channel, p.paid_at
FROM orders o
INNER JOIN payments p
  ON p.order_id = o.id
ORDER BY o.id;

只会出现 O1002、O2001，因为只有这两笔订单在 payments 中存在行。O1001、O2002 没有支付行，会被内连接过滤掉。

3）三表内连接：只看“订单有支付”的用户订单

SELECT
  u.username, o.order_no, p.pay_state
FROM users u
INNER JOIN orders o
  ON o.user_id = u.id
INNER JOIN payments p
  ON p.order_id = o.id
ORDER BY u.id, o.id;

这类查询经常用于统计“已支付订单数”“支付成功金额”等。

LEFT JOIN：保留左表全部行，右表缺失补 NULL

左连接适合“左表是主视角”的场景：用户列表页要带最近一笔订单（即使没有）、订单列表页要带支付信息（即使没有）。

1）用户左连接订单：用户全量保留

SELECT
  u.id, u.username,
  o.order_no, o.state
FROM users u
LEFT JOIN orders o
  ON o.user_id = u.id
ORDER BY u.id, o.id;

这时 cathy 会出现一行，且 o.* 为 NULL。这正是左连接最核心的语义：左边行不会因为右边缺失而消失。

2）订单左连接支付：订单全量保留

SELECT
  o.order_no, o.state,
  p.pay_state, p.pay_channel
FROM orders o
LEFT JOIN payments p
  ON p.order_id = o.id
ORDER BY o.id;

O1001、O2002 会出现，且 p.* 为 NULL。这对于排查“为什么订单没支付记录”尤其好用。

3）左连接 + “找缺失”：查询没有支付记录的订单（反连接）

这是左连接的高频工程用途：找出右表不存在的关联数据。写法是 LEFT JOIN ... ON ... WHERE right_table.pk IS NULL。

SELECT
  o.order_no, o.state
FROM orders o
LEFT JOIN payments p
  ON p.order_id = o.id
WHERE p.id IS NULL
ORDER BY o.id;

这会返回 O1001、O2002。注意这里用 p.id IS NULL（或 p.order_id IS NULL）是最稳妥的，因为当右侧没匹配时整行是补 NULL 的。

RIGHT JOIN：保留右表全部行（能不用就不用）

右连接等价于把左右交换后的左连接。因为大多数团队习惯“主表在左、从表在右”，右连接往往让人读起来别扭，且不利于统一规范。

例如下面的右连接：

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

等价于：

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

生产代码建议统一写后者：更直觉，且更容易在代码审查中快速发现“主从视角”是否正确。

最容易写错的点：WHERE 条件把外连接“变成”内连接

这是连接学习中最关键、也是最常见的线上 bug 来源之一：对右表的过滤条件写在 WHERE 会把 LEFT JOIN 的缺失行过滤掉。

先看一个错误写法：想查“所有订单及其支付成功信息（没有支付也要展示）”，很多人会写成这样：

SELECT
  o.order_no,
  p.pay_state
FROM orders o
LEFT JOIN payments p
  ON p.order_id = o.id
WHERE p.pay_state = 'SUCCESS';

这段 SQL 实际效果等价于内连接：因为 p.pay_state 为 NULL 的行在 WHERE 条件下会被过滤掉，导致没有支付记录的订单消失。

正确写法是把右表过滤放到 ON 中，让“匹配规则”变严格，而不是让“输出保留规则”失效：

SELECT
  o.order_no,
  p.pay_state
FROM orders o
LEFT JOIN payments p
  ON p.order_id = o.id
 AND p.pay_state = 'SUCCESS'
ORDER BY o.id;

此时：

有成功支付：p.pay_state='SUCCESS'
无支付 / 非成功支付：右侧补 NULL，订单仍保留

如果你的目标是“只看支付成功的订单”，那就应该直接用 INNER JOIN 或保留 WHERE，不要用 LEFT JOIN 伪装。

一个可复用的判断准则

需要保留左表全量：右表条件放 ON。
只想要匹配成功的行：用 INNER JOIN 或在 WHERE 上加条件。
想找右表缺失：LEFT JOIN + WHERE right.pk IS NULL。

连接条件里该放什么：ON 只做“关系”，WHERE 才做“筛选”吗？

很多教程会给出一句口号：“ON 放连接条件，WHERE 放过滤条件”。这句话只能当入门指导，工程上要更精确：

ON 决定如何匹配（以及在外连接里，决定哪些右侧行能匹配上）。
WHERE 决定最终保留哪些输出行。

所以外连接中，右表的过滤往往应该放在 ON，否则会影响最终行保留，进而改变语义。这不是“规范”，而是由外连接的补空机制决定的。

而左表过滤条件通常放在 WHERE 没问题，因为左表行本来就会保留；你在 WHERE 过滤左表行是符合直觉的。

例如：只看正常用户，但仍想展示“无订单”的用户：

SELECT
  u.username, o.order_no
FROM users u
LEFT JOIN orders o
  ON o.user_id = u.id
WHERE u.status = 1
ORDER BY u.id, o.id;

这里 u.status=1 放 WHERE 完全正确。

多表连接时的“主视角”设计：先定主表，再逐层扩展

三张以上表连接最怕的是“顺手写”，最终自己也说不清结果集应该长什么样。建议固定一个思考步骤：

确定主视角：结果集必须包含哪些实体？
主表放左侧：主表行是否必须全量保留？若是，用 LEFT JOIN 扩展。
每扩展一次都问自己：右表缺失要不要保留主表行？
过滤条件分层：主表过滤放 WHERE；外连接右表过滤放 ON。

例子：要展示“所有用户 + 最近一笔订单 + 该订单支付是否成功（如果有）”。

关键点：用户必须全量，订单可能没有，支付可能没有。因此连接链条应该是：

users 主表
LEFT JOIN orders（但“最近一笔”需要聚合或窗口函数）
LEFT JOIN payments（支付成功过滤在 ON 或做派生字段）

MySQL 8.0 可用窗口函数解决“最近一笔订单”：

WITH latest_orders AS (
  SELECT
    o.*,
    ROW_NUMBER() OVER (PARTITION BY o.user_id ORDER BY o.created_at DESC, o.id DESC) AS rn
  FROM orders o
)
SELECT
  u.username,
  lo.order_no,
  lo.state AS order_state,
  p.pay_state,
  (p.pay_state = 'SUCCESS') AS paid_success
FROM users u
LEFT JOIN latest_orders lo
  ON lo.user_id = u.id
 AND lo.rn = 1
LEFT JOIN payments p
  ON p.order_id = lo.id
ORDER BY u.id;

如果你把 lo.rn = 1 写到 WHERE，就会把没有订单的用户过滤掉（外连接变内连接）。所以这类限制也应放在 ON。

DISTINCT、GROUP BY 与连接：行数膨胀的根源与处理方式

连接的另一个典型坑是“一对多”导致行数膨胀。比如用户对订单是一对多，用户连接订单后行数会变多；再连接支付（如果支付是一对一还好），但如果再连订单明细（又是一对多）就会指数膨胀。

1）行数膨胀是正常现象，但必须是你想要的

如果你只想要“每个用户一行”，就不能直接 users LEFT JOIN orders，而应该：

先把 orders 聚合成每用户一行（例如统计、取最新），再连接；或
用子查询/CTE 先选出目标订单，再连接。

2）不要用 DISTINCT 作为“止血贴”

很多人看到行数变多就加 DISTINCT，这会掩盖问题，且可能导致隐藏的性能成本（排序/临时表）。正确方式是让连接后的关系与需求一致：该聚合就聚合，该挑选一行就挑选一行。

性能与索引：连接能不能快，核心看“驱动表”与索引命中

连接性能的本质是：MySQL 会选择一个表（或中间结果）作为驱动表，对其每一行去另一个表里找匹配行。找得快不快取决于：

驱动表的行数（越小越好）
被驱动表的连接键是否有合适索引
过滤条件能否提前下推、减少参与连接的行数
连接列的数据类型是否一致（否则可能隐式转换导致索引失效）

1）连接列类型必须一致

例如 orders.user_id 是 BIGINT UNSIGNED，users.id 也是 BIGINT UNSIGNED。如果一边是 VARCHAR 或 BIGINT（有符号）而另一边是 BIGINT UNSIGNED，MySQL 可能会发生隐式转换，造成索引不可用或比较异常。

工程上建议：

主键统一使用同一类型（如 BIGINT UNSIGNED）
外键列保持一致
禁止在连接键上做函数/表达式（如 ON DATE(o.created_at)=...），把表达式移到常量侧或用生成列/冗余字段

2）为被连接的外键列建索引

示例建表中已经有：

orders.idx_user_created(user_id, created_at)
payments.uk_order_id(order_id)

这能确保 users -> orders 与 orders -> payments 的连接都能走索引。

3）EXPLAIN 的关注点

EXPLAIN 时关注：

type（ref/eq_ref 比 ALL 好）
key 是否命中预期索引
rows 是否过大
Extra 是否有 Using temporary / Using filesort（不一定是错，但要有心理预期）

外连接在某些场景会限制优化器重排顺序的空间，因此不要迷信“写成 LEFT JOIN 就一定慢”，而是让驱动表尽可能小、连接键索引尽可能准确。

版本差异：MySQL 5.7 vs 8.0 在连接相关能力上的关键区别

在工程实践里，连接写法受版本影响较大的点主要有三个：

窗口函数
- MySQL 8.0 支持 ROW_NUMBER()、RANK() 等窗口函数，非常适合“每组取最新一行”。
- MySQL 5.7 不支持窗口函数，通常要用子查询 + 聚合 + 再连接，或用变量写法（不推荐）。
CTE（WITH 子句）
- MySQL 8.0 支持 WITH / 递归 CTE，能让复杂连接逻辑更清晰。
- MySQL 5.7 不支持，只能用派生表/子查询嵌套。
优化器能力与统计信息
- MySQL 8.0 在直方图统计、成本估算、某些连接优化上更成熟；同一条 SQL 在 8.0 更容易选到合理的索引与连接顺序。
- MySQL 5.7 对复杂查询有时更依赖人工改写（例如先过滤再连接、强制索引、拆分查询）。

如果你的团队处在 5.7 迁移 8.0 阶段，建议优先把“每组取一行”的逻辑从“变量技巧”迁移到窗口函数，连接语义会更直观且更不易出错。

Java + Spring Boot 实战：用清晰的连接表达业务语义

连接不是 SQL 的炫技，而是业务语义的映射。下面给出三类最常见的业务需求与写法建议（重点是连接选择与条件位置）。

1）需求：订单列表页，展示支付状态（无支付也要展示）

语义：订单为主表，支付为可选信息 → LEFT JOIN，支付过滤在 ON（如果有）。

SELECT
  o.id, o.order_no, o.amount_cents, o.state,
  p.pay_state, p.pay_channel, p.paid_at
FROM orders o
LEFT JOIN payments p
  ON p.order_id = o.id
ORDER BY o.created_at DESC, o.id DESC
LIMIT ?, ?;

在 MyBatis / JPA 原生 SQL 中，分页通常按订单排序；支付字段为 NULL 是正常情况，前端展示为“未支付/无记录”。

2）需求：统计“支付成功订单金额”，只看成功

语义：只要匹配成功且状态成功 → INNER JOIN + WHERE，或 LEFT JOIN + WHERE（但不如直接内连接清晰）。

SELECT
  SUM(o.amount_cents) AS total_amount_cents
FROM orders o
INNER JOIN payments p
  ON p.order_id = o.id
WHERE p.pay_state = 'SUCCESS'
  AND o.state = 'PAID';

这里把订单状态也加上是为了避免“支付成功但订单状态未更新”的数据异常影响统计（具体取决于业务一致性设计）。

3）需求：找出“有订单但没有支付记录”的异常订单

语义：反连接 → LEFT JOIN + WHERE p.id IS NULL

SELECT
  o.order_no, o.state, o.created_at
FROM orders o
LEFT JOIN payments p
  ON p.order_id = o.id
WHERE p.id IS NULL
  AND o.state IN ('CREATED', 'PAID')
ORDER BY o.created_at DESC;

这类 SQL 常用于对账、风控与数据修复任务。

连接的工程化规范：让团队少踩坑

在多人协作项目里，JOIN 的问题往往不是“不会写”，而是“语义写偏了没人发现”。以下是能显著降低线上事故的规范建议：

统一使用 LEFT JOIN，禁止 RIGHT JOIN 让“主视角在左”的约定稳定，代码审查更容易。
外连接右表过滤默认放 ON 除非你明确要把外连接转成内连接，否则不要把 right_table.xxx = ... 放 WHERE。
先写主表过滤，再写 JOIN 写 SQL 时先把主表的 WHERE 条件写清，再逐个 JOIN 扩展字段，避免逻辑混乱。
一对多连接前先聚合/选一行 需要“一行代表一个实体”的结果集时，不要用 DISTINCT 止血，应该在连接前把从表变成“一对一”。
连接键类型一致、索引齐全 建表阶段把外键列索引建好，避免上线后靠 hint/强制索引救火。
对外连接的 NULL 做显式处理 展示层或统计层中，明确约定 NULL 的业务含义：未发生、未知、缺失记录，必要时用 COALESCE 转换为默认值。

例如把支付状态映射为展示字段：

SELECT
  o.order_no,
  COALESCE(p.pay_state, 'NO_RECORD') AS pay_state_view
FROM orders o
LEFT JOIN payments p
  ON p.order_id = o.id;

小结：用三句话快速选型

只要匹配成功的行：INNER JOIN
左表必须全量保留：LEFT JOIN（右表缺失补 NULL）
右连接能写但不推荐：用 LEFT JOIN 交换顺序替代

真正决定你写得对不对的，是两件事：主视角是否明确，以及右表过滤是否放在 ON（外连接）还是 WHERE（最终筛选）。把这两点建立成习惯，JOIN 的大部分坑都会自然消失。

正文到此结束

所属分类：Mysql

本文链接： https://refblogs.com/article/1200
版权声明： 本文由老牛原创发布，转载或复制请以超链接形式转载,并注明出处搬砖的码农。