连接与子查询(一)

有些问题无法简单地通过单个表来解决，而且只有当你将多个来源的信息组织在一起时，关系数据库才能发挥出真正的威力:

与单个表中获取信息对比，将多个表的行组织在一起才能获得更加全面的信息
多阶段操作时，可以保存中间结果
可以根据一个表的信息，修改另一个表中的行

表之间的结合

问题:
如何利用来自多个表的信息执行任务？

解决方案:
使用连接，即在 FROM 子句中使用多个表的查询，并提供信息指定在 MySQL 中结合这些表的方法

讨论:
连接背后的基本思想是将一个表的行与其他一个或多个表的行为结合。
当每个表只能解决问题的一部分时，你可以利用连接将多个表的信息结合在一起

两个表的全连接会生成所有行可能出现的组合，称为笛卡尔积。
例如，如果一个 100 行的表与另一个 200 行的表中的每一行都相互连接，则会生成包含 100 * 200 = 20000 行的结果。
对于较大的表，或两个以上表之间的连接，笛卡尔积的结果集很容易变得非常庞大，因此连接通常需要指定 ON 或 USING 比较子句，以保证只生成表之间所需的结合(这要求每个表都有一列或多列共同的信息，你可以通过这些列将这些表做逻辑连接)。
你还可以指定一个 WHERE 子句，来限制选择哪些连接的行。每个子句都可以缩小查询的重点。

以下示例假设你有一个艺术收藏品，并通过以下两个表来记录你的收购。
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)
);

INSERT INTO artist VALUES (1, 'Da Vinci');
INSERT INTO artist VALUES (2, 'Monet');
INSERT INTO artist VALUES (3, 'Van Gogh');
INSERT INTO artist VALUES (4, 'Renoir');

MySQL root@(none):test_db> SELECT * FROM artist ORDER BY a_id;
+------+----------+
| a_id | name     |
+------+----------+
| 1    | Da Vinci |
| 2    | Monet    |
| 3    | Van Gogh |
| 4    | Renoir   |
+------+----------+

INSERT INTO painting VALUES (1, 1, 'The Last Supper', 'IN', 34);
INSERT INTO painting VALUES (1, 2, 'Mona Lisa', 'MI', 87);
INSERT INTO painting VALUES (3, 3, 'Starry Night', 'KY', 48);
INSERT INTO painting VALUES (3, 4, 'The Potato Eaters', 'KY', 67);
INSERT INTO painting VALUES (4, 5, 'Lex Deux Soeurs', 'NE', 64);

MySQL root@(none):test_db> 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    | Mona Lisa         | MI    | 87    |
| 3    | 3    | Starry Night      | KY    | 48    |
| 3    | 4    | The Potato Eaters | KY    | 67    |
| 4    | 5    | Lex Deux Soeurs   | NE    | 64    |
+------+------+-------------------+-------+-------+

每个表都包含了有关你的收藏的部分信息。例如，artist 表不会告诉你每位艺术家都创作了哪些画作，painting 表只保存了艺术家 ID，却没有他们的名字。
如果想使用两个表中的信息，那么需要编写一个连接查询。连接可以在 FROM 关键字后面指定两个或多个表。
在输出列的列表中，使用 * 可以选择所有表中的所有列，tbl_name.* 可以选择指定表中的所有列，你可以指定连接表中的特定列或使用基于这些列的表达式。

最简单的连接是只有两个表，并选择每个表中的所有列。下列 artist 和 painting 表之间的连接就是最简单的连接(通过 ORDER BY 子句让结果更易于阅读):

MySQL root@(none):test_db> SELECT * FROM artist INNER JOIN painting 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    | Mona Lisa         | MI    | 87    |
| 1    | Da Vinci | 3    | 3    | Starry Night      | KY    | 48    |
| 1    | Da Vinci | 3    | 4    | The Potato Eaters | KY    | 67    |
| 1    | Da Vinci | 4    | 5    | Lex Deux Soeurs   | NE    | 64    |
| 2    | Monet    | 1    | 1    | The Last Supper   | IN    | 34    |
| 2    | Monet    | 1    | 2    | Mona Lisa         | MI    | 87    |
| 2    | Monet    | 3    | 3    | Starry Night      | KY    | 48    |
| 2    | Monet    | 3    | 4    | The Potato Eaters | KY    | 67    |
| 2    | Monet    | 4    | 5    | Lex Deux Soeurs   | NE    | 64    |
| 3    | Van Gogh | 1    | 1    | The Last Supper   | IN    | 34    |
| 3    | Van Gogh | 1    | 2    | Mona Lisa         | MI    | 87    |
| 3    | Van Gogh | 3    | 3    | Starry Night      | KY    | 48    |
| 3    | Van Gogh | 3    | 4    | The Potato Eaters | KY    | 67    |
| 3    | Van Gogh | 4    | 5    | Lex Deux Soeurs   | NE    | 64    |
| 4    | Renoir   | 1    | 1    | The Last Supper   | IN    | 34    |
| 4    | Renoir   | 1    | 2    | Mona Lisa         | MI    | 87    |
| 4    | Renoir   | 3    | 3    | Starry Night      | KY    | 48    |
| 4    | Renoir   | 3    | 4    | The Potato Eaters | KY    | 67    |
| 4    | Renoir   | 4    | 5    | Lex Deux Soeurs   | NE    | 64    |
+------+----------+------+------+-------------------+-------+-------+

INNER JOIN 生成的结果结合了一个表与另一个表中的值。上述查询没有指定行的结合条件，因此连接会生成所有行的组合(即笛卡尔积)。
这个结果说明通常这样的连接没有意义，因为会产生大量无意义的输出。很显然，你建这些表不是为了将每个艺术家与每幅画相匹配。

如果想解决有意义的问题，那么需要通过适当的连接条件来生成相关的匹配。
例如，如果想生成画作与艺术家名的列表，那么需要一个简单的 WHERE 子句通过艺术家 ID 将两个表结合在一起，艺术家 ID 是两个表的共同字段，可以将它们连接在一起:

MySQL root@(none):test_db> SELECT * FROM artist INNER JOIN painting 
WHERE 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    | Mona Lisa         | MI    | 87    |
| 3    | Van Gogh | 3    | 3    | Starry Night      | KY    | 48    |
| 3    | Van Gogh | 3    | 4    | The Potato Eaters | KY    | 67    |
| 4    | Renoir   | 4    | 5    | Lex Deux Soeurs   | NE    | 64    |
+------+----------+------+------+-------------------+-------+-------+

WHERE 子句中的列名通过表名明确制定了进行比较的 a_id 值。结果显示了每幅画作的作者，以及你的收藏中每位艺术家的作品。

连接与索引:
连接很容易导致 MySQL 处理大量行的组合，因此最好在需要比较的列上加索引。否则，随着表大小的增加，性能会迅速下降。
对于 artist 和 painting 表，你可以通过比较 a_id 列来连接两个表。如果回顾这些表的 CREATE TABLE 语句，你会发现每个表中的 a_id 都加了索引

另外一种书写上述连接语句的方法是: 使用 ON 子句表示匹配条件

1
2
3

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

在两个表中的列名相同的特殊情况下，可以使用带有 USING 子句的 INNER JOIN。该语句不需要指定表名，而且用于连接的列只需指定一次:

SELECT * FROM artist INNER JOIN painting 
USING (a_id) 
ORDER BY a_id;
+------+----------+------+-------------------+-------+-------+
| a_id | name     | p_id | title             | state | price |
+------+----------+------+-------------------+-------+-------+
| 1    | Da Vinci | 1    | The Last Supper   | IN    | 34    |
| 1    | Da Vinci | 2    | Mona Lisa         | MI    | 87    |
| 3    | Van Gogh | 3    | Starry Night      | KY    | 48    |
| 3    | Van Gogh | 4    | The Potato Eaters | KY    | 67    |
| 4    | Renoir   | 5    | Lex Deux Soeurs   | NE    | 64    |
+------+----------+------+-------------------+-------+-------+

对于 SELECT * 查询，USING 形式的查询生成的结果与 ON 不同: USING 只返回一个连接列，因此 a_id 只出现一次，而不是两次。

ON, USING 或 WHERE 中的任何一个都可以包含比较，那么你怎么知道应该在每个子句中指定哪些连接条件呢？
一般来说，ON 或 USING 通常用来指定表的连接，而 WHERE 子句则可以用来限制选择的连接行。
例如，你需要利用 a_id 列连接表，但是只想选择来自肯塔基州的画作，那么可以使用 ON(或 USING) 子句连接两个表中的行，并使用 WHERE 子句指定 state 列

MySQL root@(none):test_db> 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 root@(none):test_db> 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 root@(none):test_db> 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
WHERE painting.state = 'KY';
+----------+-------------------+----------+-------+
| name     | title             | name     | price |
+----------+-------------------+----------+-------+
| Van Gogh | Starry Night      | Kentucky |  48   |
| Van Gogh | The Potato Eaters | Kentucky |  67   |
+----------+-------------------+-------+----------+

三个表的连接的另一个常见用途是枚举多对多的关系

在连接中加入适当的条件，就可以回答非常具体的问题:

哪些画是梵高画的？你可以通过 a_id 值连接两个表，然后通过 WHERE 子句指定输出包含梵高名字的行，并从这些行中选择画作的标题:

MySQL root@(none):test_db> 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 |
+-------------------+

谁画了蒙娜丽莎？同样，使用 a_id 来连接两个表，但这次通过 WHERE 子句指定输出行需要包含该画作的标题，并从这些行中选择艺术家名:

MySQL root@(none):test_db> SELECT artist.name FROM artist INNER JOIN painting ON artist.a_id = painting.a_id WHERE painting.title = 'Mona Lisa';
+----------+
| name     |
+----------+
| Da Vinci |
+----------+

你在肯塔基州或印第安纳州买过哪些艺术家的画作？这与前面的语句类似，但是需要指定 painting 表的州为 KY 或 IN:

MySQL root@(none):test_db> 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 的话，梵高的名字会出现两次，因为你在肯塔基州买了两幅梵高的画作

表连接与合计函数一起使用可以生成汇总信息。下列语句显示了每位艺术家有多少幅画作:

MySQL root@(none):test_db> SELECT artist.name, COUNT(*) AS 'number of paintings' FROM artist INNER JOIN painting ON artist.a_id = painting.a_id GROUP BY artist.name;
+----------+---------------------+
| name     | number of paintings |
+----------+---------------------+
| Da Vinci | 2                   |
| Van Gogh | 2                   |
| Renoir   | 1                   |
+----------+---------------------+

下列更为复杂的语句利用合计函数来显示你在每位艺术家的画作上花了多少钱: 合计金额与平均金额:

MySQL root@(none):test_db> SELECT artist.name, 
COUNT(*) AS 'number of paintings', 
SUM(painting.price) AS 'total price', 
AVG(painting.price) AS 'average price' 
FROM artist INNER JOIN painting ON artist.a_id = painting.a_id 
GROUP BY artist.name;
+----------+---------------------+-------------+---------------+
| name     | number of paintings | total price | average price |
+----------+---------------------+-------------+---------------+
| Da Vinci | 2                   | 121         | 60.5000       |
| Van Gogh | 2                   | 115         | 57.5000       |
| Renoir   | 1                   | 64          | 64.0000       |
+----------+---------------------+-------------+---------------+

对于 artist 表中的艺术家，只有你实际上买过他们的画作，他们的名字才会显示在上述汇总语句的输出中(例如，虽然 artist 表中有莫奈，但由于你还没有他的画作，因此没有显示在汇总信息中)。
为了汇总显示所有的艺术家，包括你没有买过他们的画作的艺术家，那么必须使用另一种不同类型的连接，即外连接

用 INNER JOIN 编写的连接是内连接。它们生成的结果只显示两个表都有的值
外连接不仅可以生成两个表都有的值，还可以显示只有一个表中存在的值(另一个表中没有这些值)

为了使读者更清楚地理解语句的含义，最好在指定列名时加上表名，即使 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;

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 可以省略

不同数据库的表之间的连接:

如果想在不同数据库的表之间建立连接，那么需要完整地指明表名和列名，MySQL 才能知道你所指的内容

假设 artist 属于 db1 数据库，而 painting 属于 db2 数据库。如果想明确指定表名，那么需要在前面加上数据库。完整的连接语句应该写成下面这样:

1
2
3

SELECT db1.artist.name, db2.painting.title
FROM db1.artist INNER JOIN db2.painting
on db1.artist.a_id = db2.painting.a_id;

表别名可以大大简化该语句的书写:

1
2
3

SELECT a.name, p.title
FROM db1.artist AS a INNER JOIN db2.painting AS p
on a.a_id = p.a_id;

如果没有默认的数据库，或者既不是 db1 也不是 db2，那么必须完整地指出两个表名。如果默认数据库是 db1 或 db2，那么可以省略相应的数据库名。
如果默认数据库是 db1，则可以省略数据库名 db1。相反，则不需要指定数据库名 db2

查找表之间不匹配的行

问题:
你希望查找只有一个表中有的行。或者你希望根据两个表的连接生成一个列表，并且你希望这个列表可以包含第一个表中的每一行，即使是哪些第二个表中没有的行。

解决方案:
使用外连接(LEFT JOIN 或 RIGHT JOIN) 或 NOT IN 子查询

讨论:
有些问题需要确定哪些行没有匹配(或者换句话说，哪些是另一个表缺失的行)。
例如，你可能想知道还有哪些 artist 表中的艺术家你没有他们的作品。在其他情况下也会出现类似的问题。

你有一个潜在客户的列表，还有一个已下订单客户的列表。如果你想将销售工作重点放在尚未成为真正客户的人身上，那么需要生成一组只出现在了第一个列表中却没有出现在第二个列表中的人员名单
你有一个棒球运动员名单，还有一个曾经完成本垒打的运动员名单。如果你想知道出现在第一个列表中的哪些运动员还没有击中本垒打，那么就需要生成出现在第一个列表却没有在第二个列表中的运动员名单

这类问题需要使用外连接。与内连接一样，外连接会在两个表之间查找匹配。
但与内连接不同的是，外连接还可以找出只属于其中一个表的行。外连接有两种类型: LEFT JOIN 和 RIGHT JOIN

为了说明外连接的用法，我们来考虑这样一个问题: 哪些艺术家出现在了 artist 表中，却没有在 painting 表中。
目前，这两个表都很小，所以很容易一眼看出你没有莫奈的画作(painting 表中没有 a_id 等于 2 的行)

但随着你收集的画作越来越多，这两个表的数据也会增大，到时就无法简单地通过人眼观察来回答这些问题了。那么可以用 SQL 来解答吗？
当然可以，虽然第一次遇到这种问题的时候，我们往往会尝试用下列语句来解决问题: 利用不等式条件来查找两个表之间不匹配的行:

MySQL root@(none):test_db> SELECT * FROM artist INNER 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 | 3    | 3    | Starry Night      | KY    | 48    |
| 1    | Da Vinci | 3    | 4    | The Potato Eaters | KY    | 67    |
| 1    | Da Vinci | 4    | 5    | Lex Deux Soeurs   | NE    | 64    |
| 2    | Monet    | 1    | 1    | The Last Supper   | IN    | 34    |
| 2    | Monet    | 1    | 2    | Mona Lisa         | MI    | 87    |
| 2    | Monet    | 3    | 3    | Starry Night      | KY    | 48    |
| 2    | Monet    | 3    | 4    | The Potato Eaters | KY    | 67    |
| 2    | Monet    | 4    | 5    | Lex Deux Soeurs   | NE    | 64    |
| 3    | Van Gogh | 1    | 1    | The Last Supper   | IN    | 34    |
| 3    | Van Gogh | 1    | 2    | Mona Lisa         | MI    | 87    |
| 3    | Van Gogh | 4    | 5    | Lex Deux Soeurs   | NE    | 64    |
| 4    | Renoir   | 1    | 1    | The Last Supper   | IN    | 34    |
| 4    | Renoir   | 1    | 2    | Mona Lisa         | MI    | 87    |
| 4    | Renoir   | 3    | 3    | Starry Night      | KY    | 48    |
| 4    | Renoir   | 3    | 4    | The Potato Eaters | KY    | 67    |
+------+----------+------+------+-------------------+-------+-------+

上述查询语句看似很合理，但结果显示不正确。例如一个明显的错误是，结果中的每幅画作都由多位不同的艺术家创作。
问题在于上述语句列出了两个表中艺术家 ID 不一致的所有值的组合。而你真正需要的是出现在了 artist 表中，却没有出现在 painting 表中的所有值的列表，但是内连接只能找出两个表中同时存在的值。它无法告诉你其中一个表中缺失的值

当需要从一个表中找到没有匹配(或另一个表中不存在)的值时，你应该养成思考的习惯: "哦，这是一个 LEFT JOIN 的问题"。
LEFT JOIN 是一种外连接: 它与内连接类似，它拿第一个(左)表中的行去匹配第二个(右)表中的行。
此外，如果左表的行与右表不匹配，那么 LEFT JOIN 仍会生成一行，该行中所有来自右表的列都为 NULL。
这意味着你可以通过查找 NULL 来查找右表中缺少的行。为了便于理解，下面我们通过几个步骤来解说其中的工作原理。首先，我们来看看内连接显示的匹配行:

MySQL root@(none):test_db> SELECT * FROm artist INNER 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    | Mona Lisa         | MI    | 87    |
| 3    | Van Gogh | 3    | 3    | Starry Night      | KY    | 48    |
| 3    | Van Gogh | 3    | 4    | The Potato Eaters | KY    | 67    |
| 4    | Renoir   | 4    | 5    | Lex Deux Soeurs   | NE    | 64    |
+------+----------+------+------+-------------------+-------+-------+

在以上输出中，第一个 a_id 列来自 artist 表，第二个来自 painting 表

现在用 LEFT 代替 INNER，让我们来看看外连接获得的结果:

MySQL root@(none):test_db> 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      | 2      | Mona Lisa         | MI     | 87     |
| 1    | Da Vinci | 1      | 1      | The Last Supper   | IN     | 34     |
| 2    | Monet    | <null> | <null> | <null>            | <null> | <null> |
| 3    | Van Gogh | 3      | 4      | The Potato Eaters | KY     | 67     |
| 3    | Van Gogh | 3      | 3      | Starry Night      | KY     | 48     |
| 4    | Renoir   | 4      | 5      | Lex Deux Soeurs   | NE     | 64     |
+------+----------+--------+--------+-------------------+--------+--------+

与内连接相比，对于每个 painting 表中没有的 artist 行，外连接都生成了一个额外的行，并将所有 painting 列都设为 NULL

接下来，如果只想输出 painting 表中没有的 artist 行，那么需要通过一个 WHERE 子句来指定 painting 列为 NULL 的行。
如此一来就可以滤掉内连接生成的行，只留下外连接生成的行:

MySQL root@(none):test_db> SELECT * FROM artist LEFT JOIN painting 
ON artist.a_id = painting.a_id 
WHERE painting.a_id IS NULL;

+------+-------+--------+--------+--------+--------+--------+
| a_id | name  | a_id   | p_id   | title  | state  | price  |
+------+-------+--------+--------+--------+--------+--------+
| 2    | Monet | <null> | <null> | <null> | <null> | <null> |
+------+-------+--------+--------+--------+--------+--------+

最后，如果只想显示 painting 表中没有的 artist 表的值，那么可以在输出中只指定 artist 表中的列。结果是 LEFT JOIN 只显示了左表的行，且这些行包含了右表中没有的 a_id 值:

MySQL root@(none):test_db> SELECT artist.* FROM artist LEFT JOIN painting 
ON artist.a_id = painting.a_id 
WHERE painting.a_id IS NULL;
+------+-------+
| a_id | name  |
+------+-------+
| 2    | Monet |
+------+-------+

一个类似的操作时报告每个左表的值，并指出右表是否有这个值。如果想执行该操作，那么需要执行 LEFT JOIN，计算右表中每个左表的值出现的次数。如果计数为零，则表示右表中没有该值。
以下语句列出了 artist 表中的每位艺术家，并显示了你是否有该艺术家的画作:

MySQL root@(none):test_db> 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             |
| Renoir   | yes            |
| Van Gogh | yes            |
+----------+----------------+

RIGHT JOIN 也是一个外连接，它与 LEFT JOIN 类似，只不过左右表的角色会被互换。
从语义上讲，RIGHT JOIN 强制在匹配过程为右表中的每一行都生成一行，即使左表中没有相应的行。
从语法上讲，tbl1 LEFT JOIN tbl2 相当于 tbl2 RIGHT JOIN tbl1。

另一种找出一个表有另一个表中却没有的值的方法是使用 NOT IN 子查询。
以下示例查找 painting 表中没有出现的艺术家，你可以比较这个语句与上述的 LEFT JOIN:

MySQL root@(none):test_db> SELECT * FROM artist WHERE a_id NOT IN (SELECT a_id FROM painting);
+------+-------+
| a_id | name  |
+------+-------+
| 2    | Monet |
+------+-------+

书写 LEFT JOIN 和 RIGHT JOIN 查询的其他方法:

与 INNER JOIN 相同，如果外连接的两个表中都包含了需要匹配的列名，并且你用 = 运算符进行比较，那么可以使用 USING 子句代替 ON。
例如，以下两个语句的效果是一样的:

1 2	`SELECT * FROM t1 LEFT JOIN t2 ON t1.n = t2.n; SELECT * FROM t1 LEFT JOIN t2 USING (n);`

如果你想让两个表中的每一列都进行比较，那么可以使用 NATURAL LEFT JOIN 或 NATURAL RIGHT JOIN，并且可以省略 ON 或 USING 子句:

1 2	`SELECT * FROM t1 NATURAL LEFT JOIN t2; SELECT * FROM t1 NATURAL RIGHT JOIN t2;`

在查找另一个表中没有的值，或显示是否两个表都包含每个值的时候，LEFT JOIN 非常有用。
你可以用 LEFT JOIN 生成包含所有列的汇总列表，甚至可以包括那些没什么好汇总的列。对于主从关系来说，这种做法很常见。
例如，LEFT JOIN 可以生成 "每个客户的总销售额" 报告，列出所有客户甚至是那些在汇总期间什么都没有买的客户。

当你收到两个应该有关联的数据文件，并且想看看它们之间是否真的有关联时，可以用 LEFT JOIN 进行一致性检查(也就是说，你需要检查其关系的完整性)。
你可以将两个文件都导入到 MySQL 的表中，然后运行几个 LEFT JOIN 语句来确定是否有的行只有其中一个表才有，而另一个表中不包含这些行。

识别并删除不匹配或独立存在的行

问题:
你有连个相互关联的数据集，但可能关联得不完美。你想知道是否有的数据集中存在 "孤立" 的记录(与另一个数据集中的任何记录都不匹配)，如果是这样的话，你希望可以将其删除

解决方案:
如果想识别每个表中不匹配的值，那么可以使用 LEFT JOIN 或 NOT IN 子查询。如果想删除不匹配的值，那么可以使用带有 NOT IN 子查询的 DELETE

讨论:
内连接可以识别匹配，外连接可以识别不匹配。当你拥有互相关联但可能不完美的数据集时，就可以利用外连接的这个特点。
例如，当你必须验证从外部接收的两个数据文件的完整性时，可能会发现不匹配

如果两个相关的表拥有不匹配的行，那么可以利用 SQL 语句分析和修改这些行。具体来说，恢复它们之间的关联关系就是识别独立存在的行，然后将其删除:

如果想识别独立存在的行，那么可以使用 LEFT JOIN，因为这个语句可以 "查找不匹配的行"，或者你也可以使用 NOT IN 子查询
如果想删除不匹配的行，那么可以使用带有 NOT IN 子查询的 DELETE

了解独立存在的数据非常有用，因为你可以提醒给你提供数据的人，他们的数据收集方法可能存在有缺陷，必须纠正。
例如，对于销售数据而言，如果没有某个区域，那么可能意味着该区域的经理没有报告，并忽略了这种遗漏。