SQL 连接类型

使用 Metabase 学习 SQL

免费下载 Metabase，或免费试用 Metabase 云

本文探讨了不同类型的 SQL 连接。如果您是该主题的新手，您可能还会想查阅SQL 连接文章。请注意，连接仅适用于关系型数据库。

SQL 连接类型快速回顾

SQL 连接会指示数据库合并来自不同表的列。我们通常通过将一个表中的外键与另一个表中的主键进行匹配来连接表。例如，products 表中的每条记录在 products.id 字段中都有一个唯一的 ID：这是主键。为了匹配该键，orders 表中的每条记录在 orders.product_id 字段中都有一个产品 ID：这是外键。如果我们要将订单信息与所订购产品的信息合并，我们可以进行内连接

SELECT
  orders.total as total,
  products.title as title
FROM
  orders INNER JOIN products
ON
  orders.product_id = products.id

在连接中，我们使用 Orders.product_id 而不是 Orders.id 是非常重要的：这两个字段都只是数字，因此某些订单 ID 可能会与某些产品 ID 匹配，但这些匹配将毫无意义。

SQL 连接的问题解释

即使我们使用了正确的字段，这里也存在一个针对粗心者的陷阱。很容易检查 Orders 中的每条记录都包含一个产品 ID——对 Orders.product_id 中空值的数量进行计数会返回 0

SELECT
  count(*)
FROM
  orders
WHERE
  orders.product_id IS NULL

| count(*) |
| -------- |
| 0        |

但是，如果事情不总是匹配呢？例如，假设我们正在尝试找出哪些产品没有评论。如果我们查看 reviews 表，它有 1,112 条记录

SELECT
  count(*)
FROM
  reviews

| count(*) |
| -------- |
| 1112     |

每条评论都指向一个产品

SELECT
  count(*)
FROM
  reviews
WHERE
  reviews.product_id IS NULL

| count(*) |
| -------- |
| 0        |

但是每个产品都有评论吗？为了找出答案，我们来数一下产品的数量。

SELECT
  count(*)
FROM
  products

| count(*) |
| -------- |
| 200      |

然后我们可以合并 products 和 reviews 表，并计算结果中不同产品的数量。（在实际操作中，我们可能会使用 SELECT COUNT(DISTINCT product_id) FROM reviews 来获取这个数字，但使用 INNER JOIN 有助于我们说明这个概念。）

SELECT
  count(distinct products.id)
FROM
  products INNER JOIN reviews
ON
  products.id = reviews.product_id

| count(*) |
| -------- |
| 176      |

200 个产品中只有 176 个有评论。因此，如果我们计算每个产品的评论数量，我们将只得到有评论的产品的计数——我们的查询不会告诉我们任何关于没有评论产品的信息，因为内连接在合并表时找不到任何匹配项。此查询演示了问题所在。

SELECT
  products.title as title, count(*) as number_of_reviews
FROM
  products INNER JOIN reviews
ON
  products.id = reviews.product_id
GROUP BY
  products.id
ORDER BY
  number_of_reviews ASC

| products.title            | number_of_reviews |
| ------------------------- | ----------------- |
| Rustic Copper Hat         |                 1 |
| Incredible Concrete Watch |                 1 |
| Practical Aluminum Coat   |                 1 |
| Awesome Aluminum Table    |                 1 |
| ...                       |               ... |

我们已按计数升序排列结果；如这所示，最低计数是 1，而它应该为 0。

外连接 SQL 类型来拯救！

好的：我们知道有多少产品没有评论，但它们是哪些呢？回答这个问题的一种方法是使用称为左外连接（也称为“左连接”）的 SQL 连接类型。这种连接总是至少返回我们提及的第一个表（即左表）中的一条记录。要了解它是如何工作的，假设我们有两个小表，分别名为 paint 和 fabric。paint 表包含三行：

| brand     | color |
| --------- | ----- |
| Premiere  | red   |
| Premiere  | blue  |
| Special   | blue  |

而 fabric 表只包含两行：

| kind   | shade |
| ------ | ----- |
| nylon  | green |
| cotton | blue  |

如果我们对这两个表进行内连接，将 paint.color 与 fabric.shade 匹配，则只有 blue 记录匹配：

SELECT
  *
FROM
  paint INNER JOIN fabric
ON
  paint.color = fabric.shade

| paint.brand | paint.color | fabric.kind | fabric.shade |
| ----------- | ----------- | ----------- | ------------ |
| Premiere    | blue        | cotton      | blue         |
| Special     | blue        | cotton      | blue         |

fabric 表中没有红色，因此 paint 的第一条记录不包含在结果中。同样，paint 中没有绿色，因此 fabric 中的尼龙材料也被丢弃了。

但是，如果我们进行左外连接，数据库会保留左表中每个没有匹配项的记录。由于右表中没有匹配值，SQL 会用 NULL 填充这些列：

SELECT
  *
FROM
  paint LEFT JOIN fabric
ON
  paint.color = fabric.shade

| paint.brand | paint.color | fabric.kind | fabric.shade |
| ----------- | ----------- | ----------- | ------------ |
| Premiere    | red         | NULL        | NULL         |
| Premiere    | blue        | cotton      | blue         |
| Special     | blue        | cotton      | blue         |

保留左表中的所有记录在许多不同情况下都很有用。例如，如果我们想查看哪些颜料没有匹配的面料，我们可以进行左外 SQL 连接：

SELECT
  *
FROM
  paint LEFT OUTER JOIN fabric
ON
  paint.color = fabric.shade

|  paint.brand | paint.color | fabric.kind  | fabric.shade |
| ------------ | ----------- | ------------ | ------------ |
| Premiere     | red         | NULL         | NULL         |
| Premiere     | blue        | cotton       | blue         |
| Special      | blue        | cotton       | blue         |

如果我们只选择右表中值为 NULL 的行，这会更容易阅读：

SELECT
  *
FROM
  paint LEFT OUTER JOIN fabric
ON
  paint.color = fabric.shade
WHERE
  fabric.shade IS NULL

|  paint.brand | paint.color | fabric.kind  | fabric.shade |
| ------------ | ----------- | ------------ | ------------ |
| Premiere     | red         | NULL         | NULL         |

我们可以使用这种技术，通过进行左外连接并只保留 reviews.product_id 已填充为 NULL 的行，来获取没有评论的产品列表：

SELECT
  products.title
FROM
  products LEFT OUTER JOIN reviews
ON
  products.id = reviews.product_id
WHERE
  reviews.product_id IS NULL

| products.title          |
| ----------------------- |
| Small Marble Shoes      |
| Ergonomic Silk Coat     |
| Synergistic Steel Chair |
| ...                     |

那么右外连接 SQL 和全外连接 SQL 呢？

SQL 标准定义了另外两种外连接 SQL 类型，但它们的使用频率要低得多——以至于有些数据库甚至没有实现它们。右外连接的工作方式与左外连接完全相同，只是它始终保留右表中的行，并在没有匹配项时用 NULL 填充左表中的列。很容易看出，通过交换表的顺序，你总是可以使用左外连接而不是右外连接；没有特别的理由偏爱其中一个，但几乎所有人都使用左侧形式，所以我们也建议您这样做。

全外连接保留了两个表中的所有信息。如果左侧记录在右侧没有匹配项，数据库将用 NULL 填充缺失的右侧值；如果右侧记录在左侧没有匹配项，它将填充缺失的左侧值。例如，如果我们对 paints 和 fabrics 进行全外连接，我们会得到：

|  paint.brand | paint.color | fabric.kind  | fabric.shade |
| ------------ | ----------- | ------------ | ------------ |
| Premiere     | red         | NULL         | NULL         |
| Premiere     | blue        | cotton       | blue         |
| NULL         | NULL        | nylon        | green        |
| Special      | blue        | cotton       | blue         |

全外连接偶尔可用于查找两个表之间的重叠，但在我编写 SQL 的二十年里，我只在像这样的教程中使用过它们。

使用哪种 SQL 连接类型？

回顾一下，连接有四种基本类型。内连接只保留匹配的记录，而其他三种类型则用 NULL 填充缺失的值。有些人将左表视为主要或初始表；您使用的连接类型将决定您将从该初始表中返回多少条记录，以及根据您从另一个表所需的列返回的任何附加记录。我们在这里已经看到了例外情况（例如，每个产品都有多条评论），但这表明您有一个很好的主表可以开始使用。

一般来说，您只需要使用内连接和左外连接。您使用哪种连接类型取决于您是否希望在结果中包含不匹配的行。

• 如果您需要在主表中包含不匹配的行，请使用左外连接。
• 如果您不需要不匹配的行，请使用内连接。

要从另一个角度了解抽象 SQL 的连接，请查阅我们关于使用 Metabase 查询构建器进行连接的文章。

SQL 连接的常见问题

使用内 SQL 连接而不是外连接

这可能是最常见的错误。真实数据通常存在空白，当键不匹配时，内连接会在不发出警告的情况下丢弃记录。计算一个表中没有在另一个表中匹配项的行数是一个很好的安全检查；如果存在任何此类情况，您应该考虑使用外连接而不是内连接。

对“无意义的”匹配项使用 SQL 连接

一个人的体重（以千克为单位）和他们上次购买的金额（以美元为单位）都是数字，因此可以通过匹配它们进行连接，但结果可能（很可能）是无意义的。一个不那么微不足道的例子是，当一个表包含多个引用不同表的外键时，这可能导致将患者数据与车辆登记信息连接起来，而不是与预约日期连接。在表中声明外键有助于防止这种情况发生。

混淆数据中的空值与不匹配产生的空值

如果外连接中的一个表包含 NULL 值，我们可能会得到一个列，其中的值缺失，原因可能是它们不在原始数据中，也可能是由于不匹配。根据我们正在尝试解决的问题，这些不同“类型”的 NULL 可能会很重要。