等值连接_图解 SQL 中各种连接 JOIN

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

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

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

c031a6ac58e84e4cb651e64e164dac20.png

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

115487ce467a944b5957dcfcdcdd8140.png

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

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

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

一张图来总结一下

bb962bdf5999f9ca6d9dcc8868c08332.png

自连接

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

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

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

923bfe2c7e0167f212a1b288ec293193.png

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

SELECT b.lastStation,b.nextStation,a.lastStation,a.nextStation 
FROM bus_sche a, bus_sche b 
WHERE b.nextStation = a.lastStation;

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

查询结果

bcc886276e59b3611ad84f03e1066f16.png

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

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

33013dabe3eac0f918413b69cbdc97c4.png

b1e32ffa4e138316ea8feaab7e85ddc0.png

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

内连接

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

SELECT * from Table_A JOIN Table_B;
SELECT * from Table_A INNER JOIN Table_B;

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

等值连接

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

SELECT * from Table_A A JOIN Table_B B ON A.id = B.id;

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

4c7f21f31d08131e3215c891e8a10ebf.png

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

6eb04d3613dc629a7c9cc238d166e4a6.png
红色部分代表查询结果

不等连接

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

SELECT * from Table_A A JOIN Table_B B ON A.id < B.id;  

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

181679c921baffed43ebb88b1e35fc6e.png

自然连接

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

SELECT * from Table_A NATURAL JOIN Table_B ; 
SELECT * from Table_A A NATURAL JOIN Table_B B WHERE A.id = B.id; 

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

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

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

466223296d16e6ebd4550c1b47a1e7eb.png

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

bceb5cc56c3584ec2bd4550c50e86026.png
红色部分代表查询结果

交叉连接

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

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

f5ec15328839725021910b9150cd813a.png
SELECT * from Table_A CROSS JOIN Table_B;

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

8757dcbdcfee11a950318bb765cf6c5f.png

外连接

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

左连接(左外连接)

SELECT * from Table_A A LEFT JOIN Table_B B ON A.id = B.id;
SELECT * from Table_A A LEFT OUTER JOIN Table_B B ON A.id = B.id;

查询结果如下

0ce0319e4561347e3ee4358f84e84e8d.png

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

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

62c7c28c0b08bb2b321612874ab700a7.png
红色部分代表查询结果

右连接(右外连接)

SELECT * from Table_A A RIGHT JOIN Table_B B ON A.id=B.id;
SELECT * from Table_A A RIGHT OUTER JOIN Table_B B ON A.id=B.id;

查询结果如下

85dbaf4993f938103a1a793242e79d01.png

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

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

1a9e1b1fe5beb2b2763ef007c6ff95e4.png
红色部分代表查询结果

全连接(全外连接)

SELECT * from Table_A A FULL JOIN Table_B B ON A.id=B.id;
SELECT * from Table_A A FULL OUTER JOIN Table_B B ON A.id=B.id;

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

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

d39f0d2bde6f0c8a5e1bca5d6c2785f5.png

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

5495f038736b67912fe1c3588ebae5d4.png

最后总结一下,自连接仅发生在一张基表内;内连接中的自然连接结果是多表的交集;外连接中,除了交集,哪边连接,就加上哪边的表,无交集部分为 Null。

在公众号「MoTec」后台回复「offer」,免费获取互联网校招合集、群面礼包、笔试经、面试经。

如果觉得文章,资料不错,劳烦你点赞或转发一下文章给更多同样有需要的伙伴,一起过笔试、面试,拿到大厂高薪 offer。

已标记关键词 清除标记
表情包
插入表情
评论将由博主筛选后显示,对所有人可见 | 还能输入1000个字符
相关推荐
©️2020 CSDN 皮肤主题: 数字20 设计师:CSDN官方博客 返回首页