全部标签
写点什么
学习MySQL开源云计算行业资讯算法Python人工智能区块链Linux前端个人成长面试架构师编程企业动态新基建敏捷安全读书笔记高效工作团队管理创业活动专区生涯规划知识管理运维产品经理 查看更多

「SQL 数据分析系列」10. 重谈连接

用户头像
数据与智能
关注
发布于: 1 小时前
「SQL数据分析系列」10. 重谈连接

写在前面：

大家好，我是强哥，一个热爱分享的技术狂。目前已有 12 年大数据与 AI 相关项目经验， 10 年推荐系统研究及实践经验。平时喜欢读书、暴走和写作。

业余时间专注于输出大数据、AI 等相关文章，目前已经输出了 40 万字的推荐系统系列精品文章，今年 6 月底会出版「构建企业级推荐系统：算法、工程实现与案例分析」一书。如果这些文章能够帮助你快速入门，实现职场升职加薪，我将不胜欢喜。

想要获得更多免费学习资料或内推信息，一定要看到文章最后喔。

内推信息

如果你正在看相关的招聘信息，请加我微信：liuq4360，我这里有很多内推资源等着你，欢迎投递简历。

免费学习资料

如果你想获得更多免费的学习资料，请关注同名公众号【数据与智能】，输入“资料”即可！

学习交流群

如果你想找到组织，和大家一起学习成长，交流经验，也可以加入我们的学习成长群。群里有老司机带你飞，另有小哥哥、小姐姐等你来勾搭！加小姐姐微信：epsila，她会带你入群。

第十章重谈连接

到目前为止，我在第五章中介绍过内连接的概念——你应该已经熟悉了。本章重点介绍连接表的其他方法，包括外连接（outer join）和交叉连接（cross join）。

外连接

迄今为止，前面所有包含多个表的示例中，我们并没有考虑连接条件可能无法与表中所有行匹配的问题。例如，inventory 表为每个可供租赁的电影存储一行数据，但是在 film 表的 1000 行数据中，只有 985 行在 inventory 表中存在一行或多行信息，也就是说，其余 42 部电影是不能出租的（可能是还没有上映的新片），所以在 inventory 表中是找不到这些电影的 ID 的。下面的查询通过连接这两个表来统计每个电影的可用拷贝数：

mysql> SELECT f.film_id, f.title, count(*) num_copies

-> FROM film f 

-> INNER JOIN inventory i

-> ON f.film_id = i.film_id 

-> GROUP BY f.film_id, f.title;

+---------+-----------------------------+------------+

| film_id | title | num_copies |

+---------+-----------------------------+------------+

| 1 | ACADEMY DINOSAUR | 8 |

| 2 | ACE GOLDFINGER | 3 |

| 3 | ADAPTATION HOLES | 4 |

| 4 | AFFAIR PREJUDICE | 7 |

...

| 13 | ALI FOREVER | 4 |

| 15 | ALIEN CENTER | 6 |

...

| 997 | YOUTH KICK | 2 |

| 998 | ZHIVAGO CORE | 2 |

| 999 | ZOOLANDER FICTION | 5 |

| 1000 | ZORRO ARK | 8 |

+---------+-----------------------------+------------+

958 rows in set (0.02 sec)

虽然你可能期望返回 1000 行（每个电影一行）数据，但查询只返回了 958 行。这是因为查询使用内连接，它只返回满足连接条件的行。例如，电影 Alice Fantasia（film_id 14）不会出现在结果中，因为它在 inventory 表中没有相关行记录。

如果希望不论 inventory 表中有没有相关记录，查询都返回所有 1000 部电影，可以使用外连接，这实际上使连接条件成为可选条件：

mysql> SELECT f.film_id, f.title, count(i.inventory_id) num_copies 

-> FROM film f 

-> LEFT OUTER JOIN inventory i 

-> ON f.film_id = i.film_id 

-> GROUP BY f.film_id, f.title;

+---------+-----------------------------+------------+

| film_id | title | num_copies |

+---------+-----------------------------+------------+

| 1 | ACADEMY DINOSAUR | 8 |

| 2 | ACE GOLDFINGER | 3 |

| 3 | ADAPTATION HOLES | 4 |

| 4 | AFFAIR PREJUDICE | 7 |

...

| 13 | ALI FOREVER | 4 |

| 14 | ALICE FANTASIA | 0 |

| 15 | ALIEN CENTER | 6 |

...

| 997 | YOUTH KICK | 2 |

| 998 | ZHIVAGO CORE | 2 |

| 999 | ZOOLANDER FICTION | 5 |

| 1000 | ZORRO ARK | 8 |

+---------+-----------------------------+------------+

1000 rows in set (0.01 sec)

如你所见，查询现在返回 film 表中所有 1000 行数据，其中 42 行（包括 Alice Fantasia）在 num_copies 列中的值为 0，这表示没有库存（在 inventory 表中无相关数据）。

以下是对该查询早期版本所做更改的描述：

• 连接定义从内部（inner）更改为左外部（left outer），表示服务器在连接成功的情况下将连接左侧表中的所有行包含进来（在本例中为 film 表），然后包括连接右侧表中的列（inventory 表）。

• num_copies 列定义从 count(*)更改为 count(i.inventory_id)，表示统计 inventory.inventory_id 列的非空值数目。

接下来，让我们删除 group by 子句并过滤大部分行，以便更清楚地看到内连接和外连接之间的差异。下面是一个使用内连接和过滤条件的查询，该查询仅返回几个电影的行数据：

mysql> SELECT f.film_id, f.title, i.inventory_id

-> FROM film f 

-> INNER JOIN inventory i

-> ON f.film_id = i.film_id 

-> WHERE f.film_id BETWEEN 13 AND 15;

+---------+--------------+--------------+

| film_id | title | inventory_id |

+---------+--------------+--------------+

| 13 | ALI FOREVER | 67 |

| 13 | ALI FOREVER | 68 |

| 13 | ALI FOREVER | 69 |

| 13 | ALI FOREVER | 70 |

| 15 | ALIEN CENTER | 71 |

| 15 | ALIEN CENTER | 72 |

| 15 | ALIEN CENTER | 73 |

| 15 | ALIEN CENTER | 74 |

| 15 | ALIEN CENTER | 75 |

| 15 | ALIEN CENTER | 76 |

+---------+--------------+--------------+

10 rows in set (0.00 sec)

结果表明，库存中有 4 份 Ali Forever 和 6 份 Alien Center 的副本（可供租赁）。以下是使用外连接的相同查询：

mysql> SELECT f.film_id, f.title, i.inventory_id 

-> FROM film f 

-> LEFT OUTER JOIN inventory i 

-> ON f.film_id = i.film_id 

-> WHERE f.film_id BETWEEN 13 AND 15;

+---------+----------------+--------------+

| film_id | title | inventory_id |

+---------+----------------+--------------+

| 13 | ALI FOREVER | 67 |

| 13 | ALI FOREVER | 68 |

| 13 | ALI FOREVER | 69 |

| 13 | ALI FOREVER | 70 |

| 14 | ALICE FANTASIA | NULL |

| 15 | ALIEN CENTER | 71 |

| 15 | ALIEN CENTER | 72 |

| 15 | ALIEN CENTER | 73 |

| 15 | ALIEN CENTER | 74 |

| 15 | ALIEN CENTER | 75 |

| 15 | ALIEN CENTER | 76 |

+---------+----------------+--------------+

11 rows in set (0.00 sec)

Ali Forever 和 Alien Center 的结果相同，但是多出了 Alice Fantasia 这一新行，它的 inventory.inventory_id 列数据是空值 null。此示例演示了外连接如何在不限制查询返回的行数的情况下是如何添加列值的。如果连接条件失败（如 Alice Fantasia 的情况），则从外连接表检索到的任何列都将为空值 null。

左外连接和右外连接

上一节中，每个外连接示例里我都指定了 left outer join。关键字 left 表示该连接左侧的表决定结果集中的行数，而右侧的表用于为找到的匹配项提供列值。但其实你也可以指定 right outer join，在这种情况下，连接右侧的表负责确定结果集中的行数，而左侧的表用于为之提供列值。

以下是上一节中最后一个查询的变体，使用 right outer join 而不是 left outer join：

mysql> SELECT f.film_id, f.title, i.inventory_id 

-> FROM inventory i 

-> RIGHT OUTER JOIN film f

-> ON f.film_id = i.film_id 

-> WHERE f.film_id BETWEEN 13 AND 15;

+---------+----------------+--------------+

| film_id | title | inventory_id |

+---------+----------------+--------------+

| 13 | ALI FOREVER | 67 |

| 13 | ALI FOREVER | 68 |

| 13 | ALI FOREVER | 69 |

| 13 | ALI FOREVER | 70 |

| 14 | ALICE FANTASIA | NULL |

| 15 | ALIEN CENTER | 71 |

| 15 | ALIEN CENTER | 72 |

| 15 | ALIEN CENTER | 73 |

| 15 | ALIEN CENTER | 74 |

| 15 | ALIEN CENTER | 75 |

| 15 | ALIEN CENTER | 76 |

+---------+----------------+--------------+

11 rows in set (0.00 sec)


请记住，上面两个版本的查询执行的都是外连接，而关键字 left 和 right 只是告诉服务器哪个表中的数据可以不足。因此如果你想将表 A 和 B 外连接，得到 A 的所有行数据和 B 中匹配列的额外数据，可以指定 A left outer join B 或 B right outer join A。

注意：由于很少会遇到右外连接，而且并非所有数据库服务器都支持它们，因此我建议使用左外连接。outer 关键字是可选的，因此你可以指定 A left join B，但为了使代码更清晰，我建议包含 outer 关键字。

三路外连接

在某些情况下，你可能需要将一个表与另外两个表进行外连接。例如，可以拓展上一节中的查询，以包含来自 rental 表的数据：

mysql> SELECT f.film_id, f.title, i.inventory_id, r.rental_date

-> FROM film f

-> LEFT OUTER JOIN inventory i

-> ON f.film_id = i.film_id

-> LEFT OUTER JOIN rental r

-> ON i.inventory_id = r.inventory_id

-> WHERE f.film_id BETWEEN 13 AND 15;

+---------+----------------+--------------+---------------------+

| film_id | title | inventory_id | rental_date |

+---------+----------------+--------------+---------------------+

| 13 | ALI FOREVER | 67 | 2005-07-31 18:11:17 |

| 13 | ALI FOREVER | 67 | 2005-08-22 21:59:29 |

| 13 | ALI FOREVER | 68 | 2005-07-28 15:26:20 |

| 13 | ALI FOREVER | 68 | 2005-08-23 05:02:31 |

| 13 | ALI FOREVER | 69 | 2005-08-01 23:36:10 |

| 13 | ALI FOREVER | 69 | 2005-08-22 02:12:44 |

| 13 | ALI FOREVER | 70 | 2005-07-12 10:51:09 |

| 13 | ALI FOREVER | 70 | 2005-07-29 01:29:51 |

| 13 | ALI FOREVER | 70 | 2006-02-14 15:16:03 |

| 14 | ALICE FANTASIA | NULL | NULL |

| 15 | ALIEN CENTER | 71 | 2005-05-28 02:06:37 |

| 15 | ALIEN CENTER | 71 | 2005-06-17 16:40:03 |

| 15 | ALIEN CENTER | 71 | 2005-07-11 05:47:08 |

| 15 | ALIEN CENTER | 71 | 2005-08-02 13:58:55 |

| 15 | ALIEN CENTER | 71 | 2005-08-23 05:13:09 |

| 15 | ALIEN CENTER | 72 | 2005-05-27 22:49:27 |

| 15 | ALIEN CENTER | 72 | 2005-06-19 13:29:28 |

| 15 | ALIEN CENTER | 72 | 2005-07-07 23:05:53 |

| 15 | ALIEN CENTER | 72 | 2005-08-01 05:55:13 |

| 15 | ALIEN CENTER | 72 | 2005-08-20 15:11:48 |

| 15 | ALIEN CENTER | 73 | 2005-07-06 15:51:58 |

| 15 | ALIEN CENTER | 73 | 2005-07-30 14:48:24 |

| 15 | ALIEN CENTER | 73 | 2005-08-20 22:32:11 |

| 15 | ALIEN CENTER | 74 | 2005-07-27 00:15:18 |

| 15 | ALIEN CENTER | 74 | 2005-08-23 19:21:22 |

| 15 | ALIEN CENTER | 75 | 2005-07-09 02:58:41 |

| 15 | ALIEN CENTER | 75 | 2005-07-29 23:52:01 |

| 15 | ALIEN CENTER | 75 | 2005-08-18 21:55:01 |

| 15 | ALIEN CENTER | 76 | 2005-06-15 08:01:29 |

| 15 | ALIEN CENTER | 76 | 2005-07-07 18:31:50 |

| 15 | ALIEN CENTER | 76 | 2005-08-01 01:49:36 |

| 15 | ALIEN CENTER | 76 | 2005-08-17 07:26:47 |

+---------+----------------+--------------+---------------------+

32 rows in set (0.01 sec)

结果包括库存中所有电影的租赁情况，但是电影 Alice Fantasia 对于两个外连接表的列都是空值 null。

交叉连接

我在第五章中介绍过笛卡尔积的概念，它本质上是不指定任何连接条件的情况下多表连接的结果。笛卡尔积在不经意的情况下使用得相当频繁（例如，忘记将连接条件添加到 from 子句中）xv’s’s’s’s’s’s’s’s’s’s’s’s’s’s’s’s’s’s，否则人们一般不会经常有意识地用到它。但是如果你的确要生成两个表的笛卡尔积，那么你应该指定交叉连接，如下所示：

mysql> SELECT c.name category_name, l.name language_name

-> FROM category c

-> CROSS JOIN language l;

+---------------+---------------+

| category_name | language_name |

+---------------+---------------+

| Action | English |

| Action | Italian |

| Action | Japanese |

| Action | Mandarin |

| Action | French |

| Action | German |

| Animation | English |

| Animation | Italian |

| Animation | Japanese |

| Animation | Mandarin |

| Animation | French |

| Animation | German |

...

| Sports | English |

| Sports | Italian |

| Sports | Japanese |

| Sports | Mandarin |

| Sports | French |

| Sports | German |

| Travel | English |

| Travel | Italian |

| Travel | Japanese |

| Travel | Mandarin |

| Travel | French |

| Travel | German |

+---------------+---------------+

96 rows in set (0.00 sec)


这个查询生成 category 和 language 表的笛卡尔积，得到 96 行（16 个 category 行×6 个 language 行）数据。你既然知道了交叉连接是什么，以及如何指定它，那么它是用来做什么的呢？大多数 SQL 书籍都会先描述交叉连接是什么，然后告诉你它很少有用，但是我也有发现交叉连接非常有用的情况。

第九章中，我讨论了如何使用子查询构造表。我使用的示例演示了如何构建一个可以连接到其他表的三行表。下面是示例中构造的表：

mysql> SELECT 'Small Fry' name, 0 low_limit, 74.99 high_limit

-> UNION ALL

-> SELECT 'Average Joes' name, 75 low_limit, 149.99 high_limit

-> UNION ALL

-> SELECT 'Heavy Hitters' name, 150 low_limit, 9999999.99 high_limit;

+---------------+-----------+------------+

| name | low_limit | high_limit |

+---------------+-----------+------------+

| Small Fry | 0 | 74.99 |

| Average Joes | 75 | 149.99 |

| Heavy Hitters | 150 | 9999999.99 |

+---------------+-----------+------------+

3 rows in set (0.00 sec)


虽然这正是根据电影总付款将客户分为三组所需要的表，但如果你需要生成一个大表，那么使用集合操作符 union all 合并单行表的策略可能就不是很有用了。

例如，假设你希望创建一个查询，为 2020 年的每一天生成一行，但数据库中没有包含每天一行的表。使用第九章示例中的策略，可以执行以下操作：

SELECT '2020-01-01' dt

UNION ALL

SELECT '2020-01-02' dt

UNION ALL

SELECT '2020-01-03' dt

UNION ALL

...

...

...

SELECT '2020-12-29' dt

UNION ALL

SELECT '2020-12-30' dt

UNION ALL

SELECT '2020-12-31' dt

构建一个将 366 个查询的结果合并在一起的查询还是蛮无聊的，因此可能需要一个不同的策略。试想一下：首先通过单列生成 366 行的表，单列包含 0 到 366 中的某个数，然后将这个天数加上 2020 年 1 月 1 日，这样做又会如何呢？下面是一种可能生成这样一个表的方法：

mysql> SELECT ones.num + tens.num + hundreds.num

-> FROM

-> (SELECT 0 num UNION ALL

-> SELECT 1 num UNION ALL

-> SELECT 2 num UNION ALL

-> SELECT 3 num UNION ALL

-> SELECT 4 num UNION ALL

-> SELECT 5 num UNION ALL

-> SELECT 6 num UNION ALL

-> SELECT 7 num UNION ALL

-> SELECT 8 num UNION ALL

-> SELECT 9 num) ones

-> CROSS JOIN

-> (SELECT 0 num UNION ALL

-> SELECT 10 num UNION ALL

-> SELECT 20 num UNION ALL

-> SELECT 30 num UNION ALL

-> SELECT 40 num UNION ALL

-> SELECT 50 num UNION ALL

-> SELECT 60 num UNION ALL

-> SELECT 70 num UNION ALL

-> SELECT 80 num UNION ALL

-> SELECT 90 num) tens

-> CROSS JOIN

-> (SELECT 0 num UNION ALL

-> SELECT 100 num UNION ALL

-> SELECT 200 num UNION ALL

-> SELECT 300 num) hundreds;

+------------------------------------+

| ones.num + tens.num + hundreds.num |

+------------------------------------+

| 0 |

| 1 |

| 2 |

| 3 |

| 4 |

| 5 |

| 6 |

| 7 |

| 8 |

| 9 |

| 10 |

| 11 |

| 12 |

...

...

...

| 391 |

| 392 |

| 393 |

| 394 |

| 395 |

| 396 |

|397 |

| 398 |

| 399 |

+------------------------------------+

400 rows in set (0.00 sec)

如果生成三个集合{0、1、2、3、4、5、6、7、8、9}、{0、10、20、30、40、50、60、70、80、90}和{0、100、200、300}的笛卡尔积，并将三列相加，则得到一个 400 行的结果集，它包含 0 到 399 之间的所有数字。虽然这比要生成 2020 年天数的集合所需的 366 行数据要多，但其实去掉多余的行是很容易的，我随后会展示这一点。

下一步是将这组数字转换成一组日期。为此，我将使用 date_add()函数将结果集中的每个数字加上 2020 年 1 月 1 日。然后我将添加一个过滤条件，以排除 2021 年的日期：

mysql> SELECT DATE_ADD('2020-01-01',

-> INTERVAL (ones.num + tens.num + hundreds.num) DAY) dt

-> FROM

-> (SELECT 0 num UNION ALL 

-> SELECT 1 num UNION ALL 

-> SELECT 2 num UNION ALL

-> SELECT 3 num UNION ALL

-> SELECT 4 num UNION ALL

-> SELECT 5 num UNION ALL

-> SELECT 6 num UNION ALL

-> SELECT 7 num UNION ALL

-> SELECT 8 num UNION ALL

-> SELECT 9 num) ones

-> CROSS JOIN

-> (SELECT 0 num UNION ALL

-> SELECT 10 num UNION ALL

-> SELECT 20 num UNION ALL

-> SELECT 30 num UNION ALL

-> SELECT 40 num UNION ALL

-> SELECT 50 num UNION ALL

-> SELECT 60 num UNION ALL

-> SELECT 70 num UNION ALL

-> SELECT 80 num UNION ALL

-> SELECT 90 num) tens

-> CROSS JOIN

-> (SELECT 0 num UNION ALL

-> SELECT 100 num UNION ALL

-> SELECT 200 num UNION ALL

-> SELECT 300 num) hundreds

-> WHERE DATE_ADD('2020-01-01',

-> INTERVAL (ones.num + tens.num + hundreds.num) DAY) < '2021-01-01'

-> ORDER BY 1;

+------------+

| dt |

+------------+

| 2020-01-01 |

| 2020-01-02 |

| 2020-01-03 |

| 2020-01-04 |

| 2020-01-05 |

| 2020-01-06 |

| 2020-01-07 |

| 2020-01-08 |

...

...

...

| 2020-02-26 |

| 2020-02-27 |

| 2020-02-28 |

| 2020-02-29 |

| 2020-03-01 |

| 2020-03-02 |

| 2020-03-03 |

...

...

...

| 2020-12-24 |

| 2020-12-25 |

| 2020-12-26 |

| 2020-12-27 |

| 2020-12-28 |

| 2020-12-29 |

| 2020-12-30 |

| 2020-12-31 |

+------------+

366 rows in set (0.03 sec)

这种方法的好处是在无需人工干预的情况下，结果集会自动包含额外的闰日（2 月 29 日），因为数据库服务器基于 2020 年 1 月 1 日加上 59 天就可以计算出来。

现在你有了构造 2020 年所有天数的方法，那么要怎么使用它呢？你可能会被要求生成一份报告，显示 2020 年的每一天以及那一天的电影租赁数量。报告需要包括一年中的每一天，包括没有电影租赁操作的日子。查询可能是这样的（这里使用 2005 年来匹配 rental 表中的数据）：

mysql> SELECT days.dt, COUNT(r.rental_id) num_rentals

-> FROM rental r

-> RIGHT OUTER JOIN

-> (SELECT DATE_ADD('2005-01-01',

-> INTERVAL (ones.num + tens.num + hundreds.num) DAY) dt

-> FROM

-> (SELECT 0 num UNION ALL

-> SELECT 1 num UNION ALL

-> SELECT 2 num UNION ALL

-> SELECT 3 num UNION ALL

-> SELECT 4 num UNION ALL

-> SELECT 5 num UNION ALL

-> SELECT 6 num UNION ALL

-> SELECT 7 num UNION ALL

-> SELECT 8 num UNION ALL

-> SELECT 9 num) ones

-> CROSS JOIN

-> (SELECT 0 num UNION ALL 

-> SELECT 10 num UNION ALL

-> SELECT 20 num UNION ALL

-> SELECT 30 num UNION ALL

-> SELECT 40 num UNION ALL

-> SELECT 50 num UNION ALL

-> SELECT 60 num UNION ALL

-> SELECT 70 num UNION ALL

-> SELECT 80 num UNION ALL

-> SELECT 90 num) tens

-> CROSS JOIN

-> (SELECT 0 num UNION ALL

-> SELECT 100 num UNION ALL

-> SELECT 200 num UNION ALL

-> SELECT 300 num) hundreds

-> WHERE DATE_ADD('2005-01-01',

-> INTERVAL (ones.num + tens.num + hundreds.num) DAY)

-> < '2006-01-01' 

-> ) days 

-> ON days.dt = date(r.rental_date)

-> GROUP BY days.dt

-> ORDER BY 1;

+------------+-------------+

| dt | num_rentals |

+------------+-------------+

| 2005-01-01 | 0 |

| 2005-01-02 | 0 |

| 2005-01-03 | 0 |

| 2005-01-04 | 0 |

...

| 2005-05-23 | 0 |

| 2005-05-24 | 8 |

| 2005-05-25 | 137 |

| 2005-05-26 | 174 |

| 2005-05-27 | 166 |

| 2005-05-28 | 196 |

| 2005-05-29 | 154 |

| 2005-05-30 | 158 |

| 2005-05-31 | 163 |

| 2005-06-01 | 0 |

...

| 2005-06-13 | 0 |

| 2005-06-14 | 16 |

| 2005-06-15 | 348 |

| 2005-06-16 | 324 |

| 2005-06-17 | 325 |

| 2005-06-18 | 344 |

| 2005-06-19 | 348 |

| 2005-06-20 | 331 |

| 2005-06-21 | 275 |

| 2005-06-22 | 0 |

...

| 2005-12-27 | 0 |

| 2005-12-28 | 0 |

| 2005-12-29 | 0 |

| 2005-12-30 | 0 |

| 2005-12-31 | 0 |

+------------+-------------+

365 rows in set (8.99 sec)

到目前为止，这是本书中最有趣的查询之一，因为它包括交叉连接、外连接、日期函数、分组、集合操作（union all）和聚合函数（count()）。虽然对于给定的问题，它不一定是最优雅的解决方案，但它作为例子至少能够给我们一些启迪：只要牢牢掌握该语言并有一点创造力，即使是很少使用的特性（如交叉连接）也可以在 SQL 工具箱中成为一个有效的工具。

自然连接

假如你想少动点脑子，可以选择这样的连接类型：只命名需要连接的表，连接条件的具体类型由数据库服务器决定。这种连接类型称为自然连接（natural join），它依赖多表交叉时的相同列名来推断正确的连接条件。例如，rental 表包含一个名为 customer_id 的列，该列是 customer 表的外键，其主键也是 customer_id。因此，你可以尝试使用自然连接编写查询来连接这两个表：

mysql> SELECT c.first_name, c.last_name, date(r.rental_date) 

-> FROM customer c 

-> NATURAL JOIN rental r;Empty set (0.04 sec)


因为你指定了自然连接，所以服务器检查了表定义并添加了连接条件 r.custome_id=c.customer_id 来连接这两个表。这本可以很好地工作，但在 Sakila 模式（本书使用的示例数据库）中，所有表都包含 last_update 列，以显示每一行执行最新修改的时间，因此服务器还添加了连接条件 r.last_update = c.last_update，这会导致查询不返回任何数据。

解决此问题的唯一方法是使用子查询来限制至少一个表的列：

mysql> SELECT cust.first_name, cust.last_name, date(r.rental_date)

-> FROM

-> (SELECT customer_id, first_name, last_name

-> FROM customer

-> ) cust

 -> NATURAL JOIN rental r;

+------------+-----------+---------------------+

| first_name | last_name | date(r.rental_date) |

+------------+-----------+---------------------+

| MARY | SMITH | 2005-05-25 |

| MARY | SMITH | 2005-05-28 |

| MARY | SMITH | 2005-06-15 |

| MARY | SMITH | 2005-06-15 |

| MARY | SMITH | 2005-06-15 |

| MARY | SMITH | 2005-06-16 |

| MARY | SMITH | 2005-06-18 |

| MARY | SMITH | 2005-06-18 |

...

| AUSTIN | CINTRON | 2005-08-21 |

| AUSTIN | CINTRON | 2005-08-21 |

| AUSTIN | CINTRON | 2005-08-21 |

| AUSTIN | CINTRON | 2005-08-23 |

| AUSTIN | CINTRON | 2005-08-23 |

| AUSTIN | CINTRON | 2005-08-23 |

+------------+-----------+---------------------+

16044 rows in set (0.03 sec)


现在想想，为了省事而不指定连接条件，反而产生了这种麻烦，到底值不值得？——绝对是不值得的！所以我们应该避免使用这种连接类型，而是使用有明确连接条件的内连接。

划线
评论
复制
发布于: 1 小时前阅读数: 9

版权声明: 本文为 InfoQ 作者【数据与智能】的原创文章。

原文链接:【http://xie.infoq.cn/article/48251fd15b5e06e63137ee98e】。文章转载请联系作者。

数据库sql连接
用户头像

数据与智能

关注

还未添加个人签名 2018.05.14 加入

公众号【数据与智能】主理人，个人微信：liuq4360 12 年大数据与 AI相关项目经验， 10 年推荐系统研究及实践经验，目前已经输出了40万字的推荐系统系列精品文章，并有新书即将出版。

评论

发布
暂无评论
Copyright © 2021, Geekbang Technology Ltd. All rights reserved. 极客邦控股（北京）有限公司 | 京 ICP 备 16027448 号 - 5京公网安备京公网安备 11010502039052号
「SQL数据分析系列」10. 重谈连接