SQL中 inner join、left join、right join、full join 到底怎么选？详解来了

一行Java

发布于 2022-04-07 05:38:12

1.1K00

代码可运行

文章被收录于专栏：用户9257747的专栏用户9257747的专栏

运行总次数：0

代码可运行

雷猴！我是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;

怎么样？

现在让你再去写复杂的联表查询还会晕吗？相信只要业务逻辑思路清晰之后，要写上个复杂联表查询，轻轻松松了吧！

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2021-07-01，如有侵权请联系 cloudcommunity@tencent.com 删除

数据库

sql

云数据库 SQL Server

腾讯云测试服务

本文分享自一行Java 微信公众号，前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体同步曝光计划，欢迎热爱写作的你一起参与！

登录后参与评论

0 条评论

热度