sql的各种连接查询（join合集）

客官°小女子只卖身不卖艺 2021-09-24 14:56 427阅读 0赞

利用sql语句来实现两个表或者多个表的连接查询，SQL join 用于根据两个或多个表中的列之间的关系，从这些表中查询数据。连接查询是另一种类型的多表查询。连接查询对多个表进行JOIN运算，简单地说，就是先确定一个主表作为结果集，然后，把其他表的行有选择性地“连接”在主表结果集上。

## 内连接和联查 ##

现在我有两张表，这两张表分别是人工智能词汇分组表以及人工智能词汇相关概念表，而且概念表通过字段GROUPID和分组表进行关联：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzE3ODkyNw_size_16_color_FFFFFF_t_70_pic_center]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzE3ODkyNw_size_16_color_FFFFFF_t_70_pic_center 1]  
首先可以使用多表查询法，也叫笛卡尔查询法，sql代码如下：

SELECT AI_GROUP.NAME_CH AS GROUP1 ,AI_CONCEPT_GROUP.NAME_CH AS CONCEPT_CH,AI_CONCEPT_GROUP.NAME_EN AS CONCEPT_EN
    FROM AI_CONCEPT_GROUP, AI_GROUP
    WHERE AI_GROUP.GROUPID = AI_CONCEPT_GROUP.GROUPID AND AI_GROUP.GROUPID = 7

上述代码的查询结果如下：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzE3ODkyNw_size_16_color_FFFFFF_t_70_pic_center 2]  
同样的我们可以使用 inner join来实现从多个表中返回满足 JOIN 条件的所有行。内连接查询代码如下：

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzE3ODkyNw_size_16_color_FFFFFF_t_70_pic_center 3]  
可以看出查询速度有明显增快，可以看出表连接查询还是比较快的。

## 左连接left join ##

left join返回的是左表都存在的行数据，如果右表的某一字段没有匹配到左表中，那么结果集就会以NULL填充剩下的字段。 左连接的原理示意图如下：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzE3ODkyNw_size_16_color_FFFFFF_t_70_pic_center 4]  
利用right join查询表连接的脚本：

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzE3ODkyNw_size_16_color_FFFFFF_t_70_pic_center 5]

## 右连接right join ##

right join返回的是join右边表都存在的行数据，如果左表没有匹配到游标，那么结果集就会以NULL填充剩下的字段。 右连接的原理示意图如下：  
![在这里插入图片描述][2020082315294285.png_pic_center]

上述脚本的查询出groupid=7对应的概念词中英文字段以及group字段，同时考虑到是右连接方式，因此左表中字段无法匹配其他6个group，因此用null填充。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzE3ODkyNw_size_16_color_FFFFFF_t_70_pic_center 6]

## 全连接（full join） ##

full join关键字返回左表（Websites）和右表（access\_log）中所有的行，匹配不到的行进行null填充，可以看作是左连接与有连接结果的集合。

全连接的原理示意图如下：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzE3ODkyNw_size_16_color_FFFFFF_t_70_pic_center 7]  
利用full join查询表连接的脚本：

上述脚本的查询出groupid=7对应的概念词中英文字段以及group字段，同时考虑到是全连接方式，因此左，右表中字段无法匹配其他字段都用null填充。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzE3ODkyNw_size_16_color_FFFFFF_t_70_pic_center 8]  
\*\*【注意!】\*\*左右表做笛卡尔乘积生成临时表，再按照 ON 子句进行过滤生成初步结果集，INNER JOIN 直接返回该结果集，LEFT JOIN 在结果集中再添加左表中 ON 子句过滤时完全未匹配到的行，不存在的数据置为 NULL，RIGHT JOIN 与 FULL JOIN 同理。

## SQL JOIN 中 on 与 where 的区别 ##

我们以左连接为列来说明on 与 where 的区别：  
on条件是在生成临时表时候使用的，先做笛卡尔乘积生成临时表1，按照on条件生成临时表2，再添加左表中 ON 子句过滤时完全未匹配到的行，最终形成查询结果；  
where条件是在临时表（已经on，left join之后）生成好之后，再对临时表进行过滤的条件。这时已经没有 left join 的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。  
以下是左连接情况下，on和where使用列子：

SELECT SCOPUS.AI_CONCEPT_GROUP.NAME_CH AS CONCEPT_CH,SCOPUS.AI_CONCEPT_GROUP.NAME_EN AS CONCEPT_EN,SCOPUS.AI_GROUP.NAME_CH AS GROUP_
    FROM SCOPUS.AI_CONCEPT_GROUP
    LEFT JOIN SCOPUS.AI_GROUP
    ON SCOPUS.AI_CONCEPT_GROUP.GROUPID = SCOPUS.AI_GROUP.GROUPID AND SCOPUS.AI_GROUP.GROUPID=6
    
    
    --将and改为where：
    SELECT SCOPUS.AI_CONCEPT_GROUP.NAME_CH AS CONCEPT_CH,SCOPUS.AI_CONCEPT_GROUP.NAME_EN AS CONCEPT_EN,SCOPUS.AI_GROUP.NAME_CH AS GROUP_
    FROM SCOPUS.AI_CONCEPT_GROUP
    LEFT JOIN SCOPUS.AI_GROUP
    ON SCOPUS.AI_CONCEPT_GROUP.GROUPID = SCOPUS.AI_GROUP.GROUPID WHERE SCOPUS.AI_GROUP.GROUPID=6

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzE3ODkyNw_size_16_color_FFFFFF_t_70_pic_center 9]  
根据上图可以得到：假设where查询之后，进一步筛选临时表，从而得到的结果和进行内连接或者多表联查的效果一样

reference material:  
https://www.cnblogs.com/liandy0906/p/9961515.html

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzE3ODkyNw_size_16_color_FFFFFF_t_70_pic_center]: /images/20210923/dc014098ccfb401bb23e704bcae02518.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzE3ODkyNw_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20210923/adb99430f3cc4b49a6862fd444fae58d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzE3ODkyNw_size_16_color_FFFFFF_t_70_pic_center 2]: /images/20210923/07c4de442794453996d9ef15745e0294.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzE3ODkyNw_size_16_color_FFFFFF_t_70_pic_center 3]: /images/20210923/2bc1948dfd4e411f94fba9b6d04f9256.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzE3ODkyNw_size_16_color_FFFFFF_t_70_pic_center 4]: /images/20210923/638985a0fdfe40f8801e61e15be9be82.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzE3ODkyNw_size_16_color_FFFFFF_t_70_pic_center 5]: /images/20210923/14829a52df114e2490489c5a1183f4a5.png
[2020082315294285.png_pic_center]: /images/20210923/d03fe0646b214aa0867a68cc0e012b6e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzE3ODkyNw_size_16_color_FFFFFF_t_70_pic_center 6]: /images/20210923/8665186e87114c5f94a6725ad6687a72.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzE3ODkyNw_size_16_color_FFFFFF_t_70_pic_center 7]: /images/20210923/33b132aabc1d4229b9ca53bea0957643.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzE3ODkyNw_size_16_color_FFFFFF_t_70_pic_center 8]: /images/20210923/09ed699b50b6477686e291865719535e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzE3ODkyNw_size_16_color_FFFFFF_t_70_pic_center 9]: /images/20210923/ef454e11cbc44f98b240aca333a7d938.png