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

  |   0 评论   |   浏览

图片

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