博客

最近面试遇到了一道面试题，如何实现 FULL OUTER JOIN。

什么是FULL JOIN

首先我们要知道 FULL OUTER JOIN 和 RIGHT JOIN / LEFT JOIN 的区别。

可以参考我的文章：

1.数据库__内连接、左外连接、右外连接、交叉连接区别

https://blog.csdn.net/u010003835/article/details/49252893

2.数据库__SQL的四种连接-左外连接、右外连接、内连接、全连接

https://blog.csdn.net/u010003835/article/details/49252863

下面的一个集合关系图，更能清楚的表述几者之间的区别

如何实现FULL JOIN

我们要知道MySQL 是不支持FULL JOIN的。

实现FULL JOIN 主要有以下两种思路

1.LEFT JOIN + RIGHT JOIN + UNION (去重)

2.LEFT JOIN + UNION ALL (不去重)

下面我们给出数据样例

数据表 :  test_a

DROP TABLE IF EXISTS `test_a`;
CREATE TABLE `test_a` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=gbk;

-- ----------------------------
-- Records of test_a
-- ----------------------------
INSERT INTO `test_a` VALUES ('1', 'ss');
INSERT INTO `test_a` VALUES ('2', 'pp');

数据表：test_b

DROP TABLE IF EXISTS `test_b`;
CREATE TABLE `test_b` (
  `id` int(11) DEFAULT NULL,
  `age` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk;

-- ----------------------------
-- Records of test_b
-- ----------------------------
INSERT INTO `test_b` VALUES ('3', '12');
INSERT INTO `test_b` VALUES ('4', '2');
INSERT INTO `test_b` VALUES ('2', '23');

1.LEFT JOIN + RIGHT JOIN + UNION (去重)

c参考  ：

https://blog.csdn.net/weiliyu1995/article/details/80914810

相当于

Step1 LEFT JOIN + RIGHT JOIN  两部分集合数据，有中间红色重复的部分

Step2 利用UNION ALL 将中间重复的红色部分的数据去掉

EXPLAIN
SELECT
	a.id, a.`name`, b.age
FROM test_a a
LEFT JOIN test_b b 
	ON a.id = b.id
UNION
SELECT 
	b.id, a.`name`, b.age
FROM test_b AS b
LEFT JOIN test_a AS a
	ON b.id = a.id
;

SQL的执行结果

SQL的执行计划

分析

UNION 增加了一步的去重逻辑,  但是其实我们是知道那部分的数据是重复的，我们可以使用 WHERE 去做 Filter, 并结合


UNION ALL


去完成数据集的拼装。

2.LEFT JOIN + UNION ALL (不去重)

其实可以将 下面的3部分区域的数据拼装起来，并不用去重

1.a LEFT JOIN b ON a.id = b.id  WHERE b.other_col IS NULL  是左半部分

2.a JOIN b ON a.id = b.id 是中间的部分

3.b LEFT JOIN a ON b.id = a.id WHER a.other_col IS NULL 是右半部分

SQL

SELECT
    a.id, a.`name`, b.age
FROM test_a a
LEFT JOIN test_b b 
    ON a.id = b.id
WHERE b.age IS NULL
UNION ALL
SELECT 
    b.id, a.`name`, b.age
FROM test_b AS b
LEFT JOIN test_a AS a
    ON b.id = a.id
WHERE a.`name` IS NULL
UNION ALL
SELECT 
    a.id, a.`name`, b.age
FROM test_b AS b
JOIN test_a AS a
    ON b.id = a.id
;

结果

执行计划

这样理论上会多出一部分数据集，但是去掉了中间的去重逻辑