第 16.1 节：连接的可视化

如果你是一个视觉型的人，下面的维恩图可能有助于理解 MySQL 中存在的不同类型的 JOIN。

[图片占位符：维恩图展示了 INNER JOIN、LEFT JOIN、RIGHT JOIN、OUTER JOIN 等]

第 16.2 节：带子查询的连接（派生表）

SELECT x, ...
FROM (SELECT y, ... FROM ...) AS a
JOIN tbl ON tbl.x = a.y
WHERE ...;

这将把子查询的结果计算到一个临时表中，然后将其与 tbl 进行连接。

在 MySQL 5.6 之前，临时表上不能有索引。因此，以下写法可能非常低效：

SELECT ...
FROM (SELECT y, ... FROM ...) AS a
JOIN (SELECT x, ... FROM ...) AS b ON b.x = a.y
WHERE ...;

从 5.6 开始，优化器会找出最佳索引并即时创建它（这有一定的开销，所以仍然不是“完美”的）。

另一个常见的范式是使用子查询来初始化某些内容：

SELECT
    @n := @n + 1,
    ...
FROM (SELECT @n := 0) AS initialize
CROSS JOIN the_real_table
ORDER BY ...;

（注意：从技术上讲，这是一个 CROSS JOIN（笛卡尔积），因为没有 ON 子句。但由于子查询只返回一行，需要与 the_real_table 的 n 行匹配，所以效率很高。）

第 16.3 节：完全外连接（FULL OUTER JOIN）

MySQL 不支持 FULL OUTER JOIN，但有一些方法可以模拟它。

设置数据

表结构 owners：

DROP TABLE IF EXISTS `owners`;
CREATE TABLE `owners` (
    `owner_id` int(11) NOT NULL AUTO_INCREMENT,
    `owner` varchar(30) DEFAULT NULL,
    PRIMARY KEY (`owner_id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1;

INSERT INTO `owners` VALUES
(1, 'Ben'),
(2, 'Jim'),
(3, 'Harry'),
(6, 'John'),
(9, 'Ellie');

表结构 tools：

DROP TABLE IF EXISTS `tools`;
CREATE TABLE `tools` (
    `tool_id` int(11) NOT NULL AUTO_INCREMENT,
    `tool` varchar(30) DEFAULT NULL,
    `owner_id` int(11) DEFAULT NULL,
    PRIMARY KEY (`tool_id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;

INSERT INTO `tools` VALUES
(1, 'Hammer', 9),
(2, 'Pliers', 1),
(3, 'Knife', 1),
(4, 'Chisel', 2),
(5, 'Hacksaw', 1),
(6, 'Level', NULL),
(7, 'Wrench', NULL),
(8, 'Tape Measure', 9),
(9, 'Screwdriver', NULL),
(10, 'Clamp', NULL);

我们想看到什么？

我们想要得到一个列表，显示谁拥有哪些工具，以及哪些工具可能没有所有者。

查询方法

我们可以使用 UNION 将两个查询的结果组合起来。在第一个查询中，我们使用 LEFT JOIN 将工具连接到所有者。这将把所有所有者添加到结果集中，无论他们是否实际拥有工具。

在第二个查询中，我们使用 RIGHT JOIN 将工具连接到所有者。通过这种方式，我们可以将所有工具放入结果集中，如果它们没有所有者，则所有者列将简单地包含 NULL。通过添加 WHERE owners.owner_id IS NULL 子句，我们将结果限定为那些尚未被第一个查询返回的数据集，因为我们只查找右连接表中的数据。

由于我们使用 UNION ALL，第二个查询的结果集将附加到第一个查询的结果之后。

SELECT owners.owner, tools.tool
FROM owners
LEFT JOIN tools ON owners.owner_id = tools.owner_id

UNION ALL

SELECT owners.owner, tools.tool
FROM owners
RIGHT JOIN tools ON owners.owner_id = tools.owner_id
WHERE owners.owner_id IS NULL;

结果：

+-------+--------------+
| owner | tool         |
+-------+--------------+
| Ben   | Pliers       |
| Ben   | Knife        |
| Ben   | Hacksaw      |
| Jim   | Chisel       |
| Harry | NULL         |
| John  | NULL         |
| Ellie | Hammer       |
| Ellie | Tape Measure |
| NULL  | Level        |
| NULL  | Wrench       |
| NULL  | Screwdriver  |
| NULL  | Clamp        |
+-------+--------------+
12 rows in set (0.00 sec)

第 16.4 节：检索带有订单的客户——同一主题的变体

这将获取所有客户的所有订单：

SELECT c.CustomerName, o.OrderID
FROM Customers AS c
INNER JOIN Orders AS o ON c.CustomerID = o.CustomerID
ORDER BY c.CustomerName, o.OrderID;

这将统计每个客户的订单数量：

SELECT c.CustomerName, COUNT(*) AS 'Order Count'
FROM Customers AS c
INNER JOIN Orders AS o ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerID
ORDER BY c.CustomerName;

另一种统计方法（可能更快）：

SELECT c.CustomerName,
    (SELECT COUNT(*) FROM Orders WHERE CustomerID = c.CustomerID) AS 'Order Count'
FROM Customers AS c
ORDER BY c.CustomerName;

只列出有订单的客户：

SELECT c.CustomerName
FROM Customers AS c
WHERE EXISTS (SELECT * FROM Orders WHERE CustomerID = c.CustomerID)
ORDER BY c.CustomerName;

第 16.5 节：连接示例

创建表的查询：

CREATE TABLE `user` (
    `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(30) NOT NULL,
    `course` smallint(5) unsigned DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE TABLE `course` (
    `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(50) NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB;

由于我们使用的是 InnoDB 表，并且知道 user.course 和 course.id 是相关的，我们可以指定一个外键关系：

ALTER TABLE `user`
ADD CONSTRAINT `FK_course`
FOREIGN KEY (`course`) REFERENCES `course` (`id`) ON UPDATE CASCADE;

连接查询（内连接）：

SELECT user.name, course.name
FROM `user`
INNER JOIN `course` ON user.course = course.id;