MySQL join查询的区别

MySQL版本：8.0.27。

MySQL支持三种类型的join

join/inner join（内连接）：
只获取匹配的记录；

left join/left outer join（左连接或左外连接）：
获取左表所有记录、右表匹配记录，右表没有匹配记录显示null；

right join/right outer join（右连接或右外连接）：
获取右表所有记录、左表匹配记录，左表没有匹配记录显示null；

实例

测试数据

（脚本我放在最后）

inner join

SELECT * FROM `table_a` t1 JOIN `table_b` t2 ON t2.id = t1.b_id;
-- 查询结果等同于
SELECT * FROM `table_a` t1 INNER JOIN `table_b` t2 ON t2.id = t1.b_id;

值得一提的是，它等同于我们常见的另一种查询方法：

SELECT * FROM `table_a` t1, `table_b` t2 WHERE t2.id = t1.b_id;

left join

SELECT * FROM `table_a` t1 LEFT JOIN `table_b` t2 ON t2.id = t1.b_id;
-- 查询结果等同于
SELECT * FROM `table_a` t1 LEFT OUTER JOIN `table_b` t2 ON t2.id = t1.b_id;

right join

SELECT * FROM `table_a` t1 RIGHT JOIN `table_b` t2 ON t2.id = t1.b_id;
-- 查询结果等同于
SELECT * FROM `table_a` t1 RIGHT OUTER JOIN `table_b` t2 ON t2.id = t1.b_id;

基于这三种join可以查询各种集合的结果

需要注意的是：MySQL中不支持full outer join。我们可以通过union和union all来获取它们的合集，
区别是union会把查询结果中重复的数据去重，union all会返回所有记录，效率高于union。

SQL脚本

-- ----------------------------
-- Table structure for table_a
-- ----------------------------
CREATE TABLE `table_a`  (
  `a_id` int NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `b_id` int NULL DEFAULT NULL COMMENT 'table_b的ID',
  PRIMARY KEY (`a_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = 'table_a' ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of table_a
-- ----------------------------
INSERT INTO `table_a` VALUES (1, 5);
INSERT INTO `table_a` VALUES (2, 6);
INSERT INTO `table_a` VALUES (3, 7);
INSERT INTO `table_a` VALUES (4, 9);

SET FOREIGN_KEY_CHECKS = 1;

-- ----------------------------
-- Table structure for table_b
-- ----------------------------
CREATE TABLE `table_b`  (
  `id` int NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `b_desc` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '描述',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = 'table_b' ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of table_b
-- ----------------------------
INSERT INTO `table_b` VALUES (5, 'b1');
INSERT INTO `table_b` VALUES (6, 'b2');
INSERT INTO `table_b` VALUES (7, 'b3');
INSERT INTO `table_b` VALUES (8, 'b4');

SET FOREIGN_KEY_CHECKS = 1;