SQL JOIN

Dear 丶 2021-10-01 02:20 351阅读 0赞

# SQL JOIN & MySQL 8.0 JOIN #

*  1.环境信息：  
    MySQL：8.0.16  
    MySQL Workbench：8.0
 *  2.数据库信息：  
    学生表：`t_student`  
    成绩表：`t_score`  
    关系图：两表通过`t_student`的`id`和`t_score`的`student_id`进行关联  
    ![在这里插入图片描述][20190809171236538.png]

`t_student`表数据：  
![在这里插入图片描述][2019081217150983.png]

`t_score`表数据：  
![在这里插入图片描述][20190812171750461.png]

## 1.标准SQL JOIN ##

各种Join图示：  
![在这里插入图片描述][20190812155828221.png]

### 1.1 JOIN类型 ###

*  1.(INNER) JOIN: 返回两个表中具有匹配值的记录（两表交集）
 *  2.CROSS JOIN: 返回两表笛卡尔积（第一个表中的每一行与第二个表中的每一行组合在一起的行）（两表交集）
 *  3.LEFT (OUTER) JOIN: 返回左表中的所有记录，以及右表中的匹配记录（符合某条件的并集）
 *  4.RIGHT (OUTER) JOIN: 返回右表中的所有记录，以及左表中匹配的记录（符合某条件的并集）
 *  5.FULL (OUTER) JOIN: 当左表或右表中匹配时返回所有记录（两表并集）

`左表右表`有点晕，可以理解为`第一个表`(左表)，`第二个表`(右表)，OUTER在左连接和右连接中经常省略：

select * from t_student(第一个表) a left (省略outer) join t_score(第二个表) b on a.id = b.student_id;

### 1.2 SQL JOIN 写法 ###

-- inner连接 一取两表交集
    select * from t_student a inner join t_score b on a.id = b.student_id;
    -- cross连接 一取两表笛卡尔积
    select * from t_student a  cross join t_score b ;
    -- 左连接 一第一张表为准进行关联，第二张表显示为NULL
    select * from t_student a left join t_score b on a.id = b.student_id;
    -- 左连接 一第二张表为准进行关联，第一张表显示为NULL
    select * from t_student a right join t_score b on a.id = b.student_id;
    -- full outer连接 一取两表并集
    select * from t_student a full outer join t_score b on id = b.student_id;

`cross join`结果：  
`t_student`表中的每一行与`t_score`表中的每一行组合成的新的结果集：一共4(`t_student`)x10(`t_score`)=40条数据  
![在这里插入图片描述][20190812174443376.png]

### 1.3 总结 ###

> *此处参考：https://en.wikipedia.org/wiki/Join\_(SQL) , https://www.w3schools.com/sql/sql\_join.asp*

## 2.MySQL 8.0 JOIN ##

### 2.1 JOIN类型 ###

与标准SQL相比，MySQL扩展了语法:

*  1.(INNER) JOIN/STRAIGHT\_JOIN: 返回两个表中具有匹配值的记录（两表交集）
 *  2.CROSS JOIN/STRAIGHT\_JOIN: 返回两表笛卡尔积（第一个表中的每一行与第二个表中的每一行组合在一起的行）（两表交集）
 *  3.LEFT (OUTER) JOIN: 返回左表中的所有记录，以及右表中的匹配记录（符合某条件的并集）
 *  4.RIGHT (OUTER) JOIN: 返回右表中的所有记录，以及左表中匹配的记录（符合某条件的并集）
 *  5.NATURAL INNER/OUTER LEFT/RIGHT JOIN: 返回去除重复的列名的结果集

MySQL Join 语法：

joined_table:
        table_reference {
       [INNER | CROSS] JOIN | STRAIGHT_JOIN} table_factor [join_specification]
      | table_reference {
       LEFT|RIGHT} [OUTER] JOIN table_reference join_specification
      | table_reference NATURAL [INNER | {
       LEFT|RIGHT} [OUTER]] JOIN table_factor
    
    join_specification:
        ON search_condition
      | USING (join_column_list)
    
    join_column_list:
        column_name [, column_name] ...
    
    index_hint_list:
        index_hint [, index_hint] ...

> In MySQL, JOIN, CROSS JOIN, and INNER JOIN are syntactic equivalents (they can replace each other). In standard SQL, they are not equivalent. INNER JOIN is used with an ON clause, CROSS JOIN is used otherwise.

在MySQL中JOIN，CROSS JOIN和INNER JOIN是句法当量（它们可以彼此替换），在标准SQL中，它们不等效。  
INNER JOIN与ON一起使用，CROSS JOIN 单独使用。

> STRAIGHT\_JOIN is similar to JOIN, except that the left table is always read before the right table. This can be used for those (few) cases for which the join optimizer processes the tables in a suboptimal order.

> *此处参考： https://dev.mysql.com/doc/refman/8.0/en/join.html*

#### 2.1.1 STRAIGHT\_JOIN说明 ####

STRAIGHT\_JOIN：同JOIN类似，但STRAIGHT\_JOIN指明左边的表(第一个表)作为驱动表进行查询；

-- 1 t_student join t_score
    explain select * from t_student student1  join t_score score1 on student1.id = score1.student_id;
    -- 2 t_student STRAIGHT_JOIN t_score
    explain select * from t_student student2  STRAIGHT_JOIN t_score score2 on student2.id = score2.student_id;
    
    -- 3 t_score join t_student
    explain select * from t_score score3  join t_student student3 on student3.id = score3.student_id;
    -- 4 t_score STRAIGHT_JOIN t_student
    explain select * from t_score score4  STRAIGHT_JOIN t_student student4 on student4.id = score4.student_id;

这四个SQL执行结果一样，但`执行计划`因为`JOIN`的顺序和`STRAIGHT_JOIN`的使用而不同：

> MySQL对多表连接的处理方式：首先MySQL优化器要确定以谁为驱动表，也就是说以哪个表为基准，在处理此类问题时，MySQL优化器采用了简单粗暴的解决方法：哪个表的结果集小，就以哪个表为驱动表，当然MySQL优化器实际的处理方式会复杂许多

-- 1 t_student join t_score
    explain select * from t_student student1  join t_score score1 on student1.id = score1.student_id;

![在这里插入图片描述][20190812181943647.png]驱动表为score1，先扫score1再扫student1：查询耗时：4.75，扫描rows：11个

![在这里插入图片描述][20190812182300231.png]

-- 2 t_student STRAIGHT_JOIN t_score
    explain select * from t_student student2  STRAIGHT_JOIN t_score score2 on student2.id = score2.student_id;

![在这里插入图片描述][20190812182130713.png]驱动表为student2，先扫student2再扫score2：查询耗时：4.91，扫描rows为14个  
![在这里插入图片描述][20190812182332175.png]

-- 3 t_score join t_student
    explain select * from t_score score3  join t_student student3 on student3.id = score3.student_id;

![在这里插入图片描述][20190812182144672.png]驱动表为score3，先扫score3再扫student3：查询耗时：4.75，扫描rows为11个  
![在这里插入图片描述][2019081218235365.png]

-- 4 t_score STRAIGHT_JOIN t_student
    explain select * from t_score score4  STRAIGHT_JOIN t_student student4 on student4.id = score4.student_id;

![在这里插入图片描述][20190812182203656.png]驱动表为score4，先扫score4再扫student4：查询耗时：4.75，扫描rows为11个  
![在这里插入图片描述][20190812182412157.png]  
往表中再插入些数据后，发现还是`2 t_student STRAIGHT_JOIN t_score`的耗时最多：![在这里插入图片描述][20190812184334901.png]  
`STRAIGHT_JOIN`的作用就是指定`第一个表`为`驱动表`，使用时需注意，可能会影响`性能`。

### 2.2 MySQL 8.0 JOIN写法 ###

select * from t_student a inner join t_score b on a.id = b.student_id;
    select * from t_student a cross join t_score b on a.id = b.student_id;
    select * from t_student a straight_join t_score b on a.id = b.student_id;
    select * from t_student a left join t_score b on a.id = b.student_id;
    select * from t_student a right join t_score b on a.id = b.student_id;
    select * from t_student a natural left join t_score b ;
    select * from t_student a natural right join t_score b ;
    select * from t_student a natural inner join t_score b ;

### 2.3 总结 ###

<table> 
 <thead> 
  <tr> 
   <th>类型</th> 
   <th>Join类型</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>交集</td> 
   <td>(INNER) JOIN</td> 
   <td>取两表交集</td> 
  </tr> 
  <tr> 
   <td>交集</td> 
   <td>CROSS JOIN</td> 
   <td>取两表笛卡尔集</td> 
  </tr> 
  <tr> 
   <td>交集</td> 
   <td>STRAIGHT_JOIN</td> 
   <td>取两表笛卡尔集</td> 
  </tr> 
  <tr> 
   <td>并集</td> 
   <td>LEFT (OUTER) JOIN</td> 
   <td>第一张表为准进行关联</td> 
  </tr> 
  <tr> 
   <td>并集</td> 
   <td>RIGHT (OUTER) JOIN</td> 
   <td>第二张表为准进行关联</td> 
  </tr> 
  <tr> 
   <td>结果集</td> 
   <td>NATURAL INNER JOIN</td> 
   <td>去除重复的列名的结果集</td> 
  </tr> 
  <tr> 
   <td>结果集</td> 
   <td>NATURAL LEFT (OUTER) JOIN</td> 
   <td>去除重复的列名的结果集</td> 
  </tr> 
  <tr> 
   <td>结果集</td> 
   <td>NATURAL RIGHT (OUTER) JOIN</td> 
   <td>去除重复的列名的结果集</td> 
  </tr> 
 </tbody> 
</table>

参考资料：

> \[ 1 \]. https://en.wikipedia.org/wiki/Join\_(SQL)  
> \[ 2 \]. https://www.w3schools.com/sql/sql\_join.asp  
> \[ 3 \]. https://dev.mysql.com/doc/refman/8.0/en/join.html  
> \[ 4 \]. https://blog.huoding.com/2013/06/04/261

[20190809171236538.png]: /images/20210911/7f93151637fa4ef182a6c5b4bac7705f.png
[2019081217150983.png]: /images/20210911/22364290e5e54290bece7cedb31d4dfa.png
[20190812171750461.png]: /images/20210911/ac4a6e116d024a0ab26209eb7603274e.png
[20190812155828221.png]: /images/20210911/959b663cf4de47378ced14407b3dcc7d.png
[20190812174443376.png]: /images/20210911/fda68006dc424d40ab2a321561775249.png
[20190812181943647.png]: /images/20210911/9d07061dd53c474484580d089ec98f86.png
[20190812182300231.png]: /images/20210911/7edd707023024c8397e0d527400ab54f.png
[20190812182130713.png]: /images/20210911/6e6ec5427aeb4ee1aceb2ea804b75102.png
[20190812182332175.png]: /images/20210911/334fc23118ba48d7a774a6fe5a247ae1.png
[20190812182144672.png]: /images/20210911/42d1283d4629470ebfa02066d0552c13.png
[2019081218235365.png]: /images/20210911/1e3c8d60781a40ed89267c392af84664.png
[20190812182203656.png]: /images/20210911/569386a2ec6d42beb5e4fb55792633fb.png
[20190812182412157.png]: /images/20210911/46e72710c02a4f7f87e89866d4e35adc.png
[20190812184334901.png]: /images/20210911/daf184f16e534380991a64de9c5665f6.png