MySQL 入门教程

MySQL INNER JOIN

关系型数据库中的数据通常分散在多个表中，以保持效率并避免冗余（正如我们在模块 2 和模块 6 第一课中讨论数据库设计和主/外键时所探讨的那样）。为了检索结合了这些相关表中数据的有意义信息，我们使用 JOIN (连接) 操作。

INNER JOIN（内连接）是最基本、最常用的 JOIN 类型之一，用于根据两个或多个表之间的相关列合并它们的数据行。它仅返回在基于指定的连接条件在两个表中都有匹配项的行。

1. 理解 INNER JOIN

只要两个表的列之间存在匹配，INNER JOIN 关键字就会从两个表中选择所有行。当一个公共字段（或一组字段）具有匹配的值时，它有效地将两个表中的行组合在一起。在另一个表中没有对应匹配项的行将被排除在结果集之外。

考虑我们一直在使用的 world 数据库。它有一个 city（城市）表和一个 country（国家）表。每个城市都属于一个特定的国家。city 表有一个 CountryCode 列，这是一个引用 country 表中 Code 列（主键）的外键。INNER JOIN 允许我们在单个结果集中合并来自两个表的信息，例如城市名称及其对应的国家名称。

INNER JOIN 的基本语法是：

SELECT
    column_list
FROM
    table1
INNER JOIN
    table2 ON table1.matching_column = table2.matching_column;
  • SELECT column_list: 指定你希望从连接后的表中检索的列。
  • FROM table1: 指示从中检索数据的第一个表。
  • INNER JOIN table2: 指定要与 table1 连接的第二个表。
  • ON table1.matching_column = table2.matching_column: 这是连接条件 (join condition)。它指定了两个表之间的关系。INNER JOIN 将仅返回 table1.matching_column 中的值等于 table2.matching_column 中的值的行。

使用别名 (aliases) 来表示表名是一种常见的做法，这可以使查询更简短、更易读，尤其是在处理多个表或较长的表名时。

SELECT
    c.Name AS CityName,
    co.Name AS CountryName
FROM
    city AS c
INNER JOIN
    country AS co ON c.CountryCode = co.Code;

在这里，c 是 city 表的别名，co 是 country 表的别名。通过缩短列引用的长度，这极大地提高了代码的可读性。

2. INNER JOIN 的详细示例

让我们使用 world 数据库，通过几个实际例子来说明 INNER JOIN。

2.1 示例 1：列出城市及其所属国家

要查看所有城市及其所属国家的列表，我们可以基于它们共同的 CountryCode/Code 列连接 city 和 country 表。

SELECT
    city.Name AS City,
    country.Name AS Country
FROM
    city
INNER JOIN
    country ON city.CountryCode = country.Code
ORDER BY
    country.Name, city.Name;

在这个查询中：

  1. 我们选择 city 表中的 Name（别名为 City）和 country 表中的 Name（别名为 Country）。
  2. 我们将 city 与 country 连接起来。
  3. ON 子句指定 city 表中的 CountryCode 必须匹配 country 表中的 Code。
  4. ORDER BY 用于先按国家、再按城市进行字母排序。

结果集将仅包含在 country 表中具有匹配国家代码的城市。如果由于某种原因，city 表中的某个城市具有在 country 表中不存在的 CountryCode，那么该城市将不会出现在结果中。

2.2 示例 2：组合三个表

INNER JOIN 可以扩展为组合两个以上的表。我们只需链接多个 INNER JOIN 子句。假设我们想要列出所有城市、它们的国家以及该国家所在的大洲。由于 country 表已经有了一个 Continent 列，我们不需要额外的连接：

SELECT
    c.Name AS City,
    co.Name AS Country,
    co.Continent AS Continent
FROM
    city AS c
INNER JOIN
    country AS co ON c.CountryCode = co.Code
ORDER BY
    co.Continent, co.Name, c.Name;

现在，让我们创建一个更复杂的场景。假设我们有一个名为 countrylanguage 的表，存储了每个国家使用的官方语言的信息。该表具有 CountryCode 和 Language 列。

要列出城市、它们的国家以及该国家的官方语言，我们需要将 city 与 country 连接，然后将 country 与 countrylanguage 连接。

SELECT
    c.Name AS City,
    co.Name AS Country,
    cl.Language AS OfficialLanguage
FROM
    city AS c
INNER JOIN
    country AS co ON c.CountryCode = co.Code
INNER JOIN
    countrylanguage AS cl ON co.Code = cl.CountryCode
WHERE
    cl.IsOfficial = 'T' -- 假设 'T' 代表官方语言
ORDER BY
    co.Name, c.Name, cl.Language;

在这里：

  • 第一个 INNER JOIN 使用 c.CountryCode = co.Code 将 city 连接到 country。
  • 第二个 INNER JOIN 使用 co.Code = cl.CountryCode 将 country 连接到 countrylanguage。
  • 添加了一个 WHERE 子句来仅过滤官方语言，演示了 WHERE 子句如何与连接配合使用。

2.3 示例 3：结合 GROUP BY 进行统计

INNER JOIN 本质上会过滤掉没有匹配项的行。这意味着如果一个国家存在于 country 表中，但在 city 表中没有对应的条目（可能是新录入的国家或数据错误），那么该国家将不会出现在 INNER JOIN 的结果中。

这种行为可以用来计算或识别确实有匹配项的实体。例如，如果你想知道每个国家有多少个城市，你可以将 INNER JOIN 与 GROUP BY 和 COUNT 结合使用。

SELECT
    co.Name AS Country,
    COUNT(c.ID) AS NumberOfCities
FROM
    country AS co
INNER JOIN
    city AS c ON co.Code = c.CountryCode
GROUP BY
    co.Name
ORDER BY
    NumberOfCities DESC;

这个查询：

  1. 连接了 country 和 city。
  2. 按 country.Name 对结果进行分组。
  3. 计算每个国家的城市 ID 数量。

INNER JOIN 确保结果集中仅包含在 city 表中至少拥有一个城市的国家。拥有零个城市的国家不会出现在这个结果集中。

3. 将 INNER JOIN 与 WHERE 和 ORDER BY 结合

通常，你需要对连接的结果进行过滤或排序。WHERE 子句和 ORDER BY 子句是在连接操作之后应用的。

以客户订单系统为例：检索由 'Alice Smith' 下的订单并按订单日期排序。

SELECT
    o.OrderID,
    o.OrderDate,
    o.TotalAmount,
    c.FirstName,
    c.LastName
FROM
    Orders AS o
INNER JOIN
    Customers AS c ON o.CustomerID = c.CustomerID
WHERE
    c.FirstName = 'Alice' AND c.LastName = 'Smith'
ORDER BY
    o.OrderDate DESC;

此查询首先连接 Orders（订单）和 Customers（客户）表。然后，它过滤连接后的结果，仅显示由 'Alice Smith' 下的订单。最后，它按 OrderDate（订单日期）降序排列这些特定的订单。