SQL 连接类型

本文着眼于不同类型的 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 连接类型，但它们的使用频率要低得多——以至于有些数据库甚至没有实现它们。右外连接的工作方式与左外连接完全相同，除了它始终保留右侧表格中的行，并在没有匹配项时用 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 混淆

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