Skip to content

连接

组合行 从 two or 更多表 using INNER, LEFT, RIGHT, and FULL 连接.

你将学到

  • 为什么连接 exist and 如何它们组合数据从 多个表
  • INNER JOIN — 仅 matching 行从 两者都表
  • LEFT JOIN — 所有行 从左 表, matched or 不
  • RIGHT JOIN and FULL OUTER JOIN
  • 合并 on 正确键 and avoiding Cartesian products

概念讲解

为什么需要连接

Data in a relational database is split across multiple tables to avoid redundancy. Joins reassemble that data for querying. A join combines rows from two tables based on a related column — usually a foreign key. The general syntax is SELECT ... FROM table_a JOIN table_b ON table_a.col = table_b.col.

INNER JOIN

INNER JOIN returns only rows that have matching values in both tables. If a user has no orders, that user does not appear in the result. This is the most common join type — if you write just JOIN, it defaults to INNER.

LEFT JOIN

LEFT JOIN (or LEFT OUTER JOIN) returns every row from the left table, and the matching rows from the right table. If there is no match, the right table's columns are filled with NULL. This is perfect for "列表所有用户 and 它们的顺序, 如果任何" — you see users with zero orders too.

RIGHT and FULL OUTER JOIN

RIGHT JOIN is the mirror of LEFT JOIN — all rows from the right table. FULL OUTER JOIN returns all rows from both tables; unmatched columns are NULL on either side. These are less common because you can usually rewrite a RIGHT JOIN as a LEFT JOIN by swapping table order.

Avoiding Cartesian Products

If you omit the ON clause (or write a condition that is always true), you get a Cartesian product — every row in table A joined with every row in table B. For two 1000-row tables that is 1,000,000 rows. Always specify an ON clause with the correct join key.

示例代码

              -- INNER JOIN: only users who have placed orders
SELECT u.name, o.id AS order_id, o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id
ORDER BY u.name;

-- LEFT JOIN: all users, with NULL for those without orders
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.name
ORDER BY order_count DESC;

-- Join three tables: users -> orders -> order_items
SELECT u.name, o.id, oi.product_name, oi.quantity
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN order_items oi ON o.id = oi.order_id;
            

INNER JOIN for matching 行, LEFT JOIN 到包含用户使用否 顺序, and three-表连接 chaining 用户 → 顺序 → order_items.

动手试一试

  • SQL Formatter

    格式 multi-表 JOIN 查询 for readability.

  • JSON Diff

    Compare 查询 results 从不同连接类型.

常见错误

错误写法

Forgetting ON clause

正确做法

不使用 ON, 你获取 Cartesian product (每个行 × 每个行). 总是 specify 连接条件, e.g. ON .id = b.a_id.

错误写法

Using INNER JOIN 当你 需要 LEFT JOIN

正确做法

如果你 需要所有行 从 one 表偶数当 那里是 否 match in other, use LEFT JOIN. INNER JOIN silently drops unmatched 行.

练习 连接

13 个练习

练习 连接

查阅不认识的术语

新手友好的术语表用大白话解释编程术语——变量、函数、DOM、Promise 等等。

查看术语表

动手做项目

通过引导式项目应用所学,含起始代码与解决方案。

查看项目

解读错误信息

用大白话解释常见错误——什么意思、为什么发生、如何修复。

查看错误