一些数据库之间是存在关联的，编写一个JOIN可以联结多个不同的数据库。

不同数据库之间的一些行列可能存在关联

数据库规范化

在创建数据库时，一定要思考下将如何存储数据。这称为规范化，是大多数 SQL 课程的一个重要组成部分。如果你负责设置新的数据库，则需要详细了解数据库规范化。

数据库规范化需要考虑以下三个要点：

1. 表格存储了逻辑分组的数据吗？
2. 我能在一个位置进行更改，而不是在多个表格中对同一信息作出更改吗？
3. 我能快速高效地访问和操纵数据吗？

这篇文章详细讲解了上述内容。

但是，大多数分析师处理的数据库已经设置了必要的属性。你不需要太思考数据规范化。你只需能够从数据库中获取数据，从而能够绘制图表。这将是这节课的侧重点。

JOIN 语句

JOIN 语句的整个目标是一次能够从多个表格中获取数据。

JOIN 使我们能够从多个表格中获取数据。是既简单，又很强大的功能。

在掌握了 JOIN 语句后，我们还将学习 ON 语句。

JOIN语句实际上可以看做是第二个FROM语句，on代表了两个表格对应数据合并以后贴合的位置。

JOIN语句和ON语句

练习 1

注意，上述结果与你切换 FROM 和 JOIN 中的表格得到的结果一样。此外，= 两边的列顺序并不重要。

练习 2

注意，我们需要在 SELECT 语句中指定某列所来自的每个表格。

实体关系图

你可能还记得，在上节课，我们提到实体关系图 (ERD) 是查看数据库中数据的常见方式。它也是了解如何从多个表格中获取数据的关键要素。

如果能知道 Parch & Posey handy 的 RED 看起来怎样，会比较有帮助，因此我在下面再次提供了该图。你甚至可以打印一份，这样在完成剩下的练习时可以参考。

关系连通图

表格与列

在 Parch & Posey 数据库中，有 5 个表格

1. web_events
2. accounts
3. orders
4. sales_reps
5. region

你将发现，表格中某些列的列名称旁边具有 PK 或 FK，而其他列根本没有标签。

如果你再仔细观察，可能会发现，PK 在每个表格中与第一列相关。PK 表示主键。每个表格都存在主键，它是每行的值都唯一的列。

如果你查看我们的数据库中每个表格的前几行，你会发现这个首个 PK 列始终是唯一的。对于此数据库，它始终称为 id（关系连通的标识），但并非所有数据库都这样。

键

主键 (Primary key ，PK)

主键是特定表格的唯一列，是数据库中的每个表格都必须存在的单列。在我们的每个表格中是第一列，并且这些列都叫做 id，但是并非必须都要这样。通常在大多数数据库中，主键是表格的第一列。主键是数据库实体完整性的一种规则，唯一标识一个实体，取值非空唯一。比如，学生表的学号。

外键 (Foreign key ，FK)

外键是另一个表格中的主键，是数据库参照完整性的一种规则，将两表或者多张表联系起来，取值必须来自参照表的参照字段的值，可为空也可不为空。比如，选课表里的学号。我们可以在上一个 ERD 中看到，外键如下所示：
外键
region_id
account_id
sales_rep_id
每个都与另一个表格的主键相关。下图是一个示例：

主键 - 外键关联性

在上图中，可以看出：

region_id 是外键。
region_id 与 id 相连 - 它是将这两个表格相连的主外键关系。
Crow's foot （喜鹊爪、鱼尾纹记法）表示 FK 实际上可以出现在 sales_reps 表格中的很多行中。
虽然这一行告诉我们 PK 显示在此表格中 id 每行仅出现一次。
主键具有唯一性。有时候，外键具有唯一性，但是并非总是这样。
外键始终与主键相关，并且它们与上述 crow-foot 标记法相关，显示它们可以在特定的表格中出现多次。

依照刚才的表格进行练习

第一题 第二题 第三题

JOIN 回顾总结

我们回顾下你编写的第一个 JOIN 语句。

SELECT orders.*
FROM orders
JOIN accounts
ON orders.account_id = accounts.id;

以下是这两个表格的 ERD：

image.png

注意

注意，我们的 SQL 查询包含两个想要连接的表格：一个来自 FROM，另一个来自 JOIN。然后在 ON 中，我们始终让主键等于外键：

我们按照以下方式连接任何两个表格。

image.png

练习题

参考以上图片。如果我们想连接 sales_reps 和 region 表格，如何操作？

答案

ON sales_reps.region_id = region.id

在这个语句中，表格名称的顺序并不重要，只要把爪子相勾连的索引写成等式就行了。因此，也可以写成

ON region.id = sales_reps.region_id

连接多个表格

可以利用同一逻辑连接多个表格。看看下面的三个表格。

image.png

代码

如果我们想连接所有这三个表格，我们可以采用相同的逻辑。

FROM web_events
JOIN accounts
ON web_events.account_id = accounts.id
JOIN orders
ON accounts.id = orders.account_id

现在，我们的 SELECT 语句可以从所有三个表格中获取数据。同样，JOIN 存储的是表格，ON 是让主键等于外键。

SELECT 语句将需要指定你想从中获取列的表格：

SELECT web_events.channel, accounts.name, orders.total

我们可以继续按照这一流程操作，连接所有要连接的表格。为了提高效率，我们可能不希望这么做，除非需要从所有表格中获取信息。

设置别名

别名

当我们连接表格时，最好能为每个表格设置一个别名。你实际上在算术运算符部分见过类似的列名称别名。

示例：

FROM tablename AS t1
JOIN tablename2 AS t2

之前，你见过如上所示的语句：

SELECT col1 + col2 AS total, col3

经常你可能还会见到没有AS 部分的语句。上述每个语句都可以改写为以下形式，结果依然相同：

FROM tablename t1
JOIN tablename2 t2

以及

SELECT col1 + col2 total, col3

注意

sql的分号是一个语句的结束，多个JOIN之间千万不要加分号。

练习

还是这张图

问题

1. 为与客户名称 Walmart 相关的所有 web_events 创建一个表格。表格应该包含三列：primary_poc、事件时间和每个事件的渠道。此外，你可以选择添加第四列，确保仅选中了 Walmart 事件。

2. 为每个销售代表对应的区域以及相关的客户创建一个表格，最终表格应该包含三列：区域名称、销售代表名称，以及客户名称。根据客户名称按字母顺序 (A-Z) 排序。

3. 提供每个订单的每个区域名称，以及客户名称和订单的单价 (total_amt_usd/total)。最终表格应该包含三列：区域名称、客户名称和单价。少数几个客户的总订单数为 0，因此我除以的是 (total + 0.01) 以确保没有除以 0。

答案

【解析】我们可以直接在列名称（在 SELECT 中）或表格名称（在 FROM 或 JOIN 中）后面写上别名，方法是在要设定别名的列或表格后面直接写上别名。这样可以创建清晰的列名称，虽然计算是用来创建列的，通过使用表格名称别名，代码更高效。’

我们介绍主键和外键时，稍微介绍了这些一对一和一对多关系。注意，传统数据库不允许出现多对多关系，因为这样很快就会破坏架构。这篇文章给出了很好的解释。

数据库中的关系类型对分析师来说并不太重要，但是你需要了解为何要执行不同类型的 JOIN，以及你要从数据库中获取什么数据.

INNER JOIN 和 OUTER JOIN

注意，每个新的 **JOIN ** 语句和 ** INNER JOIN **获取的都是相同的行，通过直接使用 **JOIN ** 就能看到这些行，但是它们可能还会获取其他的行。

如果连接的表格没有匹配的信息，那么就会出现单元格为空的列。这些空的单元格就引出了新的数据类型，叫做 NULL。你将在下节课详细学习 NULL，暂时先理解为任何没有数据的单元格都是 NULL。

INNER JOIN

LEFT JOIN 和 RIGHT JOIN

LEFT JOIN

每个ORDERS 行都可以匹配到ACCOUNTS 的对应行，因为ON语句写明了id的对应关系。

RIGHT JOIN

这里有四个没有订单的ACCOUNT账户，意味着有四行不满足JOIN的条件，因为现在是RIGHT JOIN而不是INNER JOIN，但是这几行也会显示在结果集的底部。由于它们 在左表中没有匹配的行，所以左表中的列不包含这些行的数据，

查询结果：上图的结果底部也有几行没有与订单相匹配的行。

上图的结果底部也有几行没有与订单相匹配的行。

左右JOIN的互换

image.png

这次查询中，如果把 FROM 后面的accounts a和 LEFT JOIN后面的 orders o互换位置，那么左表就会显示出来额外几行的内容了。

也就是类似这样的等价情况

国际通用的习惯是大家都用LEFT JOIN，遵循规则可以帮助他人更好阅读。

注意，到目前为止介绍的每个 JOIN 都是 INNER JOIN，即我们仅获取在两个表格中都匹配存在的行。

新的 JOIN 使我们能够获取可能仅在其中一个表格中存在的行。这就会导致一种新的数据类型，叫做 NULL。我们将在下节课详细讲解这一数据类型。

注意

你可能见过以下 SQL 语法

LEFT OUTER JOIN

或

RIGHT OUTER JOIN

这些命令和我们学过的 LEFT JOIN 和 RIGHT JOIN 完全一样。

OUTER JOIN

最后一种连接类型是外连接，它将返回内连接的结果，以及被连接的表格中没有匹配的行。

这种连接返回的是与两个表格中的某个表格不匹配的行，完整的外连接用例非常罕见。

你可以在此处查看外连接示例，并在此处查看罕见使用情况说明。由于这种连接的使用情况很少见，因此我们将不消费时间讨论了。

和上面的相似，你可能还会看到 FULL OUTER JOIN，它和 OUTER JOIN 一样。

练习

练习1

练习1

练习2

练习2 答案

练习3

问题和答案

【解析】
因为这是 JOIN（实际上是 INNER JOIN），我们只能获得在两个表格中都出现了的行。
因此，生成的表格将看起来像右侧表格，并获取列 countryName。
因为1、2、3 和 4 是两个表格中的 countryid，因此将同时获取这些信息。countryid 5 和 6 仅出现在了 Country 表格中，因此将忽略这两行。

练习4

结果匹配

【解析】
SELECT 语句中标注的每个元素都对应一列。
行数将和 JOIN 语句的一样，但是我们还将获得在 Country 表格中但不在 State 表格中的其他两列：Sri Lanka 和 Brazil。 '

LEFT JOIN 和 RIGHT JOIN 解决方案 梳理

这一部分讲解的是上一部分的最后两个问题，首先再看看我们处理的这两个表格：

INNER JOIN 问题

这些问题旨在让你对 LEFT JOIN 和 INNER JOIN 的原理有所了解，然后才能使用它们处理更复杂的问题。

对于如下所示的 INNER JOIN：

SELECT c.countryid, c.countryName, s.stateName
FROM Country c
JOIN State s
ON c.countryid = s.countryid;

我们实际上是将两个表格中匹配的主键和外键行连接到一起，如下图所示。

生成的表格将如下所示：

countryid	countryName	stateName
1	India	Maharashtra
1	India	Punjab
2	Nepal	Kathmandu
3	United States	California
3	United States	Texas
4	Canada	Alberta

LEFT JOIN 问题

这些问题旨在让你对 LEFT JOIN 和 INNER JOIN 的原理有所了解，然后才能使用它们处理更复杂的问题。

对于如下所示的 LEFT JOIN：

SELECT c.countryid, c.countryName, s.stateName
FROM Country c
LEFT JOIN State s
ON c.countryid = s.countryid;

和之前一样，我们实际上是将两个表格中匹配的主键和外键行连接到一起，但是我们还从 Country 表格中获取额外的行，即使它们在 State 表格中没有匹配的项。因此，我们获取的是 INNER JOIN 生成的所有行，同时还获取 FROM 中的表格里的其他行。

生成的表格将如下所示：

countryid	countryName	stateName
1	India	Maharashtra
1	India	Punjab
2	Nepal	Kathmandu
3	United States	California
3	United States	Texas
4	Canada	Alberta
5	Sri Lanka	NULL
6	Brazil	NULL

最后的 LEFT JOIN 注意事项

如果我们翻转表格顺序的话，实际上和 JOIN 语句获得的结果完全一样：

SELECT c.countryid, c.countryName, s.stateName
FROM State s
LEFT JOIN Country c
ON c.countryid = s.countryid;

这是因为如果 State 位于左侧表格中，则所有行再次出现在右侧表格中。

image.png

生成的表格将如下所示：

countryid	countryName	stateName
1	India	Maharashtra
1	India	Punjab
2	Nepal	Kathmandu
3	United States	California
3	United States	Texas
4	Canada	Alberta

JOIN 和过滤

介绍一个简单的规则：当数据库执行该查询时，它先执行连接和 ON 条件中的指令。将其看做构建新的结果集，然后使用 WHERE 条件来过滤该结果集。

这个示例是LEFT JOIN这一事实很重要。因为内连接仅返回两个表格中匹配的行，将此过滤器移到内连接的 ON 条件中将与使其保留在 WHERE 条件中产生的结果一样。

预先筛选目标id，同时保留其他结果跟在后面，可以看见大量订单没有与之匹配

练习

问题

1. 为每个销售代表对应的区域以及相关的客户创建一个表格，这次仅针对 Midwest 区域。最终表格应该包含三列：区域名称、销售代表姓名，以及客户名称。根据客户名称按字母顺序 (A-Z) 排序。

2. 为每个销售代表对应的区域以及相关的客户创建一个表格，这次仅针对 Midwest 区域，并且销售代表的名字以 S 开头。最终表格应该包含三列：区域名称、销售代表姓名，以及客户名称。根据客户名称按字母顺序 (A-Z) 排序。

3. 为每个销售代表对应的区域以及相关的客户创建一个表格，这次仅针对 Midwest 区域，并且销售代表的姓以 K 开头。最终表格应该包含三列：区域名称、销售代表姓名，以及客户名称。根据客户名称按字母顺序 (A-Z) 排序。

4. 提供每个订单的每个区域的名称，以及客户名称和所支付的单价 (total_amt_usd/total)。但是，只针对标准订单数量超过 100 的情况提供结果。最终表格应该包含三列：区域名称、客户名称和单价。为了避免除以 0 个订单，这里可以在分子上加上 0.01(total_amt_usd/(total+0.01)。

5. 提供每个订单的每个区域的名称，以及客户名称和所支付的单价 (total_amt_usd/total)。但是，只针对标准订单数量超过 100 且广告纸数量超过 50 的情况提供结果。最终表格应该包含三列：区域名称、客户名称和单价。按照最低的单价在最之前排序。为了避免除以 0 个订单，这里可以在分子上加上 0.01(total_amt_usd/(total+0.01)。

6. 提供每个订单的每个区域的名称，以及客户名称和所支付的单价 (total_amt_usd/total)。但是，只针对标准订单数量超过 100 且广告纸数量超过 50 的情况提供结果。最终表格应该包含三列：区域名称、客户名称和单价。按照最高的单价在最之前排序。为了避免除以 0 个订单，这里可以在分子上加上 0.01(total_amt_usd/(total+0.01)。

7. account id 为 1001 的客户使用了哪些不同的渠道。最终表格应该包含 2 列：客户名称和不同的渠道。你可以尝试使用 SELECT DISTINCT 使结果仅显示唯一的值。

8. 找出发生在 2015 年的所有订单。最终表格应该包含 4 列：occurred_at、account name、order total 和 order total_amt_usd。

答案

解决方案

1. 为每个销售代表对应的区域以及相关的客户创建一个表格，这次仅针对 Midwest 区域。最终表格应该包含三列：区域名称、销售代表姓名，以及客户名称。根据客户名称按字母顺序 (A-Z) 排序。

SELECT r.name region, s.name rep, a.name account
FROM sales_reps s
JOIN region r
ON s.region_id = r.id
JOIN accounts a
ON a.sales_rep_id = s.id
WHERE r.name = 'Midwest'
ORDER BY a.name;

2. 为每个销售代表对应的区域以及相关的客户创建一个表格，这次仅针对 Midwest 区域，并且销售代表的名字以 S 开头。最终表格应该包含三列：区域名称、销售代表姓名，以及客户名称。根据客户名称按字母顺序 (A-Z) 排序。

SELECT r.name region, s.name rep, a.name account
FROM sales_reps s
JOIN region r
ON s.region_id = r.id
JOIN accounts a
ON a.sales_rep_id = s.id
WHERE r.name = 'Midwest' AND s.name LIKE 'S%'
ORDER BY a.name;

3. 为每个销售代表对应的区域以及相关的客户创建一个表格，这次仅针对 Midwest 区域，并且销售代表的姓以 K 开头。最终表格应该包含三列：区域名称、销售代表姓名，以及客户名称。根据客户名称按字母顺序 (A-Z) 排序。

SELECT r.name region, s.name rep, a.name account
FROM sales_reps s
JOIN region r
ON s.region_id = r.id
JOIN accounts a
ON a.sales_rep_id = s.id
WHERE r.name = 'Midwest' AND s.name LIKE '% K%'
ORDER BY a.name;

4. 提供每个订单的每个区域的名称，以及客户名称和所支付的单价 (total_amt_usd/total)。但是，只针对标准订单数量超过 100 的情况提供结果。最终表格应该包含三列：区域名称、客户名称和单价。为了避免除以 0 个订单，这里可以在分子上加上 0.01(total_amt_usd/(total+0.01)。

SELECT r.name region, a.name account, o.total_amt_usd/(o.total + 0.01) unit_price
FROM region r
JOIN sales_reps s
ON s.region_id = r.id
JOIN accounts a
ON a.sales_rep_id = s.id
JOIN orders o
ON o.account_id = a.id
WHERE o.standard_qty > 100;

5. 提供每个订单的每个区域的名称，以及客户名称和所支付的单价 (total_amt_usd/total)。但是，只针对标准订单数量超过 100 且广告纸数量超过 50 的情况提供结果。最终表格应该包含三列：区域名称、客户名称和单价。按照最低的单价在最之前排序。为了避免除以 0 个订单，这里可以在分子上加上 0.01(total_amt_usd/(total+0.01)。

SELECT r.name region, a.name account, o.total_amt_usd/(o.total + 0.01) unit_price
FROM region r
JOIN sales_reps s
ON s.region_id = r.id
JOIN accounts a
ON a.sales_rep_id = s.id
JOIN orders o
ON o.account_id = a.id
WHERE o.standard_qty > 100 AND o.poster_qty > 50
ORDER BY unit_price;

6. 提供每个订单的每个区域的名称，以及客户名称和所支付的单价 (total_amt_usd/total)。但是，只针对标准订单数量超过 100 且广告纸数量超过 50 的情况提供结果。最终表格应该包含三列：区域名称、客户名称和单价。按照最高的单价在最之前排序。为了避免除以 0 个订单，这里可以在分子上加上 0.01(total_amt_usd/(total+0.01)。

SELECT r.name region, a.name account, o.total_amt_usd/(o.total + 0.01) unit_price
FROM region r
JOIN sales_reps s
ON s.region_id = r.id
JOIN accounts a
ON a.sales_rep_id = s.id
JOIN orders o
ON o.account_id = a.id
WHERE o.standard_qty > 100 AND o.poster_qty > 50
ORDER BY unit_price DESC;

7. account id 为 1001 的客户使用了哪些不同的渠道。最终表格应该包含 2 列：客户名称和不同的渠道。你可以尝试使用 SELECT DISTINCT 使结果仅显示唯一的值。

SELECT DISTINCT a.id, w.channel
FROM accounts a
RIGHT JOIN web_events w
ON a.id = w.account_id
WHERE a.id = '1001';

8. 找出发生在 2015 年的所有订单。最终表格应该包含 4 列：occurred_at、account name、order total 和 order total_amt_usd。

SELECT w.occurred_at, a.name, o.total, o.total_amt_usd
FROM accounts a
JOIN orders o
ON o.account_id = a.id
JOIN web_events w
ON a.id = w.account_id
WHERE w.occurred_at BETWEEN '01-01-2015' AND '01-01-2016'
ORDER BY w.occurred_at DESC;

总结

主键和外键

你学习了在连接数据库中的表格时与主键和外键相关的关键要素：

• 主键 - 对于表格中的每行都是唯一的。主键通常是数据库中的第一列（就像 Parch & Posey 数据库中每个表格的 id 列）。

• 外键 - 是出现在另一个表格中的主键，允许行不是唯一的行。

数据库的数据设置非常重要，但通常不是数据分析师的职责。这一过程称为数据库规范化。

JOIN

在这节课，你学习了如何使用 JOIN 组合多个表格的数据。你将最常用到的三个 JOIN 语句为：

1. JOIN - 一种 INNER JOIN，仅获取在两个表格中都存在的数据。
2. LEFT JOIN - 用于获取 FROM 中的表格中的所有行，即使它们不存在于 JOIN 语句中。
3. RIGHT JOIN - 用于获取 JOIN 中的表格中的所有行，即使它们不存在于 FROM 语句中。

还有几个没有讲解的高级 JOIN，它们仅适用于非常特定的情况。UNION 和 UNION ALL、CROSS JOIN 和比较难的 SELF JOIN。这些内容比较深奥，这门课程不会再过多介绍，但是有必要知道这些连接方法是存在的，它们在特殊情况下比较实用。

别名

你学习了可以使用 AS 或直接对表格和列设定别名。这样可以减少要输入的字符数，同时确保列标题可以描述表格中的数据。

后续内容

下节课的主题是聚合数据。你已经学到了很多知识，但是可能依然觉得 SQL 与统计学和使用 Excel 等平台不太相关。聚合函数将使你能够编写更复杂的 SQL 查询，并回答以下等问题：

• 哪个渠道产生的收入更高？
• 哪个客户具有商品量最多的订单？
• 哪个销售代表的订单量最多？或订单量最少？他们有多少订单？