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

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

内连接和联查

现在我有两张表，这两张表分别是人工智能词汇分组表以及人工智能词汇相关概念表，而且概念表通过字段GROUPID和分组表进行关联：


首先可以使用多表查询法，也叫笛卡尔查询法，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

上述代码的查询结果如下：

同样的我们可以使用 inner join来实现从多个表中返回满足 JOIN 条件的所有行。内连接查询代码如下：

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
INNER JOIN SCOPUS.AI_GROUP
ON SCOPUS.AI_CONCEPT_GROUP.GROUPID = SCOPUS.AI_GROUP.GROUPID AND SCOPUS.AI_GROUP.GROUPID=7

可以看出查询速度有明显增快，可以看出表连接查询还是比较快的。

左连接left join

left join返回的是左表都存在的行数据，如果右表的某一字段没有匹配到左表中，那么结果集就会以NULL填充剩下的字段。 左连接的原理示意图如下：

利用right join查询表连接的脚本：

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=7

右连接right join

right join返回的是join右边表都存在的行数据，如果左表没有匹配到游标，那么结果集就会以NULL填充剩下的字段。 右连接的原理示意图如下：

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
RIGHT JOIN SCOPUS.AI_GROUP
ON SCOPUS.AI_CONCEPT_GROUP.GROUPID = SCOPUS.AI_GROUP.GROUPID AND SCOPUS.AI_GROUP.GROUPID=7

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

全连接（full join）

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

全连接的原理示意图如下：

利用full join查询表连接的脚本：

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
FULL JOIN SCOPUS.AI_GROUP
ON SCOPUS.AI_CONCEPT_GROUP.GROUPID = SCOPUS.AI_GROUP.GROUPID AND SCOPUS.AI_GROUP.GROUPID=6

上述脚本的查询出groupid=7对应的概念词中英文字段以及group字段，同时考虑到是全连接方式，因此左，右表中字段无法匹配其他字段都用null填充。

**【注意!】**左右表做笛卡尔乘积生成临时表，再按照 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

根据上图可以得到：假设where查询之后，进一步筛选临时表，从而得到的结果和进行内连接或者多表联查的效果一样

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