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 可能很重要。