码霸霸

雷猴！我是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://blog.lupf.cn/articles/2021/07/01/1625104828246.html