一篇文章理解inner join/left join/right join的细节

叁歲伎倆 2024-04-18 18:16 56阅读 0赞

# 一篇文章理解inner join/left join/right join的细节 #

> 本文强烈建议自己动手实验。两表连接比较简单，可以直接跳到多表连接查看

### 结论 ###

无论是双表还是多表关联，**可以** 通过分解步骤来化解复杂性。比如三张表，先考虑前两张表进行关联，得到的结果想象成临时表，再跟第三张表关联。连接的顺序对结果是会产生影响的！

### 一、两表 ###

#### 1、一对一 ####

people(id,name)；id\_card(id,people\_id,card\_no)。数据见附录。

###### 例 ######

--结论：查出3条数据。inner join必须两表的数据都能连接上才会展示
    select * from people a inner join id_card b on a.id=b.people_id;

###### 例 ######

-- 结论：查出4条数据。以左表为准。左表的数据都会出现，若左表某行数据无右表数据关联，则右表所有字段补null
    -- 注意：假设左表有4条数据，左连接的时候不是一定的4条数据，而是大于等于4,假设右表跟左表是多对一的，则左表会超过4条数据
    select * from people a left join id_card b on a.id=b.people_id;

###### 例 ######

-- 结论：查出4条数据。跟上述左连接相反，以右边数据为准
    select * from people a right join id_card b on a.id=b.people_id;

备注：mysql **没有** full join

#### 2、一对多 ####

master(id,name)；pet(id,name,master\_id)。数据见附录。

###### 例 ######

-- 结论：查出2条，1条master记录和2条pet记录组合
    select * from master a inner join pet b  on a.id=b.master_id;

###### 例 ######

-- 结论：展示3条记录，其中能连接上的展示了2条，不能连接的展示为1条。不能连接的右表展示为null
    select * from master a left join pet b  on a.id=b.master_id;

**注意**：

*  为什么出现2条记录而不是1条？不是说 “以左表为准” 吗？
    
     *  答：“以左表为准” 并不能得出 “left join的数量等于左表”。反证法：如果查询结果是1条记录，那右表应该展示的哪条记录呢？所以肯定是展示2条的。

###### 例 ######

-- 结论：展示为3条记录，其中一条无法连接的，左边的表展示为null
    select * from master a right join pet b  on a.id=b.master_id;

### 二、多表 ###

多表比较复杂，常回让人产生困扰，让人困惑于连接的细节。例如3张表A、B、C，可能会产生如下困惑：

*  `B、C关联A` 和 `B关联A，C关联B`两种情况是相同的吗？前面的情况有共同的基础的关联表A；但是后面是一环套一环关联的
 *  连接的运算顺序是怎么样的？顺序不同结果会不同吗？
 *  表和表之间可能存在多对一的情况

**结论见开头**

#### 1、一对一 ####

people(id,name)；id\_card(id,people\_id,card\_no)；country(id,country,people\_id)。数据见附录。

###### 例 ######

-- 结论：先前面2张表连表，得到3行记录，再和最后一张表连表，最终得到2行记录
    select * from people a 
    inner join id_card b on a.id=b.people_id
    inner join country c on a.id=c.people_id

###### 例 ######

-- 结论：先前面2张表连表，得到4行记录，所有的people表记录都会出现。再和country表连接，最终得到4行记录
    select * from people a 
    left join id_card b on a.id=b.people_id
    left join country c on a.id=c.people_id

###### 例 ######

-- 结论：第一步，前面2表得到临时表的结果；第二步，将先前的结果再一次运算
    
    -- 1
    select * from people a 
    left join id_card b on a.id=b.people_id
    inner join country c on a.id=c.people_id
    
    -- 2
    select * from people a 
    left join id_card b on a.id=b.people_id
    right join country c on a.id=c.people_id

#### 一对多 ####

master(id,name)；pet(id,name,master\_id)；toy(id,toy\_name,pet\_id)。数据见附录。

###### 例 ######

-- 结论：按顺序进行连接
    select * from master a
    inner join pet b on a.id=b.master_id
    inner join toy c on b.id=c.pet_id

###### 例 ######

-- 结论：按顺序进行连接
    select * from master a
    left join pet b on a.id=b.master_id
    left join toy c on b.id=c.pet_id

### 附录 ###

> 测试的表和数据

CREATE TABLE `country`  (
      `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
      `country` varchar(255) NOT NULL,
      `people_id` int(11) NOT NULL,
      PRIMARY KEY (`id`) USING BTREE
    );
    INSERT INTO `country` VALUES (1, '仙剑1', 1);
    INSERT INTO `country` VALUES (2, '仙剑2', 2);
    INSERT INTO `country` VALUES (3, '仙剑-1', -1);
    
    
    CREATE TABLE `id_card`  (
      `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
      `card_no` varchar(50) NOT NULL,
      `people_id` int(11) NOT NULL,
      PRIMARY KEY (`id`) USING BTREE
    );
    INSERT INTO `id_card` VALUES (1, '441', 1);
    INSERT INTO `id_card` VALUES (2, '442', 2);
    INSERT INTO `id_card` VALUES (3, '443', 3);
    INSERT INTO `id_card` VALUES (4, '-1', -1);
    
    
    CREATE TABLE `master`  (
      `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
      `name` varchar(255) NOT NULL,
      PRIMARY KEY (`id`) USING BTREE
    );
    INSERT INTO `master` VALUES (1, '爱宠人士');
    INSERT INTO `master` VALUES (2, '不养宠物');
    
    
    CREATE TABLE `people`  (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(255) NOT NULL,
      PRIMARY KEY (`id`) USING BTREE
    );
    INSERT INTO `people` VALUES (1, '玄霄');
    INSERT INTO `people` VALUES (2, '天河');
    INSERT INTO `people` VALUES (3, '韩菱纱');
    INSERT INTO `people` VALUES (4, '柳梦璃');
    
    
    CREATE TABLE `pet`  (
      `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
      `name` varchar(255) NOT NULL,
      `master_id` int(11) NOT NULL,
      PRIMARY KEY (`id`) USING BTREE
    );
    INSERT INTO `pet` VALUES (1, '阿猫', 1);
    INSERT INTO `pet` VALUES (2, '阿狗', 1);
    INSERT INTO `pet` VALUES (3, '流浪猫狗', -1);
    
    
    CREATE TABLE `toy`  (
      `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
      `toy_name` varchar(255) NOT NULL,
      `pet_id` int(11) NOT NULL,
      PRIMARY KEY (`id`) USING BTREE
    );
    INSERT INTO `toy` VALUES (1, '阿猫的玩具：毛线', 1);
    INSERT INTO `toy` VALUES (2, '阿猫的玩具：纸团', 1);
    INSERT INTO `toy` VALUES (3, '没有主的玩具', -1);