SQL语句LEFT JOIN，RIGHT JOIN，INNER JOIN 的理解

男娘i 2022-05-18 08:37 222阅读 0赞

# SQL语句LEFT JOIN，RIGHT JOIN，INNER JOIN 的理解 #

### 环境MySQL ###

#### 准备工作 ####

1.  创建表并插入数据  
    table\_a

CREATE TABLE IF NOT EXISTS table_a ( `id` INT UNSIGNED PRIMARY KEY AUTO_INCREMENT , `parent_id` VARCHAR(100) NOT NULL, `user_id` VARCHAR(100) NOT NULL, `name` VARCHAR(100) NOT NULL DEFAULT 'table_a' );
    
    INSERT INTO table_a( `parent_id`, `user_id` ) VALUES ( 'parent_id_1','user_id');
    
    INSERT INTO table_a( `parent_id`, `user_id` ) VALUES ( 'parent_id_2','user_id');
    INSERT INTO table_a( `parent_id`, `user_id` ) VALUES ( 'surplus_a','surplus_a');

table\_b

CREATE TABLE IF NOT EXISTS table_b ( `id` INT UNSIGNED PRIMARY KEY AUTO_INCREMENT , `parent_id` VARCHAR(100) NOT NULL, `user_id` VARCHAR(100) NOT NULL, `name` VARCHAR(100) NOT NULL DEFAULT 'table_b' );
    
    INSERT INTO table_b( `parent_id`, `user_id` ) VALUES ( 'parent_id_1','user_id');
    
     INSERT INTO table_b( `parent_id`, `user_id` ) VALUES ( 'parent_id_2','user_id');
     INSERT INTO table_b( `parent_id`, `user_id` ) VALUES ( 'surplus_b','surplus_b');

1.  结果  
    table\_a

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>parent_id</th> 
   <th align="center">user_id</th> 
   <th align="right">name</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>parent_id_1</td> 
   <td align="center">user_id</td> 
   <td align="right">table_a</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>parent_id_2</td> 
   <td align="center">user_id</td> 
   <td align="right">table_a</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>surplus_a</td> 
   <td align="center">surplus_a</td> 
   <td align="right">table_a</td> 
  </tr> 
 </tbody> 
</table>

table\_b

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>parent_id</th> 
   <th align="center">user_id</th> 
   <th align="right">name</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>parent_id_1</td> 
   <td align="center">user_id</td> 
   <td align="right">table_b</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>parent_id_2</td> 
   <td align="center">user_id</td> 
   <td align="right">table_b</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>surplus_b</td> 
   <td align="center">surplus_b</td> 
   <td align="right">table_b</td> 
  </tr> 
 </tbody> 
</table>

3. 这里准备了 2 张表作为测试表，他们都有重复的数据，除了name字段

#### LEFT JOIN ####

*  测试

SELECT  table_a.parent_id, table_a.user_id, table_a.name, 
                    table_b.parent_id, table_b.user_id, table_b.name 
                    FROM table_a
                    LEFT JOIN table_b on table_a.user_id = table_b.user_id;

*  结果  
    table\_b

<table> 
 <thead> 
  <tr> 
   <th>parent_id</th> 
   <th align="center">user_id</th> 
   <th align="center">name</th> 
   <th align="right">parent_id</th> 
   <th>user_id</th> 
   <th>name</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>parent_id_1</td> 
   <td align="center">user_id</td> 
   <td align="center">table_a</td> 
   <td align="right">parent_id_1</td> 
   <td>user_id</td> 
   <td>table_b</td> 
  </tr> 
  <tr> 
   <td>parent_id_2</td> 
   <td align="center">user_id</td> 
   <td align="center">table_a</td> 
   <td align="right">parent_id_1</td> 
   <td>user_id</td> 
   <td>table_b</td> 
  </tr> 
  <tr> 
   <td>parent_id_1</td> 
   <td align="center">user_id</td> 
   <td align="center">table_a</td> 
   <td align="right">parent_id_2</td> 
   <td>user_id</td> 
   <td>table_b</td> 
  </tr> 
  <tr> 
   <td>parent_id_2</td> 
   <td align="center">user_id</td> 
   <td align="center">table_a</td> 
   <td align="right">parent_id_2</td> 
   <td>user_id</td> 
   <td>table_b</td> 
  </tr> 
  <tr> 
   <td>surplus_a</td> 
   <td align="center">surplus_a</td> 
   <td align="center">table_a</td> 
   <td align="right">null</td> 
   <td>null</td> 
   <td>null</td> 
  </tr> 
 </tbody> 
</table>

\- 结果分析  
就此例而言  
1、**table\_a和table\_b的交集部分**  
LEFT JOIN 会按照table\_b的每一行数据中user\_id为准去匹配table\_a的每一行，匹配到就会返回一行结果,匹配不到则不会返回。(换一种说法按照table\_a的每一行数据中user\_id为准去匹配table\_b的每一行，匹配到就会返回一行结果,匹配不到则不会返回。两个方式结果都是一致的)  
*需要注意的是如果leftjoin的条件不具唯一性，按照上述规则会匹配除多条结果*。  
2、**table\_a和table\_b的交集在table\_a的补集部分**  
如果table\_a中有未匹配到userid的行，也会返回，table\_b自动补null。

![left join][]

#### RIGHT JOIN ####

*  结果分析  
    同LEFT JOIN 的结果分析，只是，table\_a和table\_b对调

#### INNER JOIN ####

*  测试

SELECT  table_a.parent_id, table_a.user_id, table_a.name, 
                    table_b.parent_id, table_b.user_id, table_b.name 
                    FROM table_a
                    INNER JOIN table_b on table_a.user_id = table_b.user_id;

*  结果

*  结果分析  
    就此例而言，结果就是leftjoin或rightjoin中区交集的那一部分逻辑。

#### 保证匹配规则的唯一性。 ####

*  测试

*  结果

\- 结果分析  
加上parent\_id这个条件后，在两个表中，都只有唯一的行能够匹配，因此只匹配到了table\_a和table\_b对应的那一行，结果仅输出两行。

[left join]: /images/20220518/bcc0b53adf0f499aa62500e92354ced6.png