MySQL CookBook 学习笔记-12使用多重表

来源：互联网发布：小米平板3装windows 编辑：程序博客网时间：2024/05/18 01:20

12.0引言：

前面章节讨论的例子中大部分使用的是单一表，但是对于任何具有普通复杂性的应用来说，你很可能会需要使用多重表。一些问题不能够简单地使用单一表来解决，并且关系数据库的真正能力只有在你从复合数据来源中整合信息时才能表现出来。使用多重表有如下几个理由：

将来自多个表中的行结合起来，获取的信息比从单个表中所能获取的更为全面
保持多步操作的中间结果
根据其他表的信息修改某个表中的记录行

一个使用多重表的语句可能是多个表的连接（join），也可能是嵌套在另一个语句中的子查询，或者是多重 SELECT 语句查询结果的联合（union）。前面的章节中，我们已经或多或少地接触过子查询了，因此本章主要的焦点将放在连接和和联合上面，当然在必要时也会提到子查询。本章覆盖的主题如下：

连接不同的表以发现它们中相匹配或不匹配的行

为了解决这些问题，你必须知道连接操作的类型。内部连接（inner joins）可以展示某个表中哪些行与另一个表中的行相匹配。而外部连接（outer joins）既可以显示匹配行，也可以用于在某个表中找到与另一个表中的行不匹配的那些行。

将一个表与其自身相比较

解决某些问题时你需要将某些表与其自身相比较，这与在不同表间执行连接操作相类似，不同点在于你必须使用表别名以消除表引用的歧义。

使用联合以将多个结果集结合起来

对于某些查询，需要的信息包含了多个结果集，这些结果集可能是从不同的表中获取的，也可能是从同一个表中以不同的查询方式得到的。为了产生这样的结果，可以使用UNION将多重SELECT语句所获得的结果集联合起来。

删除不匹配的行

如果两个相关的数据集在关系分配上有缺陷，你可以确定哪些行是不匹配的。并且如果这些行是不需要的，你可以删除他们。

在不同数据库的表之间执行连接操作

当你使用多重表时，它们可能来源于同一个数据库，也可能来自不同的数据库。有时候，你甚至可能会需要使用来源于不同MySQL服务器上数据库中的表。在前两种情况下，你需要知道如何查阅不同的表中的列，这可能包括使用表的别名或者在表面前加上数据库名修饰。在第3种情况下，你可以建立跨类联合存储式数据库表（FEDERATED table）以使一个MySQL服务器能够自动访问位于另一个服务器上的表，或者为每个服务器打开一个连接并自己手动将其中的信息组合起来。

12.1 在表中找到与另一个表中的行相匹配的行

问题：

你需要编写语句以查询多个表中的信息。

解决方案：

使用连接（join）－－－即一个查询，它在FROM子句列出多重表并告诉MySQL如何匹配其中的信息

讨论：

连接的本质概念是将一个表中的行与另一个或更多的表中的行相联合。对于多重表来说，如果它们中的每个表只含有你所感兴趣的信息的一部分，那么连接使你能够将这些表中的信息结合起来。连接的输出行所包含的信息要多于从其中任何一个表中单独取出的行信息。

一个完全的连接将产生所有可能的行联合，即笛卡尔积。举例来说，将一个具有100行的表与另一个包含200行的表进行连接，产生的结果将会包含100X200即20 000个行。对于更大的表，或者对两个以上的表进行连接，由笛卡尔积产生的结果集将会膨胀得很大。正因如此，同时也因为你很少需要得到所有的联合，连接通常会包含ON或USING子句以指定如何在表间进行连接。（这需要每个表含有一个或多个包含共同信息的表，以便能够从逻辑上将它们连接在一起。）你还可以在连接中包含WHERE子句以限制选择哪些连接行。这些子句都能够缩小查询的范围。

本节将介绍基本的join语法，并展示当你查找表间的匹配时，连接是如何帮助你解决某些类型问题的。后面几节将介绍如何识别表间的不匹配，以及如何将一个表与其自身相比较。下例假设你进行美术收藏，并且用下面两个表来记录你的藏品。artist 表列出了你想要收藏其作品的画家，painting 表列出了你已经购买的作品：

CREATE TABLE artist
(
a_id INT UNSIGNED NOT NULL AUTO_INCREMENT, # artist ID
name VARCHAR(30) NOT NULL, # artist name
PRIMARY KEY (a_id),
UNIQUE (name)
);

CREATE TABLE painting
(
a_id INT UNSIGNED NOT NULL,                # artist ID
p_id INT UNSIGNED NOT NULL AUTO_INCREMENT, # painting ID
title VARCHAR(100) NOT NULL,                # title of painting
state VARCHAR(2) NOT NULL,                  # state where purchased
price INT UNSIGNED,                         # purchase price (dollars)
INDEX (a_id),
PRIMARY KEY (p_id)
);

由于你刚开始收藏，因此表中只有如下几行：

mysql > SELECT * FROM artist ORDER BY a_id ASC;

+------+----------+
| a_id | name     |
+------+----------+
|    1 | Da Vinci |
|    2 | Monet    |
|    3 | Van Gogh |
|    4 | Picasso |
|    5 | Renoir   |
+------+----------+

mysql > SELECT * FROM painting ORDER BY a_id ASC, p_id ASC;

+------+------+-------------------+-------+-------+
| a_id | p_id | title             | state | price |
+------+------+-------------------+-------+-------+
|    1 |    1 | The Last Supper   | IN    |    34 |
|    1 |    2 | The Mona Lisa     | MI    |    87 |
|    3 |    3 | Starry Night      | KY    |    48 |
|    3 |    4 | The Potato Eaters | KY    |    67 |
|    3 |    5 | The Rocks         | IA    |    33 |
|    5 |    6 | Les Deux Soeurs   | NE    |    64 |
+------+------+-------------------+-------+-------+

painting 表的price列值都很小，这暴露了你的收藏都是些廉价摹本，而不是真迹的事实。嗯，这很正常，谁能买得起原作呢？

每张表都只包含了你的藏品的部分信息。比如说，artist 表没有指出所收藏的是哪个画家的哪幅作品，而painting表列出了画家的ID而不是他们的名字。为了使用两个表的信息，你可以通过写一个执行连接的查询语句，以要求MySQL为你显示画家和作品的不同组合。连接语句在FROM关键字后面列出两个或更多个表的名字。在输出列的列表中，你可以列出从部分或所有已连接的表中所要取的列的名字，或者使用建立在这些列基础上的表达式，如tb1_name.*选择给定表的所有列，或者*选择所有表的所有列。

最简单的连接包括两个表，并选择它们中的所有列。由于没有任何限制，连接产生的结果包括所有行的联合（即笛卡尔积）。下面为在artist和painting表之间的完全连接：

mysql > SELECT * FROM artist, painting;

该语句的输出说明了为什么完全的连接通常是无用的，因为它产生的输出项太多了，以至于结果没有意义。显然，你维护这些表不是为了像前面语句所做的那样，将每个画家和每幅作品都匹配起来。此例中毫无限制的连接没有任何价值。

为了解决一些有意义的问题，你必须在连接两张表时，只产生相关的匹配，而通过包含适当的连接条件你可以做到这一点。举例来说，为了产生一幅画作及其画家的列表，你可以使用简单的WHERE子句将两个表的行关联起来，该子句通过两张表都有的画家的ID值进行匹配，作为它们之间的连接：

mysql > SELECT * FROM artist AS a, painting AS p WHERE a.a_id = p.a_id;

WHERE子句中的列名包含了表名的修饰符，这清楚地指出了比较的是哪个a_id。输出指明了在你的收藏中，每幅画作的作者是谁，以及每个画家有哪些作品。

达到同样连接目的的另一种方式是使用INNER JOIN而不是逗号操作符，并在ON子句中指明匹配条件：

mysql > SELECT * FROM artist AS a INNER JOIN painting AS p ON a.a_id = p.a_id;

在特殊情况下，即两个表的匹配列名字相同，并且是使用=操作符进行比较的话，你可以使用INNER JOIN并搭配USING子句作为替代，从而不需要表修饰符，而且连接列只需要被列出一次：

mysql > SELECT * FROM artist INNER JOIN painting USING(a_id);

+------+----------+------+-------------------+-------+-------+
| a_id | name     | p_id | title             | state | price |
+------+----------+------+-------------------+-------+-------+
|    1 | Da Vinci |    1 | The Last Supper   | IN    |    34 |
|    1 | Da Vinci |    2 | The Mona Lisa     | MI    |    87 |
|    5 | Renoir   |    6 | Les Deux Soeurs   | NE    |    64 |
|    3 | Van Gogh |    3 | Starry Night      | KY    |    48 |
|    3 | Van Gogh |    4 | The Potato Eaters | KY    |    67 |
|    3 | Van Gogh |    5 | The Rocks         | IA    |    33 |
+------+----------+------+-------------------+-------+-------+

注意当你在查询中使用USING子句时，SELECT * 只返回所有连接列中的一个实例（a_id）。

ON、USING或WHERE中的任何一个都可以包含比较操作，那么你怎么知道每个子句中该放入什么样的连接条件呢？根据经验规则，通常使用ON或USING来指定如何连接表，而使用WHERE子句限制选择哪些已连接的行。举例来说，如果根据a_id列连接表，但是是只选择在肯塔基州（Kentucky）购买的画作，那么使用ON（或USING）子句匹配两个表中的行，使用WHERE子句来检验state列：

mysql > SELECT * FROM artist INNER JOIN painting ON artist.a_id = painting.a_id WHERE painting.state = 'KY';

+------+----------+------+------+-------------------+-------+-------+
| a_id | name     | a_id | p_id | title             | state | price |
+------+----------+------+------+-------------------+-------+-------+
|    3 | Van Gogh |    3 |    3 | Starry Night      | KY    |    48 |
|    3 | Van Gogh |    3 |    4 | The Potato Eaters | KY    |    67 |
+------+----------+------+------+-------------------+-------+-------+

前面的查询使用了SELECT * 以选择所有的列。为了更好地选择一个语句应该显示哪些列，你可以提供所感兴趣的那些列的名字列表：

mysql > SELECT artist.name, painting.title, painting.state, painting.price FROM artist INNER JOIN painting ON artist.a_id = painting.a_id WHERE painting.state = 'KY';

+----------+-------------------+-------+-------+
| name     | title             | state | price |
+----------+-------------------+-------+-------+
| Van Gogh | Starry Night      | KY    |    48 |
| Van Gogh | The Potato Eaters | KY    |    67 |
+----------+-------------------+-------+-------+

编写连接查询时不一定只限于两个表。假设在前面的查询结果中，你需要查看的不是州名简写而是完整的州名，前面章节中使用的states表将州的简写与名称映射到一起，因此你可以将之加入到前面的查询中以显示完整的州名：

mysql > SELECT artist.name, painting.title, states.name, painting.price
FROM artist INNER JOIN painting
INNER JOIN states ON artist.a_id = painting.a_id AND painting.state = states.abbrev;

三种方式的连接的另一种常见用途是枚举多对多的关系，参见第12.5节中的例子。

通过在你的连接中包含适当的条件，你可以解决一些非常特殊的问题，比如下面的例子：

哪幅画是梵高（Van Gogh）的作品？为了回答这个问题，可以使用a_id值查找匹配行，再用WHERE子句限制那些包含该画家名字的行的输出，只从中选择作品名称（title）：

mysql > SELECT painting.title FROM artist INNER JOIN painting ON artist.a_id = painting.a_id WHERE artist.name = 'Van Gogh';

+-------------------+
| title |
+-------------------+
| Starry Night |
| The Potato Eaters |
| The Rocks |
+-------------------+

蒙娜丽莎（The Mona Lisa）是谁画的？你又一次使用a_id列以连接行，但是这一次WHERE子句限制那些包含画名的行的输出，以从这些行中选择画家的名字：

mysql > SELECT artist.name FROM artist INNER JOIN painting ON artist.a_id = painting.a_id WHERE painting.title = 'The Mona Lisa';

+----------+
| name |
+----------+
| Da Vinci |
+----------+

你在肯塔基（Kentucky）或印第安纳（Indiana）州购买了哪些画家的画作？这与前一个语句有些类似，通过检验painting表中的a_id列以确定将哪些行与artist表进行连接：

mysql > SELECT DISTINCT artist.name FROM artist INNER JOIN painting ON artist.a_id = painting.a_id WHERE painting.state IN('KY', 'IN');

+----------+
| name |
+----------+
| Da Vinci |
| Van Gogh |
+----------+

该语句使用了DISTINCT以将每个画家的名字只显示一次。如果没有DISTINCT，你将会发现Van Gogh被列出了两次，这是因为你在肯塔基获得了两幅Van Gogh的作品。

连接还可以与聚焦函数一起使用以产生表摘要（summaries）举个例子，为了找到对于每个画家你各收藏了多少作品，可以使用这条语句：

mysql > SELECT artist.name, COUNT(*) FROM artist INNER JOIN painting ON artist.a_id = painting.a_id GROUP BY artist.name;

+----------+----------+
| name | COUNT(*) |
+----------+----------+
| Da Vinci | 2 |
| Renoir | 1 |
| Van Gogh | 3 |
+----------+----------+

更详细的语句可以显示你为每个画家的作品花费了多少钱，包括总和以及每幅作品平均的花费：

mysql > SELECT artist.name, COUNT(*), SUM(painting.price), AVG(painting.price) FROM artist INNER JOIN painting ON artist.a_id = painting.a_id GROUP BY artist.name;

+----------+----------+---------------------+---------------------+
| name | COUNT(*) | SUM(painting.price) | AVG(painting.price) |
+----------+----------+---------------------+---------------------+
| Da Vinci | 2 | 121 | 60.5000 |
| Renoir | 1 | 64 | 64.0000 |
| Van Gogh | 3 | 148 | 49.3333 |
+----------+----------+---------------------+---------------------+

注意该语句只为在artist表中你实际购买过其作品的画家产生输出。（举例来说，artist表中的Monet没有在概要中出现，因为你还没有他的任何画作。）如果你想要表概要包括所有的画家，即使你还没有收藏他们的作品，你必须明确地使用另一种连接－－－外部连接（outer join）：

通过逗号操作符或INNER JOIN所编写的连接为内部连接，这意味着它们只为某个表中与其他表的值相匹配的值产生结果。
外部连接同样可以产生这些匹配，此外还可以为你显示某个表中的哪些值与另外一个表中的值是不相等的。第12.2节介绍了外部连接。

连接与索引：

因为连接会很容易导致MySQL产生大量的行组合，所以确认你所要比较的列已经被索引是个好主意。否则，在表规模增长时性能会下降得很快。对于artist和painting表，连接是基于每个表中的a_id列值产生的。如果你向前查看这些表的建表语句，你会发现a_id在每张表中都已被索引。

tb1_name.col_name记号在列名前使用表名进行修饰，这在连接前总是允许的，但是如果名字只出现在要连接表中的一个表内，那么可以被缩短为col_name。在那种情况下，MySQL可以清楚地确定该表是来自于哪个表，而表名修饰符不是必须的。我们不能在下面的连接中这样做，因为两个表都含有a_id列，因此列引用会有二义性：

mysql > SELECT * FROM artist INNER JOIN painting ON a_id = a_id;

ERROR 1052 (23000):Column 'a_id' in on clause is ambiguous

作为对比，下面的查询是非二义的，每个a_id的实例都使用适当的表名修饰，只有artist含有name列，只有painting含有title和state列：

mysql > SELECT name, title, state FROM artist INNER JOIN painting ON artist.a_id = painting.a_id;

为了使阅读者更清楚地掌握语句的含义，即使在不是必须的情况下，修饰列名也是有好处的，因此我倾向于在连接例子中使用修饰名。

如果你不希望在修饰列引用时需要写完整的表名，可以给每个表一个别名，并使用此别名指向它的列。下面两个语句是等价的：

SELECT artist.name, painting.title, states.name, painting.price
FROM artist INNER JOIN painting INNER JOIN states
ON artist.a_id = painting.a_id AND painting.state = states.abbrev;

SELECT a.name, p.title, s.name, p.price
FROM artist AS a INNER JOIN painting AS p INNER JOIN states AS s
ON a.a_id = p.a_id AND p.state = s.abbrev;

在AS alias_name子句中，AS是可选的。

对于选择多行的复杂语句，别名可以节省很多打字输入。此外，对于某类语句，别名不仅更方便而且也是必须的，当我们进入自连接（self-joins）主题（第12.3节）时，这会变得很明显。

12.2 查找与其他表不匹配的行

问题：

你需要在某个表中找到与另一个表不匹配的那些行，或者你需要产生一个基于表间连接的列表，在该列表中，对第一个表的每一行都有对应的条目，甚至当该行与第二个表并不匹配时也不例外。

解决方案：

使用外部连接－－－LEFT JOIN 或者 RIGHT JOIN。

讨论：

第12.1节关注的是内部连接，即在表之间发现匹配的连接。然而，解答某些问题需要确定哪些行并没有匹配（或者，换个说法，哪些行含有另一个表中所缺少的那些值）。举个例子，你或许需要知道在artist表中哪些画家的作品你还没有拥有。同样类型的问题在其他环境中也会出现，下面为一些例子：

你从事销售工作，并掌握了一个潜在客户的列表，以及已经定购你产品的客户列表。你需要在第一个列表中找出那些没有出现在第二个列表的客户。
你有一个棒球运动员的列表，和另一个打出过全垒打的运动员列表，并且你想知道第一个表中的哪些运动员没有打出过全垒打。解决方法是在第一个列表中找出那些没有出现在第二个表的运动员。

对于这些类型的问题，使用外部连接是必要的。和内部连接一样，外部连接也可以发现表间的匹配。但是与内部连接不同的是，外部连接还可以确定一个表中的哪些行与另一个表不相匹配。两种类型的外部连接为LEFT JOIN 和RIGHT JOIN。

为了了解为什么外部连接是有用的，让我们考虑一下确定aritst表中哪些画家没有出现在painting表中的问题。到目前为止，这些表都比较小，因此通过观察很容易知道结果：

mysql > SELECT * FROM artist ORDER BY a_id;

+------+----------+
| a_id | name |
+------+----------+
| 1 | Da Vinci |
| 2 | Monet |
| 3 | Van Gogh |
| 4 | Picasso |
| 5 | Renoir |
+------+----------+

mysql > SELECT * FROM painting ORDER BY a_id, p_id;

+------+------+-------------------+-------+-------+
| a_id | p_id | title | state | price |
+------+------+-------------------+-------+-------+
| 1 | 1 | The Last Supper | IN | 34 |
| 1 | 2 | The Mona Lisa | MI | 87 |
| 3 | 3 | Starry Night | KY | 48 |
| 3 | 4 | The Potato Eaters | KY | 67 |
| 3 | 5 | The Rocks | IA | 33 |
| 5 | 6 | Les Deux Soeurs | NE | 64 |
+------+------+-------------------+-------+-------+

通过查看这些表，你发现了没有Monet 和Picasso的作品（在painting表中没有a_id值为2和4的行）。但是当你获得的画作越来越多，painting表也越来越大，通过肉眼观察来回答这个问题将不再是件容易的事。那么你可以用SQL来回答它吗？当然可以，通常解决这个问题的第一个尝试是采用类似下面的语句，该语句使用了条件不等式以在两表间寻找不匹配项：

mysql > SELECT * FROM artist INNER JOIN painting ON artist.a_id != painting.a_id ORDER BY artist.a_id, painting.a_id;

+------+----------+------+------+-------------------+-------+-------+
| a_id | name | a_id | p_id | title | state | price |
+------+----------+------+------+-------------------+-------+-------+
| 1 | Da Vinci | 3 | 4 | The Potato Eaters | KY | 67 |
| 1 | Da Vinci | 3 | 3 | Starry Night | KY | 48 |
| 1 | Da Vinci | 3 | 5 | The Rocks | IA | 33 |
| 1 | Da Vinci | 5 | 6 | Les Deux Soeurs | NE | 64 |
| 2 | Monet | 1 | 2 | The Mona Lisa | MI | 87 |
| 2 | Monet | 1 | 1 | The Last Supper | IN | 34 |
| 2 | Monet | 3 | 3 | Starry Night | KY | 48 |
| 2 | Monet | 3 | 5 | The Rocks | IA | 33 |
| 2 | Monet | 3 | 4 | The Potato Eaters | KY | 67 |
| 2 | Monet | 5 | 6 | Les Deux Soeurs | NE | 64 |
| 3 | Van Gogh | 1 | 2 | The Mona Lisa | MI | 87 |
| 3 | Van Gogh | 1 | 1 | The Last Supper | IN | 34 |
| 3 | Van Gogh | 5 | 6 | Les Deux Soeurs | NE | 64 |
| 4 | Picasso | 1 | 1 | The Last Supper | IN | 34 |
| 4 | Picasso | 1 | 2 | The Mona Lisa | MI | 87 |
| 4 | Picasso | 3 | 3 | Starry Night | KY | 48 |
| 4 | Picasso | 3 | 5 | The Rocks | IA | 33 |
| 4 | Picasso | 3 | 4 | The Potato Eaters | KY | 67 |
| 4 | Picasso | 5 | 6 | Les Deux Soeurs | NE | 64 |
| 5 | Renoir | 1 | 2 | The Mona Lisa | MI | 87 |
| 5 | Renoir | 1 | 1 | The Last Supper | IN | 34 |
| 5 | Renoir | 3 | 4 | The Potato Eaters | KY | 67 |
| 5 | Renoir | 3 | 3 | Starry Night | KY | 48 |
| 5 | Renoir | 3 | 5 | The Rocks | IA | 33 |
+------+----------+------+------+-------------------+-------+-------+

输出显然是不正确的。（比如，它错误地指出每幅作品是由几个不同的画家画的。）问题在于该语句产生了两个表间所有画家的ID不同的值组合列表，而实际上你所需要的是一个artist表的值列表，且该列表中的所有值从未在painting表中出现过。此处的麻烦是内部连接只能基于两个表中都出现的值组合产生结果，却不能告诉你任何一个只在两表之一中缺少的值。

当面对在某个表中寻找与另一个表不匹配（即另一个表所缺少）的值的问题时，你应该养成如此思维的习惯，“啊，这是一个LEFT JOIN 问题。” LEFT JOIN 是外部连接的一种：它与内部连接类似，即试图将第一个（left）表和第二个（right）的表中的行相匹配。但是另一点，如果left表中没有与right表相匹配的行，LEFT JOIN仍将产生一行－－－该行中所有来自right表的列被设为NULL。这意味着你可以通过查找NULL来发现right表中所缺少的值。通过循序渐进的工作，更容易理解这是如何发生的，因此我们一开始以内部连接方式显示匹配行：

mysql > SELECT * FROM artist INNER JOIN painting ON artist.a_id = painting.a_id ORDER BY artist.a_id;

在这个输出中，第一个a_id列来自于artist表，而第二个则来自于painting表。现在将该结果与LEFT JOIN的输出进行对比，LEFT JOIN语句的写法非常类似INNER JOIN：

mysql > SELECT * FROM artist LEFT JOIN painting ON artist.a_id = painting.a_id ORDER BY artist.a_id;

+------+----------+------+------+-------------------+-------+-------+
| a_id | name | a_id | p_id | title | state | price |
+------+----------+------+------+-------------------+-------+-------+
| 1 | Da Vinci | 1 | 1 | The Last Supper | IN | 34 |
| 1 | Da Vinci | 1 | 2 | The Mona Lisa | MI | 87 |
| 2 | Monet | NULL | NULL | NULL | NULL | NULL |
| 3 | Van Gogh | 3 | 3 | Starry Night | KY | 48 |
| 3 | Van Gogh | 3 | 4 | The Potato Eaters | KY | 67 |
| 3 | Van Gogh | 3 | 5 | The Rocks | IA | 33 |
| 4 | Picasso | NULL | NULL | NULL | NULL | NULL |
| 5 | Renoir | 5 | 6 | Les Deux Soeurs | NE | 64 |
+------+----------+------+------+-------------------+-------+-------+

输出与内部连接的结果相似，但是LEFT JOIN还为artist表中的每一行产生了至少一个输出行，包括那些在painting表中没有匹配项的行。对于那些输出行，所有来自painting表的列都被设为NULL，而在内部连接中则不会产生这样的行。

下一步，为了将输出限制为只包含不匹配的artist行，需要增加在任意painting列的值中寻找NULL的WHERE子句，因为输出在与painting表匹配的情况下不可能包含NULL。这种做法过滤掉了内部连接所产生的行，只剩下由外部连接所产生的那些行。

mysql > SELECT * FROM artist LEFT JOIN painting ON artist.a_id = painting.a_id WHERE painting.a_id IS NULL ORDER BY artist.a_id;

最后，为了只显示在artist表中却不被painting表所拥有的值，需要缩短输出列的列表，以使其只包含来自于artist表的列。LEFT JOIN 列出了包含了的左表中行，而该行所包含的a_id值在右表中没有出现。

mysql > SELECT artist.* FROM artist LEFT JOIN painting ON artist.a_id = painting.a_id WHERE painting.a_id IS NULL ORDER BY artist.a_id;

+------+---------+
| a_id | name |
+------+---------+
| 2 | Monet |
| 4 | Picasso |
+------+---------+

一种类似的操作可以用来报告左表中的每个值并指示其是否位于右表。为了实现这个目的，需要执行LEFT JOIN，其中对同时出现在右表中的每个左表值进行计数。下面的语句列出了artist表中每个画家，并且显示你是否已经拥有了这些画家的作品：

mysql > SELECT artist.name, IF(COUNT(painting.a_id) > 0, 'yes', 'no') AS 'in collection' FROM artist LEFT JOIN painting ON artist.a_id = painting.a_id
GROUP BY artist.name;

+----------+---------------+
| name | in collection |
+----------+---------------+
| Da Vinci | yes |
| Monet | no |
| Picasso | no |
| Renoir | yes |
| Van Gogh | yes |
+----------+---------------+

RIGHT JOIN是另一种外部连接，它与LEFT JOIN类似，只不过将左表和右表的角色调换过来。从语义上说，RIGHT JOIN强制匹配过程为右表的每一条记录产生一行，即使左表中不存在相应的行。在语法上，tbl1 LEFT JOIN tbl2和tbl2 RIGHT JOIN tbl1是等价的，这意味着你可以像下面这样重写前面的LEFT JOIN语句，将之转换为RIGHT JOIN，并产生同样的结果：

mysql .> SELECT artist.name, IF(COUNT(painting.a_id) > 0, 'yes', 'no') AS 'in collection' FROM painting RIGHT JOIN artist ON artist.a_id = painting.a_id

GROUP BY artist.name;

编写LEFT JOIN 和RIGHT JOIN查询的其他方法：

在本书的其他地方，出于简便考虑通常只使用LEFT JOIN，但是如果你将左右表的角色对调，它们都可以被转化为RIGHT JOIN。与INNER JOIN一样，在外部连接中，如果两个表中待匹配的列名相同，并且是使用=操作符进行比较的话，你可以使用USING子句取代ON子句。举例来说，下面两条语句是等价的：

SELECT * FROM t1 LEFT JOIN t2 ON t1.n = t2.n;

SELECT * FROM t1 LEFT JOIN t2 USING(n);

下面两条也是：

SELECT * FROM t1 RIGHT JOIN t2 ON t1.n = t2.n;

SELECT * FROM t1 RIGHT JOIN t2 USING(n);

在特殊情况下，你希望根据两个表的所有列进行比较，那么可以使用NATURAL LEFT JOIN或者NATURAL RIGHT JOIN并省去ON或USING子句：

SELECT * FROM t1 NATURAL LEFT JOIN t2;

SELECT * FROM t1 NATURAL RIGHT JOIN t2;

参考：

正如本节中所展示的那样：LEFT JOIN对于查找在另一个表中无匹配的值，或者显示每个值是否有匹配是很有用的。LEFT JOIN还可能被用于产生包括表中所有条目的概要，即使其中某些条目无内容可被概要，这在刻画主表（master table）和从表（detail table）的关系时是很常见的。举个例子，LEFT JOIN 可以产生“每个顾客的总销售额”列表，其中包含了所有的顾客，虽然有部分顾客在表概要时还没有购买任何产品。（参考第12.4节以获取关于主－从表的更多信息。）

当你收到两个可能具有相关性的数据文件时，你需要确定它们是否真的相关，那么你可以使用LEFT JOIN执行一致性检查。（也就是说，你需要检查它们关系的完整性。）将每个文件导入到MySQL中，并运行两个LEFT JOIN 语句以确定是否在其中任何一个表中存在独立的行－－－在另一个表中无匹配项的行。第12.13节讨论了如何识别（并选择删除）这些独立行。

12.3 将表与自身进行比较

问题：

你希望将表中的行与同一个表中其他行进行比较。举例来说，你需要在你的收藏中找到创作“吃土豆的人（The Potato Eaters）的画家的所有作品”，或者你想要知道在states表中的哪些州与纽约是同一年加入联邦的，再或者你需要了解与其他任一州不在同一年加入联邦的州列表。

解决方案：

需要与表自身进行比较的问题牵涉到一个被称之为自连接（self-join）的操作。它的执行方式与其他的连接非常相似，除了一点，即你必须始终使用表别名，以便能够在一个语句中以不同方式引用同一个表。

讨论：

将一个表与另一个表进行连接的特殊情况是两个表为同一个，这称之为自连接。尽管不少人在一开始会对这种想法感到迷惑和奇怪，但这是完全合理的。你很可能将发现你经常使用自连接，因为它们是如此重要。

当你希望知道表中哪些元素满足某些条件时，你需要使用自连接。举个例子，假设你最喜欢的画作是“吃土豆的人”（The Potato Eaters），并且你想要在你的收藏中找到该画的作者的所有作品。那么可以如下操作：

1.在painting表中找到包含了画名为The Potato Eaters的行，以获得它的a_id值。

2.使用a_id值匹配表中具有相同a_id值的其他行。

3.显示所有匹配行的画名。

开始时的画家的ID和画作的titles如下所示：

mysql > SELECT a_id, title FROM painting ORDER BY a_id;

+------+-------------------+
| a_id | title             |
+------+-------------------+
|    1 | The Last Supper   |
|    1 | The Mona Lisa     |
|    3 | Starry Night      |
|    3 | The Potato Eaters |
|    3 | The Rocks         |
|    5 | Les Deux Soeurs   |
+------+-------------------+

一个不使用连接而找到正确画名的2－步方法为先用一个语句查找画家的ID，再在第二个语句中使用该ID选择匹配的行：

mysql > SELECT @id :=a_id FROM painting WHERE title = 'The Potato Eaters';

+------------+
| @id :=a_id |
+------------+
| 3 |
+------------+

mysql > SELECT title FROM painting WHERE a_id = @id;

+-------------------+
| title             |
+-------------------+
| Starry Night      |
| The Potato Eaters |
| The Rocks         |
+-------------------+

另一种不同的解决方式只需要一个语句，即使用自连接，其诀窍在于如何选择使用适当的符号。编写连接表自身的语句的第一次尝试通常与下类似：

mysql > SELECT title FROM painting INNER JOIN painting ON a_id = a_id WHERE title = 'The Potato Eaters';

[Err] 1066 - Not unique table/alias: 'painting'

该语句的问题在于列引用具有二义性，MySQL不能确定任一给定的列名所引用的painting表的实例。解决方法是至少给其中一个表实例起个别名，以便能够使用不同的表修饰符区分列引用。下面的语句展示具体做法，即使用别名p1和p2以两种方式引用painting表：

mysql > SELECT p2.title FROM painting AS p1 INNER JOIN painting AS p2 ON p1.a_id = p2.a_id WHERE p1.title = 'The Potato Eaters';

+-------------------+
| title             |
+-------------------+
| Starry Night      |
| The Potato Eaters |
| The Rocks         |
+-------------------+

该语句的输出表明了自连接的一些典型特征：当你开始在一个表实例中使用引用值（The Potato Eaters）以在第二个表实例中查找相匹配的行（同一画家的画作）时，其输出会包含所引用的值。这是有意义的：毕竟，该引用与其自身也是匹配的。如果你只需要找出同一画家的其他作品，那么可以显式地从输出中排除该引用值：

mysql > SELECT p2.title FROM painting AS p1 INNER JOIN painting AS p2 ON p1.a_id = p2.a_id WHERE p1.title = 'The Potato Eaters' AND p2.title != 'The Potato Eaters';

+--------------+
| title |
+--------------+
| Starry Night |
| The Rocks |
+--------------+

排除引用值的一个更常用不需要逐字地列出该值的方法，是指明你不需要在输出行中包含与引用相同的画名，无论这个画名是什么：

mysql > SELECT p2.title FROM painting AS p1 INNER JOIN painting AS p2 ON p1.a_id = p2.a_id WHERE p1.title = 'The Potato Eaters' AND p2.title != p1.title;

+--------------+
| title |
+--------------+
| Starry Night |
| The Rocks |
+--------------+

前面的语句在两个表实例中通过对ID值的比较来匹配行，实际上任何一种值都可以被用作此用途。举例来说，使用states表来回答“哪些州与纽约是同一年加入联邦的？”这个问题，即根据位于表中statehood列的日期的年部分进行两两对比：

mysql > SELECT s2.name, s2.statehood FROM states s1 INNER JOIN states s2 ON YEAR(s1.statehood) = YEAR(s2.statehood) WHERE s1.name = 'New York' ORDER BY s2.name;

+----------------+------------+
| name           | statehood |
+----------------+------------+
| Connecticut    | 1788-01-09 |
| Georgia        | 1788-01-02 |
| Maryland       | 1788-04-28 |
| Massachusetts | 1788-02-06 |
| New Hampshire | 1788-06-21 |
| New York       | 1788-07-26 |
| South Carolina | 1788-05-23 |
| Virginia       | 1788-06-25 |
+----------------+------------+

这里同样地，引用值（New York）在输出中出现。如果你需要防止这种情况，可以在ON子句中增加一个条件表达式，以显示地排除引用：

mysql > SELECT s2.name, s2.statehood FROM states AS s1 INNER JOIN states AS s2 ON YEAR(s1.statehood) = YEAR(s2.statehood) AND s1.name != s2.name WHERE s1.name = 'New York' ORDER BY s2.name;

+----------------+------------+
| name | statehood |
+----------------+------------+
| Connecticut | 1788-01-09 |
| Georgia | 1788-01-02 |
| Maryland | 1788-04-28 |
| Massachusetts | 1788-02-06 |
| New Hampshire | 1788-06-21 |
| South Carolina | 1788-05-23 |
| Virginia | 1788-06-25 |
+----------------+------------+

与找到创作“吃土豆的人”的画家的其他作品之问题类似，statehood问题同样可以借助用户定义的变量，并使用两个语句解决。当你在表中寻找单个精确的行匹配时，这种方式总是成立的。但是另外一些问题需要找到多行的匹配，此时2－语句方法就无法奏效了。假设你想要找到每一对在同一年加入联邦的州，该输出可能会包含states表中任意州的两两组合。这里没有固定的引用值，因此你不能通过变量来保存引用。对这个问题来说，自连接是完美的解决方案：

mysql > SELECT YEAR(s1.statehood) AS year, s1.name AS name1, s1.statehood AS statehood1, s2.name AS name2, s2.statehood AS statehood2
FROM states AS s1 INNER JOIN states AS s2 ON YEAR(s1.statehood) = YEAR(s2.statehood) AND s1.name != s2.name ORDER BY year, s1.name, s2.name;

+------+----------------+------------+----------------+------------+
| year | name1 | statehood1 | name2 | statehood2 |
+------+----------------+------------+----------------+------------+
| 1787 | Delaware | 1787-12-07 | New Jersey | 1787-12-18 |
| 1787 | Delaware | 1787-12-07 | Pennsylvania | 1787-12-12 |
| 1787 | New Jersey | 1787-12-18 | Delaware | 1787-12-07 |
| 1787 | New Jersey | 1787-12-18 | Pennsylvania | 1787-12-12 |
| 1787 | Pennsylvania | 1787-12-12 | Delaware | 1787-12-07 |
| 1787 | Pennsylvania | 1787-12-12 | New Jersey | 1787-12-18 |
| 1788 | Connecticut | 1788-01-09 | Georgia | 1788-01-02 |
| 1788 | Connecticut | 1788-01-09 | Maryland | 1788-04-28 |
| 1788 | Connecticut | 1788-01-09 | Massachusetts | 1788-02-06 |
| 1788 | Connecticut | 1788-01-09 | New Hampshire | 1788-06-21 |
| 1788 | Connecticut | 1788-01-09 | New York | 1788-07-26 |
| 1788 | Connecticut | 1788-01-09 | South Carolina | 1788-05-23 |
| 1788 | Connecticut | 1788-01-09 | Virginia | 1788-06-25 |
| 1788 | Georgia | 1788-01-02 | Connecticut | 1788-01-09 |
| 1788 | Georgia | 1788-01-02 | Maryland | 1788-04-28 |
| 1788 | Georgia | 1788-01-02 | Massachusetts | 1788-02-06 |
| 1788 | Georgia | 1788-01-02 | New Hampshire | 1788-06-21 |
| 1788 | Georgia | 1788-01-02 | New York | 1788-07-26 |
| 1788 | Georgia | 1788-01-02 | South Carolina | 1788-05-23 |
| 1788 | Georgia | 1788-01-02 | Virginia | 1788-06-25 |
| 1788 | Maryland | 1788-04-28 | Connecticut | 1788-01-09 |
| 1788 | Maryland | 1788-04-28 | Georgia | 1788-01-02 |
| 1788 | Maryland | 1788-04-28 | Massachusetts | 1788-02-06 |
| 1788 | Maryland | 1788-04-28 | New Hampshire | 1788-06-21 |
| 1788 | Maryland | 1788-04-28 | New York | 1788-07-26 |
| 1788 | Maryland | 1788-04-28 | South Carolina | 1788-05-23 |
| 1788 | Maryland | 1788-04-28 | Virginia | 1788-06-25 |
| 1788 | Massachusetts | 1788-02-06 | Connecticut | 1788-01-09 |
| 1788 | Massachusetts | 1788-02-06 | Georgia | 1788-01-02 |
| 1788 | Massachusetts | 1788-02-06 | Maryland | 1788-04-28 |
| 1788 | Massachusetts | 1788-02-06 | New Hampshire | 1788-06-21 |
| 1788 | Massachusetts | 1788-02-06 | New York | 1788-07-26 |
| 1788 | Massachusetts | 1788-02-06 | South Carolina | 1788-05-23 |
| 1788 | Massachusetts | 1788-02-06 | Virginia | 1788-06-25 |
| 1788 | New Hampshire | 1788-06-21 | Connecticut | 1788-01-09 |
| 1788 | New Hampshire | 1788-06-21 | Georgia | 1788-01-02 |
| 1788 | New Hampshire | 1788-06-21 | Maryland | 1788-04-28 |
| 1788 | New Hampshire | 1788-06-21 | Massachusetts | 1788-02-06 |
| 1788 | New Hampshire | 1788-06-21 | New York | 1788-07-26 |
| 1788 | New Hampshire | 1788-06-21 | South Carolina | 1788-05-23 |
| 1788 | New Hampshire | 1788-06-21 | Virginia | 1788-06-25 |
| 1788 | New York | 1788-07-26 | Connecticut | 1788-01-09 |
| 1788 | New York | 1788-07-26 | Georgia | 1788-01-02 |
| 1788 | New York | 1788-07-26 | Maryland | 1788-04-28 |
| 1788 | New York | 1788-07-26 | Massachusetts | 1788-02-06 |
| 1788 | New York | 1788-07-26 | New Hampshire | 1788-06-21 |
| 1788 | New York | 1788-07-26 | South Carolina | 1788-05-23 |
| 1788 | New York | 1788-07-26 | Virginia | 1788-06-25 |
| 1788 | South Carolina | 1788-05-23 | Connecticut | 1788-01-09 |
| 1788 | South Carolina | 1788-05-23 | Georgia | 1788-01-02 |
| 1788 | South Carolina | 1788-05-23 | Maryland | 1788-04-28 |
| 1788 | South Carolina | 1788-05-23 | Massachusetts | 1788-02-06 |
| 1788 | South Carolina | 1788-05-23 | New Hampshire | 1788-06-21 |
| 1788 | South Carolina | 1788-05-23 | New York | 1788-07-26 |
| 1788 | South Carolina | 1788-05-23 | Virginia | 1788-06-25 |
| 1788 | Virginia | 1788-06-25 | Connecticut | 1788-01-09 |
| 1788 | Virginia | 1788-06-25 | Georgia | 1788-01-02 |
| 1788 | Virginia | 1788-06-25 | Maryland | 1788-04-28 |
| 1788 | Virginia | 1788-06-25 | Massachusetts | 1788-02-06 |
| 1788 | Virginia | 1788-06-25 | New Hampshire | 1788-06-21 |
| 1788 | Virginia | 1788-06-25 | New York | 1788-07-26 |
| 1788 | Virginia | 1788-06-25 | South Carolina | 1788-05-23 |
| 1845 | Florida | 1845-03-03 | Texas | 1845-12-29 |
| 1845 | Texas | 1845-12-29 | Florida | 1845-03-03 |
| 1889 | Montana | 1889-11-08 | North Dakota | 1889-11-02 |
| 1889 | Montana | 1889-11-08 | South Dakota | 1889-11-02 |
| 1889 | Montana | 1889-11-08 | Washington | 1889-11-11 |
| 1889 | North Dakota | 1889-11-02 | Montana | 1889-11-08 |
| 1889 | North Dakota | 1889-11-02 | South Dakota | 1889-11-02 |
| 1889 | North Dakota | 1889-11-02 | Washington | 1889-11-11 |
| 1889 | South Dakota | 1889-11-02 | Montana | 1889-11-08 |
| 1889 | South Dakota | 1889-11-02 | North Dakota | 1889-11-02 |
| 1889 | South Dakota | 1889-11-02 | Washington | 1889-11-11 |
| 1889 | Washington | 1889-11-11 | Montana | 1889-11-08 |
| 1889 | Washington | 1889-11-11 | North Dakota | 1889-11-02 |
| 1889 | Washington | 1889-11-11 | South Dakota | 1889-11-02 |
| 1890 | Idaho | 1890-07-03 | Wyoming | 1890-07-10 |
| 1890 | Wyoming | 1890-07-10 | Idaho | 1890-07-03 |
| 1912 | Arizona | 1912-02-14 | New Mexico | 1912-01-06 |
| 1912 | New Mexico | 1912-01-06 | Arizona | 1912-02-14 |
| 1959 | Alaska | 1959-01-03 | Hawaii | 1959-08-21 |
| 1959 | Hawaii | 1959-08-21 | Alaska | 1959-01-03 |
+------+----------------+------------+----------------+------------+

ON 子句中的条件要求两个州的名字不能相同，如此消除多余的重复行，这些重复行由于每个州与它自己也是同一年加入联邦而造成的。但是你会发现剩下的每个州的两两组合都出现了两次。举个例子，其中有一行列出了Delaware和New Jersey，但是另外一行列出了New Jersey和Delaware。这是自连接常常导致的情况：即产生包含同样值的行对，只不过值出现的顺序不同。关于从查询结果集中消除这些“疑似重复项”的技术，参见第14.5节。

某些自连接问题属于“哪些值与表中其他行不匹配？”的类型。一个例子是“哪些州与其他任何州都不是在同一年加入联邦的？”问题。找到这些州属于“非匹配”问题，这是典型的涉及 LEFT JOIN的一类问题。在些情况下，解决方案是对states表与其自身使用LEFT JOIN：

mysql > SELECT s1.name, s1.statehood
FROM states AS s1 LEFT JOIN states AS s2 ON YEAR(s1.statehood) = YEAR(s2.statehood) AND s1.name != s2.name WHERE s2.name IS NULL ORDER BY s1.statehood;

+----------------+------------+
| name | statehood |
+----------------+------------+
| North Carolina | 1789-11-21 |
| Rhode Island | 1790-05-29 |
| Vermont | 1791-03-04 |
| Kentucky | 1792-06-01 |
| Tennessee | 1796-06-01 |
| Ohio | 1803-03-01 |
| Louisiana | 1812-04-30 |
| Indiana | 1816-12-11 |
| Mississippi | 1817-12-10 |
| Illinois | 1818-12-03 |
| Alabama | 1819-12-14 |
| Maine | 1820-03-15 |
| Missouri | 1821-08-10 |
| Arkansas | 1836-06-15 |
| Michigan | 1837-01-26 |
| Iowa | 1846-12-28 |
| Wisconsin | 1848-05-29 |
| California | 1850-09-09 |
| Minnesota | 1858-05-11 |
| Oregon | 1859-02-14 |
| Kansas | 1861-01-29 |
| West Virginia | 1863-06-20 |
| Nevada | 1864-10-31 |
| Nebraska | 1867-03-01 |
| Colorado | 1876-08-01 |
| Utah | 1896-01-04 |
| Oklahoma | 1907-11-16 |
+----------------+------------+

该语句为states表的每一行选择与其statehood列中年相同的州并排除了该州自身。对于那些没有这项匹配的行，LEFT JOIN仍然强制输出包含一个连接行，其中s2的所有列被设为NULL，而正是这些行标识了与其他州不是在同一年加入联邦的州。

12.4 产生主从列表和摘要

问题：

两个相关表具有主从关系，并且你希望产生一个列表以同时显示每一个主行与对应的从行，或者为每一个主行产生其从行的摘要。

解决方案：

这是一对多的关系。对该问题的解决方案会包含连接操作，但是连接的类型与你要回答的问题有关。如果产生一个只包含一些存在从行的主行列表，可以使用基于主表的主关键字的内部连接。如果产生的列表包含所有主行，包括那些没有从行的（主行），可以使用外部连接。

讨论：

从两个相关表中产生一个列表经常是有用的。对于那些具有主从关系或父子关系的表，其中一个表的给定行或许和另一表中的几行相匹配。本节提出了一些你可以提问（并回答）的此类问题，与前一节一样，同样使用了artist和painting表。

对于这些表的一种主从问题的形式是，“每幅画作的画者是谁？” 这是一个简单的内部连接，即根据画家的ID值将每个painting表与对应的artist行进行匹配：

mysql > SELECT artist.name, painting.title FROM artist INNER JOIN painting ON artist.a_id = painting.a_id ORDER BY name, title;

在你希望只列出具有从行的主行时，内部连接可以满足要求。然而，另一种你可以提出的主从问题的形式是，“每个画家画了哪些作品？”。这个问题与前一个相似，但不完全一样。如果在artist表中存在painting表中没有出现过的画家，那么此问题将有不同的答案，并且需要一个不同的语句以产生正确的回答。在些情况下，连接的输出应当包括一个表中与另一个表无匹配的那些行，这是需要使用外部连接（第12.2节）的“查找非匹配行”问题的一种形式。因此，为了显示每个artist行，无论其是否具有对应的painting行，应使用LEFT JOIN：

mysql > SELECT artist.name, painting.title FROM artist LEFT JOIN painting ON artist.a_id = painting.a_id ORDER BY name, title;

结果中title列为NULL的行对应了存在于artist表中但是你没有收藏其作品的那些画家。

同样的准则也适用于为主从表产生摘要的情况。举例来说，为了根据每个画家的作品数来概述你的艺术收藏，你或许会提出，“在painting表中每个画家的作品有多少？”的问题。为了根据画家的ID找到答案，你可以很容易地使用下面语句对画作进行计数：

mysql > SELECT a_id, COUNT(a_id) AS counts FROM painting GROUP BY a_id;

+------+--------+
| a_id | counts |
+------+--------+
| 1 | 2 |
| 3 | 3 |
| 5 | 1 |
+------+--------+

当然，上面的输出实际上没有任何意义，除非你能够记住所有画家的ID号。为了显示画家们的姓名而不是ID，需要连接painting表与artist表：

mysql > SELECT artist.name AS painter, COUNT(painting.a_id) AS counts FROM artist INNER JOIN painting ON artist.a_id = painting.a_id GROUP BY artist.name;

+----------+--------+
| painter | counts |
+----------+--------+
| Da Vinci | 2 |
| Renoir | 1 |
| Van Gogh | 3 |
+----------+--------+

另一方面，你可能会提问，“每个画家画了多少幅作品？”。只要每个artist表中的画家都至少具有一个相应painting表的行，那么此问题与前一个是完全等同的（并且可以使用同样的语句来回答）。但是如果在artist表中含有你没有收藏其作品的画家，它们将不会出现在该语句的输出中。为了产生每个画家的计数摘要，甚至包括那些在painting表中没有画作的画家，应该使用LEFT JOIN：

mysql > SELECT artist.name AS painter, COUNT(painting.a_id) AS counts FROM artist LEFT JOIN painting ON artist.a_id = painting.a_id GROUP BY artist.name;

+----------+--------+
| painter | counts |
+----------+--------+
| Da Vinci | 2 |
| Monet | 0 |
| Picasso | 0 |
| Renoir | 1 |
| Van Gogh | 3 |
+----------+--------+

在编写这种语句时要注意一个容易产生的细小错误。假设你在写COUNT()函数时有些细微的改变，如下所示：

mysql > SELECT artist.name AS painter, COUNT(*) AS counts FROM artist LEFT JOIN painting ON artist.a_id = painting.a_id GROUP BY artist.name;

+----------+--------+
| painter | counts |
+----------+--------+
| Da Vinci | 2 |
| Monet | 1 |
| Picasso | 1 |
| Renoir | 1 |
| Van Gogh | 3 |
+----------+--------+

现在每个画家看起来都至少含有一幅作品。为什么结果不同了呢？产生此问题的原因在于使用了COUNT(*)而不是COUNT(painting.a_id)。LEFT JOIN的工作方式是为左表中（与右表）不匹配的行同样产生连接行，而该连接行中所有来自右表的列被设为NULL。在本例中，右表为painting表，因此使用COUNT(painting.a_id)的语句是正确的，因为COUNT(expr)仅对非空值计数。而使用COUNT(*)的语句错在对所有的连接行计数，甚至那些包含NULL值即对应无画作的画家的行。LEFT JOIN对其他类型的摘要同样是合适的，为了产生显示artist表中每个画家的作品数总计和平均值，可以使用下面的语句：

mysql > SELECT artist.name AS painter, COUNT(painting.a_id) AS 'number of paintings', SUM(painting.price) AS 'total price', AVG(painting.price) AS 'average price' FROM artist LEFT JOIN painting ON artist.a_id = painting.a_id GROUP BY artist.name;

+----------+---------------------+-------------+---------------+
| painter | number of paintings | total price | average price |
+----------+---------------------+-------------+---------------+
| Da Vinci | 2 | 121 | 60.5000 |
| Monet | 0 | NULL | NULL |
| Picasso | 0 | NULL | NULL |
| Renoir | 1 | 64 | 64.0000 |
| Van Gogh | 3 | 148 | 49.3333 |
+----------+---------------------+-------------+---------------+

注意对那些你没有收藏其作品的画家，COUNT()函数的结果是零，而SUM()和AVG()的结果是NULL，这是因为后两个函数在应用到一个不包含非空值的值集合时，会返回NULL。为了在此情况下将sum或average值显示为零，应该修改语句，即首先使用IFNULL()测试SUM()或AVG()的返回值：

mysql >

SELECT artist.name AS painter, COUNT(painting.a_id) AS 'number of paintings',
IFNULL(SUM(painting.price), 0) AS 'total price',
IFNULL(AVG(painting.price), 0) AS 'average price' FROM artist LEFT JOIN painting ON artist.a_id = painting.a_id GROUP BY artist.name;

+----------+---------------------+-------------+---------------+
| painter | number of paintings | total price | average price |
+----------+---------------------+-------------+---------------+
| Da Vinci | 2 | 121 | 60.5000 |
| Monet | 0 | 0 | 0.0000 |
| Picasso | 0 | 0 | 0.0000 |
| Renoir | 1 | 64 | 64.0000 |
| Van Gogh | 3 | 148 | 49.3333 |
+----------+---------------------+-------------+---------------+

12.5 枚举多对多的关系

问题：

你希望显示这样的表间关系，即一个表中的多行可能和另一个表中的多个行相匹配。

解决方案：

这是多对多的关系，它需要第3个表以关联两个原来的表并使用3－向连接（three-way join）来列出它们之间的关系。

讨论：

前面章节中使用的atritst和painting表是一对多的关系：一个给定的画家可能创作了许多作品，但是每幅作品只能被唯一一位画家所创作。一对多关系相对比较简单，两个具有此关系的表可以使用它们共有的关键字进行连接。

更简单的是一对一关系，此关系经常被用来执行查找一个值集到另一个值集的映射。举个例子，states表包含了州的名字与简写列，可以列出完整的州名以及它们对应的简写：

mysql > SELECT name, abbrev FROM states;

+----------------+--------+
| name | abbrev |
+----------------+--------+
| Alaska | AK |
| Alabama | AL |
| Arkansas | AR |
| Arizona | AZ |
| California | CA |
| Colorado | CO |
| Connecticut | CT |
| Delaware | DE |
| Florida | FL |
| Georgia | GA |
| Hawaii | HI |
| Iowa | IA |
| Idaho | ID |
| Illinois | IL |
| Indiana | IN |
| Kansas | KS |
| Kentucky | KY |
| Louisiana | LA |
| Massachusetts | MA |
| Maryland | MD |
| Maine | ME |
| Michigan | MI |
| Minnesota | MN |
| Missouri | MO |
| Mississippi | MS |
| Montana | MT |
| North Carolina | NC |
| North Dakota | ND |
| Nebraska | NE |
| New Hampshire | NH |
| New Jersey | NJ |
| New Mexico | NM |
| Nevada | NV |
| New York | NY |
| Ohio | OH |
| Oklahoma | OK |
| Oregon | OR |
| Pennsylvania | PA |
| Rhode Island | RI |
| South Carolina | SC |
| South Dakota | SD |
| Tennessee | TN |
| Texas | TX |
| Utah | UT |
| Virginia | VA |
| Vermont | VT |
| Washington | WA |
| Wisconsin | WI |
| West Virginia | WV |
| Wyoming | WY |
+----------------+--------+

这种一一对应的关系可以用来映射painting表中州名称的缩写，其中painting表中包含一列数据以指明每一幅作品是在哪一个州购买的。如果没有映射，那么painting表的条目显示出来是这样的：

mysql > SELECT title, state FROM painting ORDER BY state;

+-------------------+-------+
| title | state |
+-------------------+-------+
| The Rocks | IA |
| The Last Supper | IN |
| Starry Night | KY |
| The Potato Eaters | KY |
| The Mona Lisa | MI |
| Les Deux Soeurs | NE |
+-------------------+-------+

如果你想知道完整的州名而不是简写，可以利用states表中所列举的它们间的一对一关系。下面的方法将该表与painting表相连接，通过使用两表共有的州名简写值：

mysql > SELECT painting.title, states.name AS state FROM painting INNER JOIN states ON painting.state = states.abbrev ORDER BY state;

表间更复杂的关系是多对多关系，此关系发生在当一个表中的行与另一个表具有多个匹配，并且反过来也一样时。数据库书籍常常会使用“零件与供应商”问题来说明这种关系。（一个给定的零件可能有好几家供应商供货，那么你如何才能产生显示哪些零件是由哪些供应商所提供的列表呢？）但是，由于我们已经太多次看到这个例子，我宁愿使用一个不同的例子。因此让我们考虑下面的场景，虽然从概念上来说它实际上是同一回事：你和你的一帮朋友是狂热的尤克牌（euchre）爱好者，并且举行4人1组的纸牌比赛，其中每两人为一对。每年，你们都聚集在一起，结成对家，并开始友好的比赛。自然地，不同的玩家对每次比赛结果的记忆不同，为了避免口舌之争，你必须在数据库中记录结对情况与结果。记录比赛结果的一种方式是建立如下的一个表，对每一次比赛的年份内，你需要记录下团队的名字、输赢记录、玩家以及玩家居住的城市：

mysql > SELECT * FROM euchre ORDER BY year, wins DESC, player;

+----------+------+------+--------+----------+-------------+
| team | year | wins | losses | player | player_city |
+----------+------+------+--------+----------+-------------+
| Kings | 2005 | 10 | 2 | Ben | Cork |
| Kings | 2005 | 10 | 2 | Billy | York |
| Crowns | 2005 | 7 | 5 | Melvin | Dublin |
| Crowns | 2005 | 7 | 5 | Tony | Derry |
| Stars | 2005 | 4 | 8 | Franklin | Bath |
| Stars | 2005 | 4 | 8 | Wallace | Cardiff |
| Sceptres | 2005 | 3 | 9 | Maurice | Leeds |
| Sceptres | 2005 | 3 | 9 | Nigel | London |
| Crowns | 2006 | 9 | 3 | Ben | Cork |
| Crowns | 2006 | 9 | 3 | Tony | Derry |
| Kings | 2006 | 8 | 4 | Franklin | Bath |
| Kings | 2006 | 8 | 4 | Nigel | London |
| Stars | 2006 | 5 | 7 | Maurice | Leeds |
| Stars | 2006 | 5 | 7 | Melvin | Dublin |
| Sceptres | 2006 | 2 | 10 | Billy | York |
| Sceptres | 2006 | 2 | 10 | Wallace | Cardiff |
+----------+------+------+--------+----------+-------------+

如上表所显示的那样，每个队有多个玩家，且每个玩家可以参加多个队。该表获取了这种自然的多对多的关系，但是是以一个非正规的形式，因为每行不必要地保存了不少重复信息。（每支队伍的信息同时作为每个玩家的信息被记录了多次。）一个更好地表现此多对多关系的方法是使用多重表：

只在euchre_team表中保存一次每个团队的名称、年度和记录。
只在euchre_player表中保存一次每个玩家的姓名、居住地。
建立第3个表euchre_link，该表作为两个基本表的连接或者说桥梁，保存团队－玩家间的联系。为了将此表保存的信息最小化，为每个团队和玩家在它们各自的表中赋予一个唯一的ID，并且在euchre_link表中只保存这些ID。

因此团队和玩家表如下所示：

mysql > SELECT * FROM euchre_team;

+----+----------+------+------+--------+
| id | name | year | wins | losses |
+----+----------+------+------+--------+
| 1 | Kings | 2005 | 10 | 2 |
| 2 | Crowns | 2005 | 7 | 5 |
| 3 | Stars | 2005 | 4 | 8 |
| 4 | Sceptres | 2005 | 3 | 9 |
| 5 | Kings | 2006 | 8 | 4 |
| 6 | Crowns | 2006 | 9 | 3 |
| 7 | Stars | 2006 | 5 | 7 |
| 8 | Sceptres | 2006 | 2 | 10 |
+----+----------+------+------+--------+

mysql > SELECT * FROM euchre_player;

+----+----------+---------+
| id | name | city |
+----+----------+---------+
| 1 | Ben | Cork |
| 2 | Billy | York |
| 3 | Tony | Derry |
| 4 | Melvin | Dublin |
| 5 | Franklin | Bath |
| 6 | Wallace | Cardiff |
| 7 | Nigel | London |
| 8 | Maurice | Leeds |
+----+----------+---------+

euchre_link表以如下方式关联团队和玩家信息：

mysql > SELECT * FROM euchre_link;

+---------+-----------+
| team_id | player_id |
+---------+-----------+
| 1 | 1 |
| 1 | 2 |
| 2 | 3 |
| 2 | 4 |
| 3 | 5 |
| 3 | 6 |
| 4 | 7 |
| 4 | 8 |
| 5 | 5 |
| 5 | 7 |
| 6 | 1 |
| 6 | 3 |
| 7 | 4 |
| 7 | 8 |
| 8 | 2 |
| 8 | 6 |
+---------+-----------+

为了使用这些表来回答关于团队与玩家的问题，你需要执行一个3－向连接，即利用连接表来使两个基本表相互关联。下面为一些例子：

列出所有的配对以显示每个团队及参加此队的玩家。此语句列举了所有euchre_team和euchre_player表的对应关系，并且重现了原来位于非正规的euchre表中的信息：

mysql > SELECT t.name, t.year, t.wins, t.losses, p.name, p.city
FROM euchre_team AS t INNER JOIN euchre_link AS l
INNER JOIN euchre_player AS p
ON t.id = l.team_id AND p.id = l.player_id
ORDER BY t.year, t.wins DESC, p.name;

或：

SELECT te.name, te.year, te.wins, te.losses, p.name, p.city FROM euchre_player p INNER JOIN(
SELECT *
FROM euchre_team AS t INNER JOIN euchre_link AS l
ON t.id = l.team_id) AS te ON p.id = te.player_id;

+----------+------+------+--------+----------+---------+
| name | year | wins | losses | name | city |
+----------+------+------+--------+----------+---------+
| Kings | 2005 | 10 | 2 | Ben | Cork |
| Kings | 2005 | 10 | 2 | Billy | York |
| Crowns | 2005 | 7 | 5 | Melvin | Dublin |
| Crowns | 2005 | 7 | 5 | Tony | Derry |
| Stars | 2005 | 4 | 8 | Franklin | Bath |
| Stars | 2005 | 4 | 8 | Wallace | Cardiff |
| Sceptres | 2005 | 3 | 9 | Maurice | Leeds |
| Sceptres | 2005 | 3 | 9 | Nigel | London |
| Crowns | 2006 | 9 | 3 | Ben | Cork |
| Crowns | 2006 | 9 | 3 | Tony | Derry |
| Kings | 2006 | 8 | 4 | Franklin | Bath |
| Kings | 2006 | 8 | 4 | Nigel | London |
| Stars | 2006 | 5 | 7 | Maurice | Leeds |
| Stars | 2006 | 5 | 7 | Melvin | Dublin |
| Sceptres | 2006 | 2 | 10 | Billy | York |
| Sceptres | 2006 | 2 | 10 | Wallace | Cardiff |
+----------+------+------+--------+----------+---------+

列出特定团队的所有成员（2005年的Crowns队）：

mysql > SELECT p.name, p.city
FROM euchre_team AS t INNER JOIN euchre_link AS l
INNER JOIN euchre_player AS p
ON t.id = l.team_id AND p.id = l.player_id
AND t.name = 'Crowns' AND t.year = 2005;

列出给定玩家（Billy）曾经参与的所有团队：

mysql > SELECT t.name, t.year, t.wins, t.losses
FROM euchre_team AS t INNER JOIN euchre_link AS l
INNER JOIN euchre_player AS p
ON t.id = l.team_id AND p.id = l.player_id
WHERE p.name = 'Billy';

+----------+------+------+--------+
| name | year | wins | losses |
+----------+------+------+--------+
| Kings | 2005 | 10 | 2 |
| Sceptres | 2006 | 2 | 10 |
+----------+------+------+--------+

12.6 查找每组行中含有最大或最小值的行

问题：

你希望找到表中每组行中的哪一行包含了给定列的最大或最小值。举个例子，你想知道在你的收藏中每个画家最贵的作品。

解决方案：

建立一个临时表以保存每组的最大或最小值，并将临时表和原始表进行连接以得到每组中匹配的行。如果你喜欢用单个查询语句来解决，可以在FROM子句中使用子查询，而不是临时表。

讨论：

很多问题涉及在特定的表列中查找最大值或最小值，并且你希望知道包含此值的行中其他列的值也是非常普遍的。举例来说，当你正使用artist和painting表的时候，有可能被问到像这样的问题：“哪幅画作在你的收藏中是最贵的，并且其作者是谁？”一种方法是将最高的价格保存在用户定义变量中，再使用该变量确定包含此价格的行，以便能够从中获取其他列的值：

mysql > SET @max_price = (SELECT MAX(price) FROM painting);

mysql > SELECT artist.name, painting.title, painting.price FROM artist INNER JOIN painting ON painting.a_id = artist.a_id WHERE painting.price = @max_price;

+----------+---------------+-------+
| name | title | price |
+----------+---------------+-------+
| Da Vinci | The Mona Lisa | 87 |
+----------+---------------+-------+

同样的问题也可以通过创建临时表来保存最高价格，然后将之与另一个表相连接来解决：

mysql > CREATE TABLE tmp SELECT MAX(price) AS max_price FROM painting;

mysql > SELECT artist.name, painting.title, painting.price FROM artist INNER JOIN painting INNER JOIN tmp ON painting.a_id = artist.a_id AND painting.price = tmp.max_price;

上面展示的使用用户定义变量或临时表的技术在第8.5节中有所阐述。此处它们的用法与其类似，只不过现在我们将它们用在了多重表上。

从表面上看，使用临时表和连接解决此问题比使用用户定义变量要更复杂一点，但这项技术有实际价值吗？是的，当然有，因为它引申了一项更通用的技术，以解决更复杂的问题。前面的语句只显示了在整个表中最贵的单幅画作的信息，如果你的问题是，“每个画家的最贵作品是什么？”，那么你将无法使用用户定义变量去回答这个，因为此解答需要为每个画家找到最高价格，而一个变量一次只能保存一个值。然而使用临时表的技术仍然可以工作得很好，因为表可以包含多个行，并且通过连接可以找到它们的所有匹配。

为了回答此问题，选择每个画家的ID和相应的最高作品价格并存入一个临时表。该表保存的不是所有作品最高价格而是每组的最大值，“组”在此被定义为“给定画家的作品”。然后使用保存在临时表中的画家ID和价格与painting表中的行相匹配，并将结果与artist表连接以得到画家的姓名：

mysql > CREATE TABLE tmpprice SELECT a_id, MAX(price) AS max_price FROM painting GROUP BY painting.a_id;

mysql > SELECT artist.name, painting.title, painting.price FROM artist INNER JOIN painting INNER JOIN tmpprice
ON painting.a_id = artist.a_id AND painting.a_id = tmpprice.a_id AND painting.price = tmpprice.max_price;

为了在单个语句获得同样的结果，可以在FROM子句中使用子查询在临时表中获取相同的行：

mysql > SELECT artist.name, painting.title, painting.price FROM artist INNER JOIN painting INNER JOIN
(SELECT a_id, MAX(price) AS max_price FROM painting GROUP BY a_id)
AS tmp
ON painting.a_id = artist.a_id AND painting.a_id = tmp.a_id AND painting.price = tmp.max_price;

另一种回答每组最大值问题的方法是使用LEFT JOIN以将表与其自身相连接。下面的语句确定了每个画家ID的最高价格的作品（我们使用 IS NULL 来选择属于p1的并且在p2 中没有比其价格更高的那些行）：

mysql > SELECT p1.a_id, p1.title, p1.price FROM painting AS p1 LEFT JOIN painting AS p2
ON p1.a_id = p2.a_id AND p1.price < p2.price WHERE p2.a_id IS NULL;

+------+-------------------+-------+
| a_id | title | price |
+------+-------------------+-------+
| 1 | The Mona Lisa | 87 |
| 3 | The Potato Eaters | 67 |
| 5 | Les Deux Soeurs | 64 |
+------+-------------------+-------+

为了显示画家的姓名而不是ID值，将LEFT JOIN的结果与artist表相连接：

mysql > SELECT artist.name, p1.title, p1.price
FROM painting AS p1 LEFT JOIN painting AS p2 ON p1.a_id = p2.a_id AND p1.price < p2.price
INNER JOIN artist ON p1.a_id = artist.a_id
WHERE p2.a_id IS NULL;

与自身左连接的方法或许比使用临时表或子查询稍显得更不直观一些。

上面的技术同样可用于其他类型的值，比如临时值。假设driver_log表列出了司机及其行驶的里程：

mysql > SELECT name, trav_date, miles FROM driver_log ORDER BY name, trav_date;

+-------+------------+-------+
| name | trav_date | miles |
+-------+------------+-------+
| Ben | 2006-08-29 | 131 |
| Ben | 2006-08-30 | 152 |
| Ben | 2006-09-02 | 79 |
| Henry | 2006-08-26 | 115 |
| Henry | 2006-08-27 | 96 |
| Henry | 2006-08-29 | 300 |
| Henry | 2006-08-30 | 203 |
| Henry | 2006-09-01 | 197 |
| Suzi | 2006-08-29 | 391 |
| Suzi | 2006-09-02 | 502 |
+-------+------------+-------+

对于此表，一种分组最大值的问题是“显示每个司机最近的行驶里程”。它可以使用如下的临时表来解决：

mysql > CREATE TABLE tmpdriverlog SELECT name, MAX(trav_date) AS trav_date FROM driver_log GROUP BY name;

mysql > SELECT driver_log.name, driver_log.trav_date, driver_log.miles FROM driver_log
INNER JOIN tmpdriverlog ON driver_log.name = tmpdriverlog.name AND driver_log.trav_date = tmpdriverlog.trav_date
ORDER BY driver_log.name;

+-------+------------+-------+
| name | trav_date | miles |
+-------+------------+-------+
| Ben | 2006-09-02 | 79 |
| Henry | 2006-09-01 | 197 |
| Suzi | 2006-09-02 | 502 |
+-------+------------+-------+

同样，你可以在FROM子句中使用子查询来解决，如下所示：

mysql > SELECT driver_log.name, driver_log.trav_date, driver_log.miles FROM driver_log
INNER JOIN (SELECT name, MAX(trav_date) AS trav_date FROM driver_log GROUP BY name) AS tmpdriverlog
ON driver_log.name = tmpdriverlog.name AND driver_log.trav_date = tmpdriverlog.trav_date
ORDER BY driver_log.name;

哪种技术更好：临时表还是在FROM子句中的子查询？对于较小的表，两种方式或许没有多大的区别。但如果临时表或子查询的结果很大，使用临时表一般具有一个优点，即你可以在创建它之后，使用它进行连接之前，先对其做索引。

参考：

本节展示了如何解答分组最大值的，即通过选择摘要信息存入临时表并将之与原始表连接，或者在FROM子句中使用子查询。这些技术在很多情况下有用，比如计算团队等级，每个团队的等级是根据组中每个团队与最高纪录的团队相比较的结果而决定的。第12.7节讨论了具体的做法。

12.7 计算队伍排名

问题：

你需要根据球队的输赢记录来计算球队的排名，包括比赛落后（games-behind, GB）值。

解决方案：

先确定哪支球队处于第一位，然后将结果与原来的行相连接。

讨论：