完全外部加入

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

设置数据

-- ----------------------------
-- Table structure for `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;

-- ----------------------------
-- Records of owners
-- ----------------------------
INSERT INTO `owners` VALUES ('1', 'Ben');
INSERT INTO `owners` VALUES ('2', 'Jim');
INSERT INTO `owners` VALUES ('3', 'Harry');
INSERT INTO `owners` VALUES ('6', 'John');
INSERT INTO `owners` VALUES ('9', 'Ellie');
-- ----------------------------
-- Table structure for `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;
-- ----------------------------
-- Records of tools
-- ----------------------------
INSERT INTO `tools` VALUES ('1', 'Hammer', '9');
INSERT INTO `tools` VALUES ('2', 'Pliers', '1');
INSERT INTO `tools` VALUES ('3', 'Knife', '1');
INSERT INTO `tools` VALUES ('4', 'Chisel', '2');
INSERT INTO `tools` VALUES ('5', 'Hacksaw', '1');
INSERT INTO `tools` VALUES ('6', 'Level', null);
INSERT INTO `tools` VALUES ('7', 'Wrench', null);
INSERT INTO `tools` VALUES ('8', 'Tape Measure', '9');
INSERT INTO `tools` VALUES ('9', 'Screwdriver', null);
INSERT INTO `tools` VALUES ('10', 'Clamp', null);

我们想看到什么？

我们希望获得一个列表，其中我们看到谁拥有哪些工具，以及哪些工具可能没有所有者。

查询

为此，我们可以使用 UNION 组合两个查询。在第一个查询中，我们使用 LEFT JOIN 加入了所有者的工具。这会将我们所有的所有者添加到我们的结果集中，如果他们实际拥有工具并不重要。

在第二个查询中，我们使用 RIGHT JOIN 将工具加入到所有者中。通过这种方式，我们设法获取结果集中的所有工具，如果它们不属于任何人，则其所有者列将只包含 NULL。通过添加由 owners.owner_id IS NULL 过滤的 WHERE 子句，我们将结果定义为第一个查询尚未返回的数据集，因为我们只查找右连接表中的数据。

由于我们使用 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)