SQL Joins Guide: Types, Cardinality, and Fixes SQL JOIN 完整指南:类型、基数、重复行与重构

What are SQL joins? 什么是 SQL JOIN?

The syntax is usually easy; the difficult part is preserving the intended business grain. A join does not merely "add columns." It can remove rows, create null-extended rows, or multiply one input row into many outputs. Before writing a join, state what one result row should represent and whether unmatched entities belong in the answer.

语法通常并不难,真正困难的是保持预期业务粒度。JOIN 不只是"增加字段",它可能删除记录、产生带 NULL 的扩展记录,也可能把一条输入记录扩张成多条输出。写 JOIN 前应先说明结果中的一行代表什么,以及未匹配实体是否应该保留。

SELECT o.order_id, o.order_date, c.customer_name
FROM orders o
INNER JOIN customers c
  ON c.customer_id = o.customer_id;

Choose the join type from the business question 根据业务问题选择 JOIN 类型

Do not choose a join by habit. The type expresses which population must survive. If a report asks for customers who placed an order, an inner join is appropriate. If it asks for every customer and their latest order when available, the customer table belongs on the preserved side of a left join. If the task is data reconciliation between two systems, a full join may be needed to reveal records missing from either source.

不要凭习惯选择 JOIN。连接类型表达的是哪一侧总体必须保留。报告如果只需要已经下单的客户,INNER JOIN 合适;如果需要所有客户及其可能存在的最新订单,客户表应放在 LEFT JOIN 的保留侧;如果要核对两个系统的数据,则可能需要 FULL JOIN 暴露任一来源缺失的记录。

The PostgreSQL table-expression documentation defines these join forms and explains that a cross join between N and M rows produces N × M rows. That arithmetic is also the right mental model for accidental multiplication.

PostgreSQL 表表达式官方文档定义了这些连接形式,并说明 N 行与 M 行的 CROSS JOIN 会产生 N × M 行。这个算式也是理解意外行数膨胀的基础模型。

Understand cardinality before joining tables 连接表之前先理解基数

Cardinality describes how many rows on one side can match a row on the other. A primary-key-to-foreign-key join is commonly many-to-one: many orders can reference one customer, but each order matches at most one customer. Joining from orders to customers normally preserves the order grain. Joining orders to order items is one-to-many and changes the output grain to order item. Neither relationship is inherently bad; problems arise when the query author assumes the original grain still holds.

基数描述一侧的一条记录能够匹配另一侧多少条记录。主键到外键通常是多对一:多个订单可以指向一个客户,但每个订单至多匹配一个客户。订单连接客户一般保持订单粒度;订单连接订单明细属于一对多,输出粒度会变为订单明细。关系本身并非错误,问题在于作者仍假设原粒度不变。

Place join conditions and filters deliberately 谨慎放置连接条件与过滤条件

ON defines which pairs match. USING is concise when both sides share identically named key columns. NATURAL JOIN infers all same-named columns and is fragile because a schema change can silently alter the relationship. In production analytical SQL, explicit conditions are usually easier to review.

ON 定义哪些记录组合匹配;当两侧键字段同名时, USING 更简洁; NATURAL JOIN 会推断所有同名字段,因此模式变化可能静默改变关系。在生产分析 SQL 中,显式条件通常更容易审查。

-- Keeps every customer; only completed orders can match
SELECT c.customer_id, o.order_id
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.customer_id
 AND o.status = 'completed';

-- Removes customers without a completed order
SELECT c.customer_id, o.order_id
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.customer_id
WHERE o.status = 'completed';

The first query preserves every customer because status is part of the match condition. The second evaluates status after the join; null-extended rows fail the predicate, so the result behaves like an inner join for that condition. This difference is central to LEFT JOIN SQL and should be tested with an explicitly unmatched customer.

第一条查询把状态作为匹配条件,因此保留所有客户;第二条在连接后判断状态,NULL 扩展行无法通过条件,所以对该条件而言结果表现得像 INNER JOIN。这是 LEFT JOIN SQL 的核心差异,应使用明确未匹配客户进行测试。

Diagnose duplicate rows created by joins 诊断 JOIN 产生的重复行

A join does not randomly create duplicates. It returns one row for every pair satisfying the condition. If an order matches three promotions and two support contacts, joining both child tables directly can produce six combinations for that order. A later DISTINCT may hide identical projections, but it does not prove the grain is correct and can discard legitimate differences.

JOIN 不会随机制造重复,它会为每一对满足条件的记录返回一行。如果一个订单匹配三个促销记录和两个客服联系人,直接连接两张子表可能为该订单产生六种组合。后续 DISTINCT 也许能隐藏投影完全相同的行,但它不能证明粒度正确,还可能丢弃合理差异。

WITH item_totals AS (
  SELECT order_id,
         SUM(quantity * unit_price) AS order_total
  FROM order_items
  GROUP BY order_id
),
payment_totals AS (
  SELECT order_id,
         SUM(amount) AS paid_total
  FROM payments
  GROUP BY order_id
)
SELECT o.order_id, i.order_total, p.paid_total
FROM orders o
LEFT JOIN item_totals i ON i.order_id = o.order_id
LEFT JOIN payment_totals p ON p.order_id = o.order_id;

Each child table is reduced to one row per order before the joins, so the order grain is explicit. Other valid remedies include selecting one child row with a deterministic window rule, using EXISTS when only presence matters, or returning child collections separately. The correct choice depends on what one output row represents.

每张子表在连接前都被压缩为每个订单一行,因此订单粒度清晰。其他有效方法包括使用确定性窗口规则选一条子记录、只关心存在性时使用 EXISTS ,或将子集合单独返回。正确选择取决于输出一行代表什么。

Control complexity in multi-table join queries 控制多表 JOIN 查询的复杂度

As the join graph grows, correctness becomes harder to infer from syntax alone. One join may preserve the grain, the next may expand it, and a third may filter away unmatched rows. Aliases such as a , b , and c make the graph even harder to review. Use role-based aliases, group related logic in named stages, and state the grain after every stage.

连接图增大后,仅从语法很难推断正确性。第一个连接可能保持粒度,第二个连接可能扩张粒度,第三个连接又可能过滤未匹配记录。 a 、 b 、 c 这类别名会进一步增加审查难度。应使用基于角色的别名,将相关逻辑放进命名阶段,并写明每个阶段后的粒度。

1. Start with the decision population. Choose the table or subquery that defines which entities can appear.
2. Add one relationship at a time. Record expected and actual total rows and distinct business keys.
3. Verify the matching key. Check type, nullability, normalization, temporal validity, and uniqueness.
4. Declare preservation. Explain why unmatched rows should disappear or remain at each join.
5. Reduce child sets early. Aggregate, filter, or rank one-to-many tables before combining them when the final grain is higher.
6. Review the entire graph. Inspect CTE dependencies, join count, nested subqueries, windows, and aggregates together.

1. 从决策总体开始。 选择定义哪些实体可以出现的表或子查询。
2. 一次增加一个关系。 记录预期与实际总行数和不同业务键数量。
3. 验证匹配键。 检查类型、空值、规范化、时间有效性和唯一性。
4. 声明保留规则。 解释每次连接为什么删除或保留未匹配记录。
5. 提前缩减子集合。 当最终粒度较高时,在组合前聚合、过滤或排名一对多表。
6. 审查完整关系图。 把 CTE 依赖、连接数量、嵌套子查询、窗口和聚合一起检查。

Use semi, anti, and temporal join patterns precisely 准确使用半连接、反连接与时间连接模式

Some relationship questions should not return columns from both sides. A semi-join asks whether at least one related row exists; an anti-join asks whether none exists. SQL commonly expresses these with EXISTS and NOT EXISTS . They avoid multiplying the preserved entity when several matches exist and communicate that only presence matters.

有些关系问题不需要返回两侧字段。半连接询问是否至少存在一条相关记录,反连接询问是否完全不存在。SQL 通常使用 EXISTS 与 NOT EXISTS 表达。即使存在多个匹配项,它们也不会复制被保留实体,并清楚表明业务只关心存在性。

SELECT c.customer_id, c.customer_name
FROM customers c
WHERE NOT EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.customer_id
    AND o.status = 'completed'
);

A left join followed by WHERE child.key IS NULL can express the same anti-relationship, but NOT EXISTS is often clearer and avoids accidental changes when additional child filters are placed in the wrong clause. Be careful with NOT IN : if its subquery can return null, three-valued logic may produce no true comparisons. Confirm the engine's semantics and nullability before using it.

LEFT JOIN 后再使用 WHERE child.key IS NULL 也能表达反关系,但 NOT EXISTS 通常更清晰,并能减少新增子表过滤条件放错位置的风险。使用 NOT IN 时尤其谨慎:如果子查询返回 NULL,三值逻辑可能让比较全部无法为真。使用前应确认引擎语义和字段空值属性。

Temporal and range joins match intervals rather than equal keys. For example, an order may need the product price version effective at order_time . The join needs both identity and validity predicates, such as price.valid_from <= order_time and order_time < price.valid_to . Overlapping versions create multiple matches; missing coverage creates unmatched rows. Test boundary timestamps, open-ended intervals, daylight-saving changes, and overlapping effective periods explicitly.

时间连接和范围连接匹配的是区间而不是相等键。例如订单可能需要关联 order_time 当时生效的产品价格版本。连接需要同时包含实体键与有效期条件,如 price.valid_from <= order_time 和 order_time < price.valid_to 。版本重叠会产生多个匹配,覆盖缺口会产生未匹配记录。应明确测试边界时间戳、开放区间、夏令时变化和生效期重叠。

Separate logical joins from physical execution 区分逻辑 JOIN 与物理执行

SQL states the logical relationship; the database selects physical algorithms such as nested loops, hash joins, or merge joins. A hash join is not automatically good, and a nested loop is not automatically bad. Suitability depends on row counts, selectivity, ordering, memory, indexes, and data distribution. The Microsoft SQL Server join documentation distinguishes logical and physical joins and is a useful reference for plan review.

SQL 描述逻辑关系,数据库选择嵌套循环、哈希连接或合并连接等物理算法。哈希连接并非天然优秀,嵌套循环也并非天然糟糕;适用性取决于行数、选择性、排序、内存、索引与数据分布。Microsoft SQL Server JOIN 官方文档区分了逻辑连接与物理连接,适合执行计划审查。

Performance tuning comes after semantic validation. A fast query returning multiplied revenue is still wrong. Benchmark with representative parameter values and volume, include cold and warm cache behavior where relevant, and verify that a rewrite returns the same intended rows before comparing runtime.

性能调优应在语义验证之后进行。快速返回被放大收入的查询仍然是错误查询。应使用代表性参数和值域进行基准测试,必要时覆盖冷缓存与热缓存,并先验证改写结果符合预期,再比较运行时间。

Validate a SQL join before production 上线前验证 SQL JOIN

Use adversarial fixtures rather than only clean sample data. Include an unmatched left row, an unmatched right row, duplicate keys on each side, a null key, a key with different formatting, and a temporally expired relationship. These cases reveal whether the query's preservation and matching rules are explicit.

不要只使用干净样例,应准备对抗性测试数据:左侧未匹配记录、右侧未匹配记录、两侧重复键、空键、格式不同的键以及时间已失效的关系。这些场景能够暴露查询的保留和匹配规则是否明确。

• Assert expected total rows and distinct business keys.
• Measure unmatched rates from both sides.
• Find keys with more than one match and explain each relationship.
• Compare aggregate totals before and after the join.
• Review selected columns so duplicated facts are not mistaken for new events.
• Run the complete statement through structural review before plan-level tuning.

• 断言预期总行数和不同业务键数量。
• 测量两侧未匹配比例。
• 找出拥有多个匹配项的键并解释关系。
• 比较连接前后的聚合总量。
• 审查选择字段,避免把复制事实误认为新事件。
• 执行计划调优前,先对完整语句进行结构审查。

SQL joins frequently asked questions SQL JOIN 常见问题

Continue with a related guide 继续阅读相关指南

Use these closely related guides to verify the next layer of the workflow without mixing separate troubleshooting or decision intents. 可继续阅读以下紧密相关的指南,验证工作流的下一层,同时避免混淆不同的排障或决策意图。

• INNER JOIN in SQL SQL INNER JOIN
• LEFT JOIN in SQL SQL LEFT JOIN