SQL 连接查询详解

适用范围

本文面向已经掌握 SELECT、WHERE、GROUP BY 等基础语法的开发者，系统说明 SQL 连接查询的用法、差异、常见坑点、性能影响和面试速查要点。

本文以标准 SQL 写法为主。不同数据库在语法或优化器行为上存在差异时，会在对应章节单独说明 MySQL、PostgreSQL、SQL Server 的常见差异。

目录

• 一、示例数据模型
• 二、JOIN 的基础模型
• 三、INNER JOIN：只保留匹配行
• 四、LEFT JOIN：保留左表全部行
• 五、RIGHT JOIN：保留右表全部行
• 六、FULL OUTER JOIN：保留两边全部行
• 七、CROSS JOIN：笛卡尔积
• 八、SELF JOIN：自连接
• 九、多条件连接与非等值连接
• 十、半连接与反连接
• 十一、关键差异专题
• 十二、性能与执行计划要点
• 十三、面试与速查
• 十四、最佳实践清单

一、示例数据模型

后文使用一组电商订单表作为统一示例。

表结构

CREATE TABLE users (
    user_id     INTEGER PRIMARY KEY,
    user_name   VARCHAR(50) NOT NULL,
    city        VARCHAR(50),
    referrer_id INTEGER
);

CREATE TABLE orders (
    order_id   INTEGER PRIMARY KEY,
    user_id    INTEGER NOT NULL,
    order_date DATE NOT NULL,
    status     VARCHAR(20) NOT NULL,
    total_amt  DECIMAL(10, 2) NOT NULL
);

CREATE TABLE order_items (
    item_id    INTEGER PRIMARY KEY,
    order_id   INTEGER NOT NULL,
    product_id INTEGER NOT NULL,
    quantity   INTEGER NOT NULL,
    unit_price DECIMAL(10, 2) NOT NULL
);

CREATE TABLE products (
    product_id   INTEGER PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    category     VARCHAR(50) NOT NULL
);

CREATE TABLE payments (
    payment_id INTEGER PRIMARY KEY,
    order_id   INTEGER NOT NULL,
    pay_method VARCHAR(20) NOT NULL,
    paid_amt   DECIMAL(10, 2) NOT NULL,
    paid_at    TIMESTAMP
);

表关系

关系	说明
users.user_id = orders.user_id	一个用户可以有多个订单
orders.order_id = order_items.order_id	一个订单可以有多个明细
products.product_id = order_items.product_id	一个商品可以出现在多个订单明细中
orders.order_id = payments.order_id	一个订单可能有付款记录，也可能没有
users.referrer_id = users.user_id	用户可以由另一个用户推荐，用于自连接示例

示例数据

users	user_id	user_name	city	referrer_id
	1	Alice	Shanghai	null
	2	Bob	Beijing	1
	3	Carol	Shanghai	1
	4	Dave	Shenzhen	null

orders	order_id	user_id	status	total_amt
	101	1	paid	299.00
	102	1	pending	88.00
	103	2	paid	560.00

payments	payment_id	order_id	pay_method	paid_amt
	9001	101	card	299.00
	9002	103	alipay	560.00

二、JOIN 的基础模型

1. 连接不是简单“拼表”

逻辑上，连接可以理解为：

1. 从两张表产生候选行组合。
2. 用 ON 条件判断哪些组合匹配。
3. 根据连接类型决定保留哪些匹配或不匹配的行。

如果没有连接条件，两张表会产生笛卡尔积。左表有 m 行，右表有 n 行，结果最多会有 m * n 行。

2. 最小连接语法

SELECT
    u.user_id,
    u.user_name,
    o.order_id,
    o.total_amt
FROM users AS u
JOIN orders AS o
    ON u.user_id = o.user_id;

JOIN 默认通常等价于 INNER JOIN。实际项目中建议写完整的 INNER JOIN、LEFT JOIN 等类型，让语义更明确。

3. 结果行数由关系基数决定

关系	结果特点
一对一	每个匹配键最多产生一行
一对多	“一”的那一侧会被重复
多对多	两侧都会按匹配组合放大
无匹配	内连接丢弃，外连接按规则保留并补 NULL

三、INNER JOIN：只保留匹配行

INNER JOIN 只返回两边都满足连接条件的行。

SELECT
    u.user_name,
    o.order_id,
    o.status,
    o.total_amt
FROM users AS u
INNER JOIN orders AS o
    ON u.user_id = o.user_id;

结果只包含有订单的用户。示例数据中，Dave 没有订单，不会出现在结果中。

适用场景

• 查询必须同时存在于两张表的数据。
• 查询订单及其所属用户。
• 查询订单明细及其商品信息。

常见误区

如果连接键不是唯一的，INNER JOIN 会产生多行匹配结果。重复行不一定是数据库错误，通常是表关系本身导致的。

四、LEFT JOIN：保留左表全部行

LEFT JOIN 返回左表全部行。右表匹配不到时，右表字段补 NULL。

SELECT
    u.user_name,
    o.order_id,
    o.status
FROM users AS u
LEFT JOIN orders AS o
    ON u.user_id = o.user_id;

这个查询会保留所有用户。没有订单的 Dave 仍会出现，但 order_id 和 status 为 NULL。

适用场景

• 查所有用户以及他们可能存在的订单。
• 查所有订单以及可能存在的付款记录。
• 找出主表中没有从表记录的数据。

常见误区：WHERE 会把外连接变成内连接

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

这会过滤掉 o.status 为 NULL 的行，因此没有订单的用户也会被过滤掉。若想保留全部用户，并且只匹配已支付订单，应把条件放进 ON：

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

五、RIGHT JOIN：保留右表全部行

RIGHT JOIN 返回右表全部行。左表匹配不到时，左表字段补 NULL。

SELECT
    u.user_name,
    o.order_id
FROM users AS u
RIGHT JOIN orders AS o
    ON u.user_id = o.user_id;

多数情况下，RIGHT JOIN 可以改写为交换表顺序的 LEFT JOIN：

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

实际项目中通常优先使用 LEFT JOIN，因为阅读方向更一致。

六、FULL OUTER JOIN：保留两边全部行

FULL OUTER JOIN 返回左右两表的全部行。匹配成功时合并为一行；某一边匹配不到时，另一边字段补 NULL。

SELECT
    u.user_id,
    u.user_name,
    o.order_id
FROM users AS u
FULL OUTER JOIN orders AS o
    ON u.user_id = o.user_id;

适用场景

• 对账：找出两边都有、只在左边、只在右边的数据。
• 数据迁移校验。
• 主数据和外部系统数据比对。

方言差异

• PostgreSQL 和 SQL Server 支持 FULL OUTER JOIN。
• MySQL 8.x 不直接支持 FULL OUTER JOIN，通常用 LEFT JOIN 与 RIGHT JOIN 加 UNION 模拟。

SELECT u.user_id, u.user_name, o.order_id
FROM users AS u
LEFT JOIN orders AS o
    ON u.user_id = o.user_id
UNION
SELECT u.user_id, u.user_name, o.order_id
FROM users AS u
RIGHT JOIN orders AS o
    ON u.user_id = o.user_id;

七、CROSS JOIN：笛卡尔积

CROSS JOIN 返回两张表所有行的组合。

SELECT
    u.user_name,
    p.product_name
FROM users AS u
CROSS JOIN products AS p;

如果有 4 个用户和 10 个商品，结果会有 40 行。

适用场景

• 生成所有组合。
• 构造日期维度与业务维度的完整矩阵。
• 测试数据生成。

风险

CROSS JOIN 很容易产生巨大结果集。使用前必须估算行数。

八、SELF JOIN：自连接

自连接是同一张表与自身连接，常用于层级关系、推荐关系、上下级关系。

SELECT
    u.user_name AS user_name,
    r.user_name AS referrer_name
FROM users AS u
LEFT JOIN users AS r
    ON u.referrer_id = r.user_id;

这里 users 被使用了两次，必须用不同别名区分不同角色。

九、多条件连接与非等值连接

1. 多条件连接

连接条件可以包含多个字段或业务约束。

SELECT
    o.order_id,
    p.payment_id,
    p.paid_amt
FROM orders AS o
INNER JOIN payments AS p
    ON o.order_id = p.order_id
   AND o.total_amt = p.paid_amt;

多条件连接常用于复合主键、版本表、租户隔离字段和对账场景。

2. 非等值连接

连接条件不一定只能用 =，也可以使用范围条件。

SELECT
    o.order_id,
    o.total_amt,
    r.level_name
FROM orders AS o
INNER JOIN amount_ranges AS r
    ON o.total_amt >= r.min_amt
   AND o.total_amt <  r.max_amt;

非等值连接常用于金额区间、时间区间、价格区间和等级匹配。

十、半连接与反连接

半连接和反连接不一定写成 JOIN，但它们解决的是连接类问题。

1. 半连接：存在即可

查询有订单的用户：

SELECT
    u.user_id,
    u.user_name
FROM users AS u
WHERE EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.user_id = u.user_id
);

EXISTS 只关心是否存在匹配行，不会因为一个用户有多个订单而重复返回用户。

2. 反连接：不存在才要

查询没有订单的用户：

SELECT
    u.user_id,
    u.user_name
FROM users AS u
WHERE NOT EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.user_id = u.user_id
);

这是查“主表中缺失从表记录”的常用写法。

3. LEFT JOIN ... IS NULL

同样可以用外连接实现反连接：

SELECT
    u.user_id,
    u.user_name
FROM users AS u
LEFT JOIN orders AS o
    ON u.user_id = o.user_id
WHERE o.order_id IS NULL;

4. NOT IN 与 NULL 风险

SELECT
    u.user_id,
    u.user_name
FROM users AS u
WHERE u.user_id NOT IN (
    SELECT o.user_id
    FROM orders AS o
);

如果子查询结果中包含 NULL，NOT IN 可能返回空结果或非预期结果。反连接优先使用 NOT EXISTS。

十一、关键差异专题

1. ON 与 WHERE

ON 决定两表如何匹配，WHERE 决定最终结果如何过滤。

对于 INNER JOIN，过滤条件放在 ON 或 WHERE 中，很多情况下结果等价：

SELECT u.user_name, o.order_id
FROM users AS u
INNER JOIN orders AS o
    ON u.user_id = o.user_id
WHERE o.status = 'paid';

对于外连接，右表条件放在 WHERE 中会过滤掉补 NULL 的行，可能改变外连接语义。

2. USING 与 ON

当两张表连接字段同名时，可以使用 USING：

SELECT
    order_id,
    paid_amt
FROM orders
INNER JOIN payments
    USING (order_id);

USING (order_id) 等价于 ON orders.order_id = payments.order_id 的简化写法，但输出列通常只保留一个 order_id。

方言差异

• PostgreSQL 和 MySQL 支持 USING。
• SQL Server 不支持 USING，应使用 ON。

3. JOIN 与子查询

查询用户及订单数量，通常可以用连接聚合：

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

也可以用相关子查询：

SELECT
    u.user_id,
    u.user_name,
    (
        SELECT COUNT(*)
        FROM orders AS o
        WHERE o.user_id = u.user_id
    ) AS order_count
FROM users AS u;

现代数据库优化器可能将两者优化成相近的执行计划。选择时应优先考虑语义清晰，再结合执行计划验证性能。

4. COUNT(*) 与 COUNT(column)

在外连接聚合中，COUNT(*) 会统计补 NULL 后的行，COUNT(o.order_id) 只统计右表真实匹配行。

SELECT
    u.user_id,
    COUNT(*) AS row_count,
    COUNT(o.order_id) AS order_count
FROM users AS u
LEFT JOIN orders AS o
    ON u.user_id = o.user_id
GROUP BY u.user_id;

没有订单的用户，COUNT(*) 通常为 1，COUNT(o.order_id) 为 0。

十二、性能与执行计划要点

1. 连接键要有合适索引

常见连接键应该建立索引：

CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_order_items_order_id ON order_items(order_id);
CREATE INDEX idx_payments_order_id ON payments(order_id);

索引不能保证查询一定快，但缺少连接键索引会让大表连接很容易退化为大量扫描。

2. 常见连接算法

算法	基本思路	适合场景
Nested Loop Join	外层取一行，内层查匹配行	小表驱动大表、内层有高选择性索引
Hash Join	对一侧构建哈希表，另一侧探测	大量等值连接、无序数据
Merge Join	两侧按连接键排序后顺序合并	两侧已有序或排序成本可接受

具体使用哪种算法由数据库优化器决定。开发者应重点提供正确统计信息、索引和可优化的 SQL 写法。

3. 避免无意的行数放大

订单连接订单明细时，一个订单会重复多行：

SELECT
    o.order_id,
    o.total_amt,
    oi.product_id
FROM orders AS o
INNER JOIN order_items AS oi
    ON o.order_id = oi.order_id;

如果此时再对 o.total_amt 求和，可能重复计算订单金额。应先在合适粒度聚合，再连接：

SELECT
    o.user_id,
    SUM(o.total_amt) AS user_total_amt
FROM orders AS o
GROUP BY o.user_id;

4. 只选择必要列

SELECT * 会增加网络传输、内存占用和阅读成本。连接查询尤其应该明确列名，并给重复字段设置别名。

5. 用执行计划验证假设

性能问题不要只凭经验判断。应使用对应数据库的执行计划工具：

数据库	常用方式
PostgreSQL	EXPLAIN、EXPLAIN ANALYZE
MySQL	EXPLAIN、EXPLAIN ANALYZE
SQL Server	Actual Execution Plan、SET STATISTICS IO ON

十三、面试与速查

1. JOIN 类型对比表

JOIN 类型	是否保留左表未匹配行	是否保留右表未匹配行	典型用途
INNER JOIN	否	否	查两边都存在的数据
LEFT JOIN	是	否	查主表全部数据及可选从表数据
RIGHT JOIN	否	是	可读性上通常改写为 LEFT JOIN
FULL OUTER JOIN	是	是	对账、差异比对
CROSS JOIN	不按匹配判断	不按匹配判断	生成所有组合
SELF JOIN	取决于连接类型	取决于连接类型	层级、推荐、上下级关系

2. 场景选择表

需求	推荐写法
只要两边都存在的数据	INNER JOIN
保留所有用户，带出订单	LEFT JOIN
找没有订单的用户	NOT EXISTS 或 LEFT JOIN ... IS NULL
判断是否存在订单，不想重复用户	EXISTS
对比两张表差异	FULL OUTER JOIN，MySQL 用 UNION 模拟
生成用户与商品所有组合	CROSS JOIN
查询用户和推荐人	SELF JOIN

3. 常见面试题

问题 1：INNER JOIN 和 LEFT JOIN 的区别是什么？

INNER JOIN 只返回两边都匹配的行。LEFT JOIN 保留左表全部行，右表不匹配时补 NULL。

问题 2：为什么 LEFT JOIN 后在 WHERE 中写右表条件会丢失左表数据？

因为右表不匹配的行会补 NULL，而 WHERE right_table.column = value 会过滤掉这些 NULL 行，使结果表现得像内连接。

问题 3：查没有订单的用户，用 NOT IN、NOT EXISTS 还是 LEFT JOIN ... IS NULL？

优先考虑 NOT EXISTS，语义清晰且不受子查询 NULL 值影响。LEFT JOIN ... IS NULL 也常用。NOT IN 在子查询可能产生 NULL 时有风险。

问题 4：为什么连接后数据变多了？

通常是连接键在右表或两边都不是唯一键，产生了一对多或多对多匹配。需要检查数据基数，必要时先聚合或去重再连接。

问题 5：COUNT(*) 和 COUNT(o.order_id) 在 LEFT JOIN 中有什么区别？

COUNT(*) 统计结果行，包括右表未匹配但被保留的左表行。COUNT(o.order_id) 只统计右表真实匹配的非 NULL 订单。

4. 易错清单

• 忘记写 ON 条件，意外产生笛卡尔积。
• 在外连接的 WHERE 中过滤右表字段，导致外连接退化。
• 在一对多连接后直接对主表金额求和，导致重复计算。
• 用 SELECT * 导致同名字段混乱。
• 误以为 DISTINCT 是解决重复行的首选方案。
• 使用 NOT IN 时未考虑子查询中的 NULL。
• 没有为大表连接键建立合适索引。

十四、最佳实践清单

1. 明确写出连接类型，不依赖默认 JOIN 语义。
2. 每个连接都写清楚 ON 条件。
3. 外连接右表过滤条件优先放在 ON 中，最终结果过滤再放 WHERE。
4. 连接前先确认表关系是一对一、一对多还是多对多。
5. 聚合前确认当前结果集粒度。
6. 查存在性优先考虑 EXISTS，查不存在优先考虑 NOT EXISTS。
7. 大表连接前检查连接键索引和执行计划。
8. 避免 SELECT *，为同名列设置明确别名。
9. 用小样本验证结果行数，再放到生产规模数据上执行。
10. 性能结论以执行计划和实际耗时为准。