完全外部加入

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)