手记

图解 SQL 中各种连接 JOIN

先用文字来捋一下思路,数据库操作中无非就是「增删查改」,其中「 查」用得最多且最复杂,变化多端。查询的时候,我们可以只是单表查询,也可以是多表连接查询,单表查询中的学问也很大,但限于篇幅,本次主要分享多表连接查询,也就是各种各样的连接(JOIN)。

至于其他的,有时间、机会或者大家有强烈需求(文底或后台留言)再总结一下后分享。

多表连接查询中的「多表」,可以是同一张表自己和自己连接查询。

相当于(可以理解为) A 表自己先复制自己后再和自己连接,如此称为「自连接

也可以在不同张表中连接查询,可分为「内连接」、「交叉连接」、「外连接」。

内连接根据所使用的比较方式不同,又分为「等值连接」、「自然连接」和「不等连接」三种,连接的结果列出这些表中与连接条件相匹配的数据行。

与内连接不同的是,外连接不只列出与连接条件相匹配的行,而是列出左表(左外连接时)、右表(右外连接时)或两个表(全外连接时)中所有符合搜索条件的数据行。

外连接分为「左外连接」或「左连接」( OUTER JOIN 或 LEFT JOIN)、「右外连接」或「右连接」(RIGHT OUTER JOIN 或 RIGHT JOIN)和「全外连接」或「全连接」(FULL OUTER JOIN 或 FULL JOIN)三种。

一张图来总结一下

自连接

一张表中,假如有两个以上的字段,且这些字段有一定的关系,我们又刚好想摸清这些关系字段的数据,就可以在这上面做文章,俗称「自连接」。

其中最经典的例子是车站站点表。

如果用的是关系型数据库,早期开发中估计这么干,现在估计用的都是图数据库。我们建一张表 bus_sche,为了简单,表中只有上一站地点和下一站地点及唯一标识,然后插入一些模拟数据。

我就可以通过自连接查询上下站关系找到坐车线路

只在一张表中查询,表 bus_sche 使用了两个别名 bus_sche a, bus_sche b,因此相当于有两张表,用 WHERE条件连接查询,「实际只有一张表在自我连接查询」。

查询结果


一句话,「 一张表,使用多个别名,表面上进行多表连接查询,这就是自连接查询」。

分享多张不同表连接查询前,为了简单,只建两张表,Table_A,Table_B

注意两张表的 id ,A中有1、3、4、8,B中有1、2、3、5、6,还有一个字段分别是 name 和names,该字段数据都是按顺序的小写字母,前面再加个 A 或 B 为了方便区分属于哪个表。

内连接

内连接查询操作只列出与连接条件匹配的数据行,使用INNER JOIN或者直接使用JOIN 进行连接。

内连接可以没有连接条件,没有条件之后的查询结果,会保留所有结果(笛卡尔集),与后面分享的交叉连接差不多。

等值连接

在连接条件中使用等于号(=)运算符比较被连接列的列值,其查询结果中列出被连接表中的所有列,包括其中的重复列

查询结果,注意列数是 4 列,两张表的字段直接拼接在一起,重复的字段在后面添加数字序列以做区分

通俗讲就是根据条件,找到表 A 和 表 B 的数据的交集,加以Venn 图表示就是

红色部分代表查询结果

不等连接

而不等连接跟等值连接仅仅是连接条件中使用的运算符不一样,其余一致。不等连接使用的是除等于号运算符以外的其它比较运算符,如>、>=、<=、<、!>、!<和<> 等。

根据条件,一个个做比较,满足条件的所有结果 

自然连接

在连接条件中使用等于(=)运算符比较被连接列的列值,但它使用选择列表指出查询结果集合中所包括的列,并删除连接表中的重复列

注意添加条件用 WHERE,而不是用 ON,如用 ON,会报语法错误,不知是不是 MySQL 版本问题,具体不敢确定。如果你知道具体原因,不妨动动手指留言一下。

在连接查询中,通常不用 WHERE 而是用 ON,因为 WHERE 没有 ON 效率高。ON 指匹配到第一条成功的就结束,其他不匹配;若没有,不进行匹配而 WHERE 会一直匹配,进行判断。

查询结果,注意是已经删除了重复列,列数只有 3,这也是和等值连接的区别


根据条件,找到表 A 和 表 B 的数据的交集,但字段已经去重, Venn 图表示就是

红色部分代表查询结果

交叉连接

交叉连接不带 WHERE 子句,它返回被连接的两个表所有数据行的笛卡尔积。从一张表中循环取出每一条记录,每条记录都会去另一张表中匹配每一条记录,匹配结果一定保留(因为无条件,如果有条件,则只保留满足条件的结果)。

假设 A 表有 n 条记录,B 表有 m 条记录,则结果为 n * m 条记录。

因为 A 表数据有 4 条,B 表数据有 5 条,4 x 5 = 20,因此交叉查询结果有 20 条,如下

外连接

外连接不只列出与连接条件相匹配的行,而且还加上左表(左外连接时)或右表(右外连接时)或两个表(全外连接时)中所有符合搜索条件的数据行。

左连接(左外连接)

查询结果如下

根据条件,用右表(B)匹配左表(A),能匹配,正确保留,不能匹配其他表的字段都置空 Null

也就是,根据条件找到表 A 和 表 B 的数据的交集,再加上左表的数据集, Venn 图表示就是

红色部分代表查询结果

右连接(右外连接)

查询结果如下


根据条件,用左表(A)匹配右表(B),能匹配,正确保留,不能匹配其他表的字段都置空 Null

也就是,根据条件找到表 A 和 表 B 的数据的交集,再加上右表的数据集, Venn 图表示就是

红色部分代表查询结果


全连接(全外连接)

目前我的 MySQL 不支持此种方式,可以用其他方式替代解决,在此不展开。

理论上是根据条件找到表 A 和 表 B 的数据的交集,再加上左右表的数据集,因此 Venn 图表示如下

红色部分代表查询结果


另外,再补上国外大神的两表内外连接查询的各种情况的查询语句及 Venn 图



作者:MoTec
链接:https://www.jianshu.com/p/fbf0c0f51861


0人推荐
随时随地看视频
慕课网APP