关系型数据库最难的地方,就是建模(model)。
错综复杂的数据,需要建立模型,才能储存在数据库。所谓"模型"就是两样东西:实体(entity)+ 关系(relationship)。
实体指的是那些实际的对象,带有自己的属性,可以理解成一组相关属性的容器。关系就是实体之间的联系,通常可以分成"一对一"、"一对多"和"多对多"等类型。
在关系型数据库里面,每个实体有自己的一张表(table),所有属性都是这张表的字段(field),表与表之间根据关联字段"连接"(join)在一起。所以,表的连接是关系型数据库的核心问题。
表的连接分成好几种类型。
- 内连接(inner join)
- 外连接(outer join)
- 左连接(left join)
- 右连接(right join)
- 全连接(full join)
以前,很多文章采用维恩图(两个圆的集合运算),解释不同连接的差异。
上周,我读到一篇文章,认为还有比维恩图更好的解释方式。我发现确实如此,换一个角度解释,更容易懂。
所谓"连接",就是两张表根据关联字段,组合成一个数据集。问题是,两张表的关联字段的值往往是不一致的,如果关联字段不匹配,怎么处理?比如,表 A 包含张三和李四,表 B 包含李四和王五,匹配的只有李四这一条记录。
很容易看出,一共有四种处理方法。
- 只返回两张表匹配的记录,这叫内连接(inner join)。
- 返回匹配的记录,以及表 A 多余的记录,这叫左连接(left join)。
- 返回匹配的记录,以及表 B 多余的记录,这叫右连接(right join)。
- 返回匹配的记录,以及表 A 和表 B 各自的多余记录,这叫全连接(full join)。
下图就是四种连接的图示。我觉得,这张图比维恩图更易懂,也更准确。
上图中,表 A 的记录是 123,表 B 的记录是 ABC,颜色表示匹配关系。返回结果中,如果另一张表没有匹配的记录,则用 null 填充。
这四种连接,又可以分成两大类:内连接(inner join)表示只包含匹配的记录,外连接(outer join)表示还包含不匹配的记录。所以,左连接、右连接、全连接都属于外连接。
这四种连接的 SQL 语句如下。
SELECT * FROM A INNER JOIN B ON A.book_id=B.book_id; SELECT * FROM A LEFT JOIN B ON A.book_id=B.book_id; SELECT * FROM A RIGHT JOIN B ON A.book_id=B.book_id; SELECT * FROM A FULL JOIN B ON A.book_id=B.book_id;
上面的 SQL 语句还可以加上where
条件从句,对记录进行筛选,比如只返回表 A 里面不匹配表 B 的记录。
SELECT * FROM A LEFT JOIN B ON A.book_id=B.book_id WHERE B.id IS null;
另一个例子,返回表 A 或表 B 所有不匹配的记录。
SELECT * FROM A FULL JOIN B ON A.book_id=B.book_id WHERE A.id IS null OR B.id IS null;
此外,还存在一种特殊的连接,叫做"交叉连接"(cross join),指的是表 A 和表 B 不存在关联字段,这时表 A(共有 n 条记录)与表 B (共有 m 条记录)连接后,会产生一张包含 n x m 条记录的新表(见下图)。
(完)
我是酱油侠 说:
哈哈哈,最近正好在看数据库的东西,阮老师就发这块的文章啦,
感觉cross join这种连接方式,只能用下面这种方式表达了,用维恩图维度上好像不行…
2019年1月15日 21:17 | # | 引用
扣篮 说:
"返回表 A 或表 B 所有不匹配的记录"的例子
这里是不是应该用 full join ?
2019年1月15日 21:46 | # | 引用
阮一峰 说:
@扣篮:
谢谢指出,确实写错了,已经更正。
2019年1月15日 21:53 | # | 引用
faxiangui 说:
最后一个交叉连接是不是笛卡儿积?
2019年1月16日 16:14 | # | 引用
DoLo-lty 说:
"返回表 A 或表 B 所有不匹配的记录" 还不如直接用Inner join
2019年1月16日 17:32 | # | 引用
xingvyuan 说:
峰哥终于更新了 ,满满的期待,作图不易,且看且保留
2019年1月16日 18:06 | # | 引用
极度愤青 说:
SELECT * FROM A , B
2019年1月17日 15:44 | # | 引用
Kevin 说:
阮老师的文章都是浅显易懂。
2019年1月17日 17:16 | # | 引用
Liant 说:
MySQL好像是不支持FULL JOIN的,阮老师如果再写一写主流的关系型数据库对这几种JOIN的支持就好了
2019年1月18日 10:17 | # | 引用
周润发 说:
我现在在自学前端,看到这篇文章,想了解一下各位前辈公司里用的是什么数据库?
2019年1月18日 16:20 | # | 引用
最王座 说:
cross join结果是笛卡尔积吗?
2019年1月19日 10:11 | # | 引用
刘德华 说:
不错,明了大方。
2019年1月22日 14:55 | # | 引用
royaso 说:
博主最近的更文都是这样的,没啥深度
2019年1月23日 10:07 | # | 引用
weiShilei 说:
问下各位交叉连接(cross join)这种链接在实际运用中会用在什么场景或情况下呢?
2019年1月23日 21:41 | # | 引用
阿童木 说:
我是前端,数据库知识懂的不多,我猜测是否可以这样用交叉连接:
表A是学生,表B是各科,交叉起来做成绩单?
2019年1月28日 16:17 | # | 引用
Rambone 说:
Spark SQL的join语法有点差异
2019年2月 2日 14:41 | # | 引用
kenny 说:
非常棒的解释,简明扼要。
2019年2月 9日 19:41 | # | 引用
lanruo 说:
是笛卡尔积,条件不足,重复连接了而已
2019年2月11日 17:09 | # | 引用
八嘎虎 说:
学了数据库一年了,看了这篇终于懂了
感动的哭
2019年3月12日 20:04 | # | 引用
hanscity 说:
讲的真好,简洁明了
2019年3月19日 19:50 | # | 引用
james 说:
不错 ,提炼出精华了
2019年3月20日 15:34 | # | 引用
lpz 说:
第一张图的supplier多两个字段吧
2019年3月28日 13:02 | # | 引用
丢火车 说:
好的方法真的很重要。数据库表连接这下估计是忘不了了。
2019年6月10日 09:57 | # | 引用
曾亦可 说:
印象深刻 多谢多谢
2019年8月23日 22:18 | # | 引用
New撕裂的天堂 说:
先收藏再说。
2019年12月27日 16:37 | # | 引用
周星驰 说:
我司用nodejs + mongoDB
2020年2月19日 21:57 | # | 引用
zoyua 说:
简单易懂
2020年10月27日 11:33 | # | 引用