mysql中的各种join整理

布满荆棘的人生 2023-01-09 14:29 135阅读 0赞

Join可以分为两种

①implicit join

②explicit join

举例如下:

<table style="width:500px;"> 
 <tbody> 
  <tr> 
   <td>类型</td> 
   <td>SQL举例</td> 
  </tr> 
  <tr> 
   <td>explicit join</td> 
   <td>SELECT * FROM A a JOIN B b ON a.id = b.pid;</td> 
  </tr> 
  <tr> 
   <td>explicit join</td> 
   <td>SELECT * FROM A a INNER JOIN B b ON a.id = b.pid;</td> 
  </tr> 
  <tr> 
   <td>implicit join</td> 
   <td>SELECT * FROM A a,B b WHERE a.id = b.pid;</td> 
  </tr> 
 </tbody> 
</table>

implicit join的意思是说,where语句其实也是属于一种join

只是在sql中没有表现出来.

\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#

<table> 
 <tbody> 
  <tr> 
   <td style="width:115px;"> <h2><strong>join类型</strong></h2> </td> 
   <td style="width:496px;"> <h2><strong>解释</strong></h2> </td> 
   <td style="width:278px;"> <h2>图解</h2> </td> 
  </tr> 
  <tr> 
   <td style="width:115px;"><strong>inner join</strong></td> 
   <td style="width:496px;">两张表中都有的数据才会显示</td> 
   <td style="width:278px;"><img alt="" height="111" src="https://img-blog.csdnimg.cn/20210121181903580.png" width="177"></td> 
  </tr> 
  <tr> 
   <td style="width:115px;"><strong>left join</strong></td> 
   <td style="width:496px;"> <p>则显示a表中存在的全部数据及a、b中都有的数据，</p> <p>a中有、b中没有的数据以null显示</p> </td> 
   <td style="width:278px;"><img alt="" height="109" src="https://img-blog.csdnimg.cn/2021012118193094.png" width="175"></td> 
  </tr> 
  <tr> 
   <td style="width:115px;"><strong>right join</strong></td> 
   <td style="width:496px;"> <p>显示b表中存在的全部数据及a、b中都有的数据，</p> <p>b中有、a中没有的数据以null显示</p> </td> 
   <td style="width:278px;"><img alt="" height="110" src="https://img-blog.csdnimg.cn/20210121181947293.png" width="177"></td> 
  </tr> 
  <tr> 
   <td style="width:115px;"><strong>full join</strong></td> 
   <td style="width:496px;">两张表中所有数据都显示，实际就是inner +(left-inner)+(right-inner)</td> 
   <td style="width:278px;"><img alt="" height="105" src="https://img-blog.csdnimg.cn/20210121182007917.png" width="179"></td> 
  </tr> 
  <tr> 
   <td style="width:115px;"><strong>cross join</strong></td> 
   <td style="width:496px;">笛卡尔积(排列组合)</td> 
   <td style="width:278px;">没有合适的图来表达</td> 
  </tr> 
 </tbody> 
</table>

**Reference:**

**\[1\][sql中的join和inner join的区别][sql_join_inner join]**

\[2\][在python的dataframe中进行类似于mysql的join操作(持续更新)][python_dataframe_mysql_join]

[sql_join_inner join]: https://blog.csdn.net/hjfcgt123/article/details/84988077
[python_dataframe_mysql_join]: https://blog.csdn.net/appleyuchi/article/details/100695418?ops_request_misc=%25257B%252522request%25255Fid%252522%25253A%252522161122447816780262562511%252522%25252C%252522scm%252522%25253A%25252220140713.130102334.pc%25255Fblog.%252522%25257D&request_id=161122447816780262562511&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2~blog~first_rank_v1~rank_blog_v1-1-100695418.pc_v1_rank_blog_v1&utm_term=join%20dataframe