「SQL 数据分析系列」5. 多表查询

写在前面：

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

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

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

内推信息

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

免费学习资料

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

学习交流群

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

第五章多表查询

在第二章中，我演示了如何通过规范化过程将相关概念分解成独立的部分，这个示例最终产生两个表格：person 和 favorite_food。但是如果要创建一个显示某人的姓名、地址和最喜欢的食物的报告，则需要某种机制将这两个表中的数据重新整合在一起，这种机制称为连接（join）。本章主要介绍最简单和最常见的连接，即内连接（inner join）。第十章演示了其他所有连接类型。

什么是连接

尽管对单个表的查询并不少见，但你会发现大多数查询都会涉及两个、三个甚至更多的表。为了举例说明，让我们看一下 customer 和 address 表的定义，然后编写一个从这两个表检索数据的查询：

mysql> desc customer;

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

| Field | Type | Null | Key | Default |

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

| customer_id | smallint(5) unsigned | NO | PRI | NULL |

| store_id | tinyint(3) unsigned | NO | MUL | NULL |

| first_name | varchar(45) | NO | | NULL |

| last_name | varchar(45) | NO | MUL | NULL |

| email | varchar(50) | YES | | NULL |

| address_id | smallint(5) unsigned | NO | MUL | NULL |

| active | tinyint(1) | NO | | 1 |

| create_date | datetime | NO | | NULL |

| last_update | timestamp | YES | | CURRENT_TIMESTAMP |

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

mysql> desc address;

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

| Field | Type | Null | Key | Default |

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

| address_id | smallint(5) unsigned | NO | PRI | NULL |

| address | varchar(50) | NO | | NULL |

| address2 | varchar(50) | YES | | NULL |

| district | varchar(20) | NO | | NULL |

| city_id | smallint(5) unsigned | NO | MUL | NULL |

| postal_code | varchar(10) | YES | | NULL |

| phone | varchar(20) | NO | | NULL |

| location | geometry | NO | MUL | NULL |

| last_update | timestamp | NO | | CURRENT_TIMESTAMP |

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

假设你要检索每个客户的名字和姓氏，以及他们的街道地址。因此，你的查询需要检索 customer.first_name，customer.last_name,和 address.address 列。但是如何在同一个查询中从两个表中检索数据呢？答案的关键就在于 customer.address_id 列，它保存 address 表中客户记录的 ID（更正式的说法是 customer.address_id 指向 address 表的外键）。这个查询指示服务器使用 customer.address_id 列作为 customer 表和 address 表之间的桥梁，从而允许在同一查询的结果集中包括两个表中的列，该类操作称为连接（join）。

注意：可以选择创建外键约束来验证一个表中的值是否存在于另一个表中。对于上一个示例，可以在 customer 表上创建外键约束，以确保插入到 customer.address_id 列的数据可以在 address.address_id 列找到。请注意，对两个表执行 join 操作不一定要有外键约束。

笛卡尔积

最简单的方法是将 customer 和 address 表直接放入查询的 from 子句中，看看会有怎样的输出。下面的查询检索客户的名字和姓氏以及街道地址，在 from 子句中包含两个表，并用 join 关键字分隔：

mysql> SELECT c.first_name, c.last_name, a.address  

-> FROM customer c JOIN address a;

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

| first_name | last_name | address |

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

| MARY | SMITH | 47 MySakila Drive | | PATRICIA | JOHNSON | 47 MySakila Drive |

| LINDA | WILLIAMS | 47 MySakila Drive | | BARBARA | JONES | 47 MySakila Drive |

| ELIZABETH | BROWN | 47 MySakila Drive | | JENNIFER | DAVIS | 47 MySakila Drive |

| MARIA | MILLER | 47 MySakila Drive | | SUSAN | WILSON | 47 MySakila Drive |

...

| SETH | HANNON | 1325 Fukuyama Street |

| KENT | ARSENAULT | 1325 Fukuyama Street |

| TERRANCE | ROUSH | 1325 Fukuyama Street |

| RENE | MCALISTER | 1325 Fukuyama Street |

| EDUARDO | HIATT | 1325 Fukuyama Street |

| TERRENCE | GUNDERSON | 1325 Fukuyama Street |

| ENRIQUE | FORSYTHE | 1325 Fukuyama Street |

| FREDDIE | DUGGAN | 1325 Fukuyama Street |

| WADE | DELVALLE | 1325 Fukuyama Street |

| AUSTIN | CINTRON | 1325 Fukuyama Street |

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

361197 rows in set (0.03 sec)

我们有 599 个客户，address 表中有 603 行数据，那么结果集是如何高达 361197 行的呢？仔细看，你会发现很多客户的街道地址都是一样的。因为查询没有指定两个表应该如何连接，所以数据库服务器产生笛卡尔积（Cartesian product），即两个表的所有排列组合（599 个客户 x 603 个地址=361197 个排列）。该类连接称为交叉连接（cross join），实际应用中很少会使用这种连接，我们会在第十章介绍它。

内连接

要修改前面的查询，使每个客户只返回一行数据，则需要描述两个表之间的关系。我先前说过 customer.address_id 列在两个表之间起连接作用，因此需要将此信息添加到 from 子句的 on 子句中：

mysql> SELECT c.first_name, c.last_name, a.address

 -> FROM customer c JOIN address a

 -> ON c.address_id = a.address_id;

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

| first_name | last_name | address |

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

| MARY | SMITH | 1913 Hanoi Way |

| PATRICIA | JOHNSON | 1121 Loja Avenue |

| LINDA | WILLIAMS | 692 Joliet Street |

| BARBARA | JONES | 1566 Inegl Manor |

| ELIZABETH | BROWN | 53 Idfu Parkway |

| JENNIFER | DAVIS | 1795 Santiago de Compostela Way |

| MARIA | MILLER | 900 Santiago de Compostela Parkway |

| SUSAN | WILSON | 478 Joliet Way |

| MARGARET | MOORE | 613 Korolev Drive |

...

| TERRANCE | ROUSH | 42 Fontana Avenue |

| RENE | MCALISTER | 1895 Zhezqazghan Drive |

| EDUARDO | HIATT | 1837 Kaduna Parkway |

| TERRENCE | GUNDERSON | 844 Bucuresti Place |

| ENRIQUE | FORSYTHE | 1101 Bucuresti Boulevard |

| FREDDIE | DUGGAN | 1103 Quilmes Boulevard |

| WADE | DELVALLE | 1331 Usak Boulevard |

| AUSTIN | CINTRON | 1325 Fukuyama Street |

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

599 rows in set (0.00 sec)

现在，由于增加了 on 子句，结果集输出了预期的 599 行数据，而不是之前的 361197 行，该子类指示服务器通过使用 address_id 列将 customer 表和 address 表连接起来。例如，customer 表中 Mary Smith 的行在 address_id 列中的值是 5（示例中未显示），服务器使用此值在 address 表的 address_id 列中查找值为 5 的行，然后从该行的 address 列中获取值，即'1913 Hanoi Way'。

如果一个表中的 address_id 列存在一个值，而另一个表中不存在该值，则包含该值的行的连接将失败，并且不会被包括到结果集中。这种类型的连接称为内连接（inner join），它是最常用的连接类型。具体来讲，如果 customer 表中的某一行的 address_id 列的值为 999，而 address 表中没有某一行的 address_id 列的值为 999，则该 customer 行不会包含在结果集中。如果希望不管是否存在匹配项，某表中的所有行都能被包括到结果集中，则需要使用外连接（outer join），但这是我们在第十章才会介绍的内容。

在上一个示例中，我没有在 from 子句中指定连接类型。但是，当要使用内连接来连接两个表的时候，应该在 from 子句中显式指明这一点。下面是与上面相同的示例，添加了连接类型（注意关键字 inner）：

SELECT c.first_name, c.last_name, a.address

FROM customer c INNER JOIN address a

 ON c.address_id = a.address_id;

如果不指定连接类型，那么服务器将默认使用内连接。然而后面我们会介绍其他几种类型的连接，所以你最好能养成在使用连接时明确指定类型的习惯，这样也方便其他人在将来使用或维护你的查询。

如果用于连接两个表的列的名称相同（比如上一个查询就是这样），则可以使用 using 子句替代 on 子句，如下所示：

SELECT c.first_name, c.last_name, a.address

FROM customer cINNER JOIN address a

 USING (address_id);

因为 using 是一种只能在特定情况下使用的语法简化方法，所以我还是建议使用 on 子句避免混淆。

ANSI 连接语法

本书中用于连接表的符号是从 ANSI SQL 标准的 SQL92 版本中引入的。所有主流数据库（Oracle Database、Microsoft SQL Server、MySQL、IBM DB2 Universal Database 和 Sybase Adaptive Server）都采用了 SQL92 的连接语法。由于这些数据库大多数在 SQL92 标准发布之前就存在了，所以它们也包含一些旧的连接语法。例如，所有这些服务器都可以识别前面查询的以下变化：

mysql> SELECT c.first_name, c.last_name, a.address 

-> FROM customer c, address a 

-> WHERE c.address_id = a.address_id;

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

| first_name | last_name | address |

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

| MARY | SMITH | 1913 Hanoi Way |

| PATRICIA | JOHNSON | 1121 Loja Avenue |

| LINDA | WILLIAMS | 692 Joliet Street |

| BARBARA | JONES | 1566 Inegl Manor ||

ELIZABETH | BROWN | 53 Idfu Parkway |

| JENNIFER | DAVIS | 1795 Santiago de Compostela Way |

| MARIA | MILLER | 900 Santiago de Compostela Parkway |

| SUSAN | WILSON | 478 Joliet Way |

| MARGARET | MOORE | 613 Korolev Drive |

...

| TERRANCE | ROUSH | 42 Fontana Avenue |

| RENE | MCALISTER | 1895 Zhezqazghan Drive |

| EDUARDO | HIATT | 1837 Kaduna Parkway

|| TERRENCE | GUNDERSON | 844 Bucuresti Place |

| ENRIQUE | FORSYTHE | 1101 Bucuresti Boulevard |

| FREDDIE | DUGGAN | 1103 Quilmes Boulevard |

| WADE | DELVALLE | 1331 Usak Boulevard |

| AUSTIN | CINTRON | 1325 Fukuyama Street |

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

599 rows in set (0.00 sec)

这种旧的连接方法不包括 on 子类，而是在 from 子句通过逗号将命名的表分隔开，接着在 where 子句中使用连接条件。可能你不想按照 SQL92 语法的标准来，而偏向于使用旧连接语法，但是 ANSI 连接语法具备以下优点：

• 连接条件和过滤条件被分为两个不同的子句（on 子句和 where 子句），使得查询更容易理解；

• 每两个表的连接条件都包含在它们自己的 on 子句中，这样不容易错误地忽略某些连接条件；

• 使用 SQL92 连接语法的查询可以跨数据库服务器移植，而旧的语法在不同的服务器上略有不同。

SQL92 连接语法的优点在同时包含连接和过滤条件的复杂查询中更加明显。考虑以下查询，它仅返回邮政编码为 52137 的客户：

mysql> SELECT c.first_name, c.last_name, a.address 

-> FROM customer c, address a

 -> WHERE c.address_id = a.address_id

 -> AND a.postal_code = 52137;

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

| first_name | last_name | address |

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

| JAMES | GANNON | 1635 Kuwana Boulevard |

| FREDDIE | DUGGAN | 1103 Quilmes Boulevard |

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

2 rows in set (0.01 sec)

乍一看，要确定 where 子句中的哪些条件是连接条件，哪些是过滤条件并不容易。再者，使用哪种类型的连接也不是很明显（要知道连接的类型，就要仔细观察 where 子句中的连接条件，看是否使用了什么特殊字符）。此外，也不容易确定是否错误地忽略了某些连接条件。下面是使用 SQL92 连接语法的相同查询：

mysql> SELECT c.first_name, c.last_name, a.address

 -> FROM customer c INNER JOIN address a 

-> ON c.address_id = a.address_id

 -> WHERE a.postal_code = 52137;

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

| first_name | last_name | address |

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

| JAMES | GANNON | 1635 Kuwana Boulevard |

| FREDDIE | DUGGAN | 1103 Quilmes Boulevard |

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

2 rows in set (0.00 sec)

在这条查询中，你能很清楚地看到哪个条件用于连接，哪个条件用于过滤。显然，使用 SQL92 连接语法的语句会更容易理解。

连接三个及以上的表

连接三个表和连接两个表的方法类似，但有些微不同。对于两个表的连接，from 子句中包含两个表名和一个连接类型，以及一个用于定义如何连接两个表的 on 子句。对于三个表的连接，在 from 子句中将包含三个表和两种连接类型，以及两个 on 子句。

为了说明这一点，我们将前面的查询改为返回客户的城市而不是街道地址。注意城市名不存储在 address 表中，而是通过 city 表的外键进行访问。以下是表格定义：

mysql> desc address;

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

| Field | Type | Null | Key | Default |

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

| address_id | smallint(5) unsigned | NO | PRI | NULL |

| address | varchar(50) | NO | | NULL |

| address2 | varchar(50) | YES | | NULL |

| district | varchar(20) | NO | | NULL |

| city_id | smallint(5) unsigned | NO | MUL | NULL |

| postal_code | varchar(10) | YES | | NULL |

| phone | varchar(20) | NO | | NULL |

| location | geometry | NO | MUL | NULL |

| last_update | timestamp | NO | | CURRENT_TIMESTAMP |

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

mysql> desc city;

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

| Field | Type | Null | Key | Default |

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

| city_id | smallint(5) unsigned | NO | PRI | NULL |

| city | varchar(50) | NO | | NULL |

| country_id | smallint(5) unsigned | NO | MUL | NULL |

| last_update | timestamp | NO | | CURRENT_TIMESTAMP |

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

为了打印出每个客户的城市，需要使用 address_id 列从 customer 转移到 address，然后通过 city_id 列由 address 表到 city 表。查询如下：

mysql> SELECT c.first_name, c.last_name, ct.city 

-> FROM customer c 

-> INNER JOIN address a 

-> ON c.address_id = a.address_id 

-> INNER JOIN city ct 

-> ON a.city_id = ct.city_id;

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

| first_name | last_name | city |

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

| JULIE | SANCHEZ | A Corua (La Corua) |

| PEGGY | MYERS | Abha |

| TOM | MILNER | Abu Dhabi |

| GLEN | TALBERT | Acua |

| LARRY | THRASHER | Adana |

| SEAN | DOUGLASS | Addis Abeba |

...

| MICHELE | GRANT | Yuncheng |

| GARY | COY | Yuzhou |

| PHYLLIS | FOSTER | Zalantun |

| CHARLENE | ALVAREZ | Zanzibar |

| FRANKLIN | TROUTMAN | Zaoyang |

| FLOYD | GANDY | Zapopan |

| CONSTANCE | REID | Zaria |

| JACK | FOUST | Zeleznogorsk |

| BYRON | BOX | Zhezqazghan |

| GUY | BROWNLEE | Zhoushan |

| RONNIE | RICKETTS | Ziguinchor |

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

599 rows in set (0.03 sec)

对于这个查询，在 from 子句中有三个表、两个连接类型和两个 on 子句，所以看起来蛮复杂的。乍一看，表在 from 子句中出现的顺序好像很重要，但是其实如果你调换表的顺序，也能得到相同的结果。下面三种查询语句返回的结果都相同：

SELECT c.first_name, c.last_name, ct.city

FROM customer c  

INNER JOIN address a

 ON c.address_id = a.address_id

 INNER JOIN city ct

 ON a.city_id = ct.city_id;   

SELECT c.first_name, c.last_name, ct.city

FROM city ct  

INNER JOIN address a  

ON a.city_id = ct.city_id  

INNER JOIN customer c

 ON c.address_id = a.address_id;   

SELECT c.first_name, c.last_name, ct.city

FROM address a

 INNER JOIN city ct

 ON a.city_id = ct.city_id

 INNER JOIN customer c

 ON c.address_id = a.address_id;

因为没有使用 order by 子句指定结果的排序方式，所以唯一的不同就是返回行的顺序了。

连接顺序重要吗

如果你不明白为什么 customer/address/city 查询的所有三个版本都会产生相同的结果，那么请记住一点：SQL 是一种非过程化的语言，也就是说，只要描述需要检索的内容以及涉及的数据库对象，而以何种方式执行查询则是由数据库服务器负责的。数据库服务器使用从数据库对象收集的信息，从三张表中选择一个作为起点（被选择的表称为驱动表(driving table)），然后确定连接其他表的顺序。因此表在 from 子句中出现的顺序并不重要。

但是，如果你想在查询中以特定的顺序连接表，则可以按照需要的顺序将表排列好，然后指定 MySQL 关键字 straight_join，或者使用 SQL Server 的 force order 选项，抑或是在 Oracle Database 中使用 ordered 或者 leading 优化提示。例如，要告诉 MySQL 服务器使用 city 表作为驱动表，然后连接 address 和 customer 表，可以使用以下语句：

SELECT STRAIGHT_JOIN c.first_name, c.last_name, ct.city

FROM city ct 

INNER JOIN address a

 ON a.city_id = ct.city_id 

INNER JOIN customer c 

ON c.address_id = a.address_id

将子查询结果作为查询表

前面介绍了几个包含多个表的查询示例，但是还有一点值得思考：如果某些数据集是由子查询生成的，该怎么办？子查询是第九章的重点，但我在上一章的 from 子句中已经介绍过子查询的概念。以下查询将 customer 表与 address 和 city 表的子查询相连接：

mysql> SELECT c.first_name, c.last_name, addr.address, addr.city

 -> FROM customer c  

-> INNER JOIN  

-> (SELECT a.address_id, a.address, ct.city  

-> FROM address a  -> INNER JOIN city ct

 -> ON a.city_id = ct.city_id

 -> WHERE a.district = 'California'  

-> ) addr  

-> ON c.address_id = addr.address_id;

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

| first_name | last_name | address | city |

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

| PATRICIA | JOHNSON | 1121 Loja Avenue | San Bernardino |

| BETTY | WHITE | 770 Bydgoszcz Avenue | Citrus Heights |

| ALICE | STEWART | 1135 Izumisano Parkway | Fontana |

| ROSA | REYNOLDS | 793 Cam Ranh Avenue | Lancaster |

| RENEE | LANE | 533 al-Ayn Boulevard | Compton |

| KRISTIN | JOHNSTON | 226 Brest Manor | Sunnyvale |

| CASSANDRA | WALTERS | 920 Kumbakonam Loop | Salinas |

| JACOB | LANCE | 1866 al-Qatif Avenue | El Monte |

| RENE | MCALISTER | 1895 Zhezqazghan Drive | Garden Grove |

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

9 rows in set (0.00 sec)

第四行是第一个子查询，别名为 addr，它查找位于加利福尼亚的所有地址。外部查询将子查询结果连接到 customer 表，以返回居住在加利福尼亚的所有客户的名字、姓氏、街道地址和城市。虽然可以不使用子查询，而是通过简单地连接三个表来编写这个查询，但有时使用一个或多个子查询对于性能和可读性的提升有好处。

想知道内部到底发生了什么，可以单独运行子查询并查看结果。下面是上一个示例的子查询结果：

mysql> SELECT a.address_id, a.address, ct.city  

-> FROM address a  

-> INNER JOIN city ct

 -> ON a.city_id = ct.city_id

 -> WHERE a.district = 'California';

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

| address_id | address | city |

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

| 6 | 1121 Loja Avenue | San Bernardino |

| 18 | 770 Bydgoszcz Avenue | Citrus Heights |

| 55 | 1135 Izumisano Parkway | Fontana |

| 116 | 793 Cam Ranh Avenue | Lancaster |

| 186 | 533 al-Ayn Boulevard | Compton |

| 218 | 226 Brest Manor | Sunnyvale |

| 274 | 920 Kumbakonam Loop | Salinas |

| 425 | 1866 al-Qatif Avenue | El Monte |

| 599 | 1895 Zhezqazghan Drive | Garden Grove |

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

9 rows in set (0.00 sec)

此结果集包含所有九个位于加利福尼亚的地址。当通过 address_id 列连接到 customer 表时，结果集将包含有关这些地址的客户信息。

连续两次使用一个表

如果要连接多个表，可能会需要多次连接同一个表。例如，在示例数据库中，演员通过 film_actor 表与他们出演的电影相关联。如果要查找出现两个指定演员的影片，可以编写查询如下：将 film 表连接到 film_actor 表，将 film_actor 表连接到 actor 表：

mysql> SELECT f.title

 -> FROM film f

 -> INNER JOIN film_actor fa

 -> ON f.film_id = fa.film_id  

-> INNER JOIN actor a  

-> ON fa.actor_id = a.actor_id  

-> WHERE ((a.first_name = 'CATE' AND a.last_name = 'MCQUEEN')  

-> OR (a.first_name = 'CUBA' AND a.last_name = 'BIRCH'));

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

| title |

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

| ATLANTIS CAUSE |

| BLOOD ARGONAUTS |

| COMMANDMENTS EXPRESS |

| DYNAMITE TARZAN |

| EDGE KISSING |

...

| TOWERS HURRICANE |

| TROJAN TOMORROW |

| VIRGIN DAISY |

| VOLCANO TEXAS |

| WATERSHIP FRONTIER |

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

54 rows in set (0.00 sec)

此查询返回所有出现了演员 Cate McQueen 或 Cuba Birch 的电影。但是如果你要检索同时出现这两个演员的电影，则需要在 film 表中找到在 film_actor 表中有两行数据的所有行，其中一行与 Cate McQueen 关联，另一行与 Cuba Birch 关联。因此，你需要两次包含 film_actor 和 actor 表，每个表都要有不同的别名，以便服务器知道你在不同的子句中引用的是哪一个表：

mysql> SELECT f.title 

-> FROM film f 

-> INNER JOIN film_actor fa1 

-> ON f.film_id = fa1.film_id

 -> INNER JOIN actor a1 

-> ON fa1.actor_id = a1.actor_id

 -> INNER JOIN film_actor fa2 

-> ON f.film_id = fa2.film_id 

-> INNER JOIN actor a2 

-> ON fa2.actor_id = a2.actor_id 

-> WHERE (a1.first_name = 'CATE' AND a1.last_name = 'MCQUEEN') 

-> AND (a2.first_name = 'CUBA' AND a2.last_name = 'BIRCH');

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

| title |

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

| BLOOD ARGONAUTS |

| TOWERS HURRICANE |

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

2 rows in set (0.00 sec)

可以看到，两位演员出演了 54 部不同的电影，但他们只合作过两部电影。因为多次使用了同一个表，所以示例中用到了表别名。

自连接

不仅可以在同一个查询中多次包含同一个表，而且还可以将一个表连接到它本身。刚开始你可能会觉得这样做很奇怪，但其实这样也是合理的。有些表包含指向自身的外键，这意味着它包含指向同一表中主键的列。虽然示例数据库中并不包含这样的关系，但是让我们假设 film 表包含 prequel_film_id 列，它指向电影的父级（例如，电影 Fiddler Lost II 将使用此列指向父级电影 Fiddler Lost）。如果我们添加该附加列，表结构将如下：

mysql> desc film;

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

| Field | Type | Null | Key | Default |

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

| film_id | smallint(5) unsigned | NO | PRI | NULL |

| title | varchar(255) | NO | MUL | NULL |

| description | text | YES | | NULL |

| release_year | year(4) | YES | | NULL |

| language_id | tinyint(3) unsigned | NO | MUL | NULL |

| original_language_id | tinyint(3) unsigned | YES | MUL | NULL |

| rental_duration | tinyint(3) unsigned | NO | | 3 |

| rental_rate | decimal(4,2) | NO | | 4.99 |

| length | smallint(5) unsigned | YES | | NULL |

| replacement_cost | decimal(5,2) | NO | | 19.99 |

| rating | enum('G','PG','PG-13', 'R','NC-17') | YES | | G |

| special_features | set('Trailers',..., 'Behind the Scenes')| YES | | NULL |

| last_update | timestamp | NO |

| CURRENT_ TIMESTAMP |

| prequel_film_id | smallint(5) unsigned | YES | MUL | NULL 

|+----------------------+-----------------------+------+-----+-------------------+

使用自连接（self-join），你可以编写查询检索有前传的电影，以及电影前传的名字：

mysql> SELECT f.title, f_prnt.title prequel

 -> FROM film f  

-> INNER JOIN film f_prnt  

-> ON f_prnt.film_id = f.prequel_film_id

 -> WHERE f.prequel_film_id IS NOT NULL;

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

| title | prequel |

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

| FIDDLER LOST II | FIDDLER LOST |

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

1 row in set (0.00 sec)

此查询使用 prequel_film_id 外键将 film 表自连接，并指定表别名 f 和 f_prnt，以区分各个表的用途。