完全外部加入
MySQL 不支援 FULL OUTER JOIN
,但有一些方法可以模擬一個。
設定資料
placeholderCopy-- ----------------------------
-- 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
,第二個查詢的結果集將附加到第一個查詢結果集。
placeholderCopySELECT `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)