Example
MySQL does not support the FULL OUTER JOIN, but there are ways to emulate one.
Setting up the data
-- ----------------------------
-- 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);
What do we want to see?
We want to get a list, in which we see who owns which tools, and which tools might not have an owner.
The queries
To accomplish this, we can combine two queries by using UNION.
In this first query we are joining the tools on the owners by using a LEFT JOIN. This will add all of our owners to our resultset, doesn't matter if they actually own tools.
In the second query we are using a RIGHT JOIN to join the tools onto the owners. This way we manage to get all the tools in our resultset, if they are owned by no one their owner column will simply contain NULL. By adding a WHERE-clause which is filtering by owners.owner_id IS NULL we are defining the result as those datasets, which have not already been returned by the first query, as we are only looking for the data in the right joined table.
Since we are using UNION ALL the resultset of the second query will be attached to the first queries resultset.
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)
