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      |

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

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填充缺失的右侧值，如果右侧的记录在左侧没有匹配，它将用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填充缺失值。有些人将左表视为主表或初始表；您使用的连接类型将决定您将返回初始表中的多少记录，以及根据您想要的另一表的列返回的任何额外记录。我们已经在这里看到了一些例外（例如，每个产品都有多个评论），但这是一个很好的迹象，表明您有一个很好的主表可以开始。

通常，您只需要使用内部连接和左外连接。您使用的连接类型取决于您是否希望将不匹配的行包括在结果中

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

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

SQL连接的常见问题

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

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

在“匹配”不具意义的“匹配”上使用SQL连接

一个人的体重（千克）和他们的最后一次购买价值（美元）都是数字，因此可以通过匹配它们来进行连接，但结果（可能）是没有意义的。一个不太轻浮的例子是，当一张表中包含几个引用不同表的多个外键时，这可能导致将患者数据与车辆注册而不是预约日期连接起来。在表中声明外键可以帮助防止这种情况。

混淆数据中的NULL值与不匹配的NULL值

如果在外部连接中的一个表中包含NULL，我们最终可能会得到一个列，其值缺失，因为它们既不在原始数据中，也因为不匹配。根据我们试图解决的问题，这些不同的“风味”的NULL可能很重要。