其他
SQL中 inner join、left join、right join、full join 到底怎么选？详解来了
雷猴！我是mbb！
作为一名CURD工程师，联表查询应该就算是一项相对复杂的工作了吧，如果表结构复杂一点，左一连，右一连，内一连再加上外一连，很可能就被绕晕的，最终得到的数据集就不是自己理想中的结果；
能被绕晕呢，无非就两种情况！要么是业务不熟悉，对数据的理解不够深入；要么就是对各种联表查询的细微的差别了解的不够深入；
首先来看一下数据库表链接的几种方式
inner join 内连接 left join 左连接 right join 右连接 full join 全连接（mysql没有，oricle有） 以及四种方式衍生出的其他数据集
四种方式本质都是做表之间的关联，仅仅只是存在了些许细微的差别，最终带来两表之间不同的结果集；
下面就通过两张示例表加上示意图，以最简单的方式去理解一下他们各自的差异；
1准备
一张用户表，一张城市表，用户表中有个城市id（city_id）关联着城市表的id
user_info表
用户表
DROP TABLE IF EXISTS `user_info`;
CREATE TABLE `user_info` (
`u_id` int(11) NOT NULL,
`user_name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`age` int(11) NULL DEFAULT NULL,
`city_id` int(11) NULL DEFAULT NULL,
PRIMARY KEY (`u_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of user_info
-- ----------------------------
INSERT INTO `user_info` VALUES (1, '张三', 10, 1);
INSERT INTO `user_info` VALUES (2, '李四', 20, 2);
INSERT INTO `user_info` VALUES (3, '王五', 25, 3);
INSERT INTO `user_info` VALUES (4, '赵六', 80, 20);city_info
城市表
DROP TABLE IF EXISTS `city_info`;
CREATE TABLE `city_info` (
`id` int(11) NOT NULL,
`city` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of city_info
-- ----------------------------
INSERT INTO `city_info` VALUES (1, '北京');
INSERT INTO `city_info` VALUES (2, '上海');
INSERT INTO `city_info` VALUES (3, '杭州');
INSERT INTO `city_info` VALUES (4, '深圳');
2联表测试
内连接 inner join
内连接查询两张表直接的交集部分，只保留两张表都有的字段
# INNER JOIN
SELECT * FROM
user_info AS ur
INNER JOIN
city_info AS ci
ON ur.city_id = ci.id;
左连接 left join
返回左边表中的所有行，即使右边表中没有行与之匹配，左边的行依然显示，右边没有匹配尚的显示为null
# LEFT JOIN
SELECT * FROM user_info AS ur LEFT JOIN city_info AS ci ON ur.city_id = ci.id;
右连接 right join
和左连接正好相反，返回右边表的所有行，即使左边没有行与之匹配，未匹配上的显示null
#RIGHT JOIN
SELECT * FROM
user_info AS ur
RIGHT JOIN
city_info AS ci
ON ur.city_id = ci.id;
全连接full join
通俗理解，就是取两张表的并集；mysql中不支持该语法，但是可以采用UNION方式完成
# FULL JOIN
SELECT * FROM user_info AS ur LEFT JOIN city_info AS ci ON ur.city_id = ci.id
UNION
SELECT * FROM user_info AS ur RIGHT JOIN city_info AS ci ON ur.city_id = ci.id;
左表独有
查询左边表独有的数据
# 左表独有
SELECT * FROM
user_info AS ur
LEFT JOIN
city_info AS ci
ON ur.city_id = ci.id
WHERE ci.id IS NULL;
右表独有
查询右边表独有的数据
# 右表独有
SELECT * FROM
user_info AS ur
RIGHT JOIN
city_info AS ci
ON ur.city_id = ci.id
WHERE ur.id IS NULL;
并集去交集
查询两张表中各自独有的数据，把交集部分去掉
# 并集去交集
SELECT * FROM user_info AS ur LEFT JOIN city_info AS ci ON ur.city_id = ci.id WHERE ci.id IS NULL
UNION
SELECT * FROM user_info AS ur RIGHT JOIN city_info AS ci ON ur.city_id = ci.id WHERE ur.id IS NULL;
自然连接
MySQL自行根据相同的字段名判断并完成连接，不需要指定条件；
因为上面的表测试自然连接不太明显，因此这里重新创建两张表来进行测试
DROP TABLE IF EXISTS `t1`;
CREATE TABLE `t1` (
`id` int(11) NOT NULL,
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of t1
-- ----------------------------
INSERT INTO `t1` VALUES (1, '张三');
INSERT INTO `t1` VALUES (2, '李四');
INSERT INTO `t1` VALUES (3, '王五');
INSERT INTO `t1` VALUES (4, '胡九');
DROP TABLE IF EXISTS `t2`;
CREATE TABLE `t2` (
`id` int(11) NOT NULL,
`t2` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of t2
-- ----------------------------
INSERT INTO `t2` VALUES (1, '李四');
INSERT INTO `t2` VALUES (2, '王五');
INSERT INTO `t2` VALUES (3, '钱八');
INSERT INTO `t2` VALUES (5, '梁六');
可以看到，左右两张表都有id字段，自然连接，就会以id作为关联
自然内连接
取两张表的交集
SELECT * FROM t1 NATURAL JOIN t2;自然左连接
包含左边表的所有字段
SELECT * FROM t1 NATURAL LEFT JOIN t2;自然右连接
包含右边表的所有字段
SELECT * FROM t1 NATURAL RIGHT JOIN t2;
怎么样？
现在让你再去写复杂的联表查询还会晕吗？相信只要业务逻辑思路清晰之后，要写上个复杂联表查询，轻轻松松了吧！
END
精品资料，超赞福利，免费领
最近开发整理了一个用于速刷面试题的小程序；其中收录了上千道常见面试题及答案(包含基础、并发、JVM、MySQL、Redis、Spring、SpringMVC、SpringBoot、SpringCloud、消息队列等多个类型)，欢迎您的使用。QQ交流群：912509560