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

  |   评论   |   浏览

 如遇图片加载失败,可尝试使用手机流量访问

雷猴!我是mbb!

作为一名 CURD工程师,联表查询应该就算是一项相对复杂的工作了吧,如果表结构复杂一点,左一连,右一连,内一连再加上外一连,很可能就被绕晕的,最终得到的数据集就不是自己理想中的结果;

能被绕晕呢,无非就两种情况!要么是业务不熟悉,对数据的理解不够深入;要么就是对各种联表查询的细微的差别了解的不够深入;

首先来看一下数据库表链接的几种方式

  • inner join 内连接
  • left join 左连接
  • right join 右连接
  • full join 全连接(mysql没有,oricle有)
  • 以及四种方式衍生出的其他数据集

四种方式本质都是做表之间的关联,仅仅只是存在了些许细微的差别,最终带来两表之间不同的结果集;

下面就通过两张示例表加上示意图,以最简单的方式去理解一下他们各自的差异;

准备

一张用户表,一张城市表,用户表中有个城市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, '深圳');

     如遇图片加载失败,可尝试使用手机流量访问

联表测试

内连接 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;

 如遇图片加载失败,可尝试使用手机流量访问

右表独有

查询右边表独有的数据

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-seqLlXAC-1625104478959)(https://mmbiz.qpic.cn/mmbiz_png/GjuWRiaNxhnTuzwQIUxLE6m6wbIPQb69BW8yVoXovFhJO6icyKGgbLIQJa8KvCeFUicB6h7ZurT3IvH1rwy2RrIvQ/640?wx_fmt=png)]

# 右表独有 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;

     如遇图片加载失败,可尝试使用手机流量访问

怎么样?

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



标题:SQL中 inner join、left join、right join、full join 到底怎么选?详解来了
作者:码霸霸
地址:https://lupf.cn/articles/2021/07/01/1625104828246.html