mysql join的用法? 说一说MySQL的7种join操作

100人浏览 2025-01-13 10:16:25

【死记硬背】

MySQL的7种join操作分别是：内连接(inner join)、左连接(left join)、右连接(right join)、外连接(outer join)、左内连接(left join excluding inner join)、右内连接(right join excluding inner join)、外内连接(outer join excluding inner join)。

下面进行实际操作来看下这7种join的操作结果。

1 SQL准备

CREATE TABLE `user`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
INSERT INTO `user` VALUES (1, '张三');
INSERT INTO `user` VALUES (2, '李四');
INSERT INTO `user` VALUES (3, '王二');

CREATE TABLE `user2`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
INSERT INTO `user2` VALUES (1, '张三');
INSERT INTO `user2` VALUES (2, '李四');
INSERT INTO `user2` VALUES (4, '麻子');

2 inner join(内连接)

select a.id,a.name from user a inner join user2 b on a.id=b.id;

3 left join(左连接)

select a.id,a.name from user a left join user2 b on a.id=b.id;

4 right join(右连接)

select b.id,b.name from user a right join user2 b on a.id=b.id;

5 outer join(外连接)

select a.id,a.name from user a left join user2 b on a.id=b.id union select b.id,b.name from user a right join user2 b on a.id=b.id;

6 left join excluding inner join(左内连接)

select a.id,a.name from user a left join user2 b on a.id=b.id where b.id is null;

7 right join excluding inner join(右内连接)

select b.id,b.name from user a right join user2 b on a.id=b.id where a.id is null;

8 outer join excluding inner join(外内连接)

select a.id,a.name from user a left join user2 b on a.id=b.id where b.id is null
union
select b.id,b.name from user a right join user2 b on a.id=b.id where a.id is null;