写点什么

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

发布于: 2021 年 06 月 16 日
「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 |

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

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 |

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

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 |

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

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 |

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

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 |

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

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 |

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

row in set (0.00 sec)

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


发布于: 2021 年 06 月 16 日阅读数: 78
用户头像

还未添加个人签名 2018.05.14 加入

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

评论

发布
暂无评论
「SQL数据分析系列」5. 多表查询