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

以你之姓@ 2022-10-11 13:44 167阅读 0赞

![图片][d5b300cf67e6bd5c4ae99f31f0723d8c.png]

雷猴！我是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);
    
    ![图片][0565151bebb9eb769c03eaf7b9ac8f9e.png]
 *  **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, '深圳');
    
    ![图片][4941f5e64584c790aab82d9d25ce1abd.png]

## 2联表测试 ##

### 内连接 inner join ###

内连接查询两张表直接的`交集部分`，只保留两张表都有的字段

![图片][f93f9ff3e8d2456780b593e0bb9c7cb2.png]

# INNER JOIN
    SELECT *  FROM 
     user_info AS ur 
     INNER JOIN 
     city_info AS ci 
     ON ur.city_id = ci.id;

![图片][3931a912b4560f7732a7ee687e0ac01b.png]

### 左连接 left join ###

返回`左边表中的所有行`，即使右边表中没有行与之匹配，左边的行依然显示，右边没有匹配尚的显示为null

![图片][cd7896ad4d44e33c8aabe745eaef3f21.png]

# LEFT JOIN 
    SELECT *  FROM user_info AS ur LEFT JOIN city_info AS ci ON ur.city_id = ci.id;

![图片][413ebaf82850d6e8640d9453e476615e.png]

### 右连接 right join ###

和左连接正好相反，返回`右边表的所有行`，即使左边没有行与之匹配，未匹配上的显示null

![图片][aeb89fc5da92484c9947461ddab0a6ba.png]

#RIGHT JOIN
    SELECT *  FROM 
     user_info AS ur 
     RIGHT JOIN 
     city_info AS ci 
     ON ur.city_id = ci.id;

![图片][2a0e9843adef8e9a60dacbbf2ee16724.png]

### 全连接full join ###

通俗理解，就是取两张表的`并集`；mysql中不支持该语法，但是可以采用UNION方式完成

![图片][b453a1df19408fc324d235c5facb36ee.png]

# 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;

![图片][a8c8f3ab6b77e3d0a5f61c4b9174259f.png]

### 左表独有 ###

查询左边表独有的数据

![图片][23d605e1e3ea282249756d7c1197b0b7.png]

# 左表独有
    SELECT *  FROM 
     user_info AS ur 
     LEFT JOIN 
     city_info AS ci 
     ON ur.city_id = ci.id 
     WHERE ci.id IS NULL;

![图片][4d90651635d7d4e9482e11cb0bc7a034.png]

### 右表独有 ###

查询右边表独有的数据

\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(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;

![图片][1dfa21ea947f14b83e7b91af2d328c87.png]

### 并集去交集 ###

查询两张表中各自独有的数据，把交集部分去掉

![图片][47d0bb43d7b388d0bdceed2bfd00d150.png]

# 并集去交集
    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;

![图片][3860068fd77251e258586e80724ecfb0.png]

### 自然连接 ###

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, '梁六');

![图片][9bf1680fbd3c4a1826b5d81f4a5db640.png]

可以看到，左右两张表都有id字段，自然连接，就会`以id作为关联`

*  自然内连接
    
    取两张表的交集
    
        SELECT *  FROM t1 NATURAL JOIN t2;
    
    ![图片][78556f010aba7c6e8cdfe5a4fd61d1a0.png]
 *  自然左连接
    
    包含左边表的所有字段
    
        SELECT *  FROM t1 NATURAL LEFT JOIN t2;
    
    ![图片][d481a54209a15e77ec2ef39dc7b275a9.png]
 *  自然右连接
    
    包含右边表的所有字段
    
        SELECT *  FROM t1 NATURAL RIGHT JOIN t2;
    
    ![图片][cc7796a89dd4c1959798701cb7bc13be.png]

怎么样？

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

![ebd1c0799f5cd83d1ec4c29627bed952.png][]

[d5b300cf67e6bd5c4ae99f31f0723d8c.png]: /images/20221005/0856f1ae876c4809bc513c5ee5037079.png
[0565151bebb9eb769c03eaf7b9ac8f9e.png]: /images/20221005/dc8b554e44414e37a33e2ccbaa8afd22.png
[4941f5e64584c790aab82d9d25ce1abd.png]: /images/20221005/b3a63629fb7c4d2b89981f15ccd214d7.png
[f93f9ff3e8d2456780b593e0bb9c7cb2.png]: /images/20221005/5456ca1d21dc4deb9d22f7c569edbe2e.png
[3931a912b4560f7732a7ee687e0ac01b.png]: /images/20221005/89ab5bd6e9384ff08c5b44a713ab0183.png
[cd7896ad4d44e33c8aabe745eaef3f21.png]: /images/20221005/38b2c00e34934e538a4ab5ce1d50c508.png
[413ebaf82850d6e8640d9453e476615e.png]: /images/20221005/3edcfeaf5d1d49b39d261e1554917368.png
[aeb89fc5da92484c9947461ddab0a6ba.png]: /images/20221005/5fd70ebe515c462eafe212b1f49fe6dd.png
[2a0e9843adef8e9a60dacbbf2ee16724.png]: /images/20221005/31435f827c5a400ca0e87f85f674566c.png
[b453a1df19408fc324d235c5facb36ee.png]: /images/20221005/34e4769736f54044a0681f49449be9c3.png
[a8c8f3ab6b77e3d0a5f61c4b9174259f.png]: /images/20221005/324b1ed510e7493092e6fc832ca460d4.png
[23d605e1e3ea282249756d7c1197b0b7.png]: /images/20221005/2f0e62a66ab64fe7aa0c68593754ae8b.png
[4d90651635d7d4e9482e11cb0bc7a034.png]: /images/20221005/0ba48e5f0ce742328c6ccd44757ada24.png
[1dfa21ea947f14b83e7b91af2d328c87.png]: /images/20221005/3ee6fa9a1f394f7ab0300dbd6ea34e7c.png
[47d0bb43d7b388d0bdceed2bfd00d150.png]: /images/20221005/b33f4c6f5dd44fb3b361338c463a5bbd.png
[3860068fd77251e258586e80724ecfb0.png]: /images/20221005/3bebb189848145cb952a4cbfdb073df2.png
[9bf1680fbd3c4a1826b5d81f4a5db640.png]: /images/20221005/58ba8b9827724856bda62937b46e87c5.png
[78556f010aba7c6e8cdfe5a4fd61d1a0.png]: /images/20221005/16dcebce04144204a3c57a5a5fcbb4a3.png
[d481a54209a15e77ec2ef39dc7b275a9.png]: /images/20221005/8af8fbacd58f4b85b6e187971fe8826a.png
[cc7796a89dd4c1959798701cb7bc13be.png]: /images/20221005/872cb91a9e024af98cb0cc5901fdd479.png
[ebd1c0799f5cd83d1ec4c29627bed952.png]: /images/20221005/5534ba8ede9c49b8a3370ef007a0ffea.png