龙空技术网

说一说MySQL的7种join操作

面试题解析 535

前言:

而今姐妹们对“mysql中的joinon”大致比较珍视,朋友们都想要分析一些“mysql中的joinon”的相关知识。那么小编同时在网络上搜集了一些关于“mysql中的joinon””的相关内容,希望我们能喜欢,各位老铁们一起来了解一下吧!

【死记硬背】

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 nullunionselect b.id,b.name from user a right join user2 b on a.id=b.id where a.id is null;

【温馨提示】

点赞+收藏文章,关注我并私信回复【面试题解析】,即可100%免费领取楼主的所有面试题资料!

标签: #mysql中的joinon