数学上,文氏图常用作集合论的表达工具,在做资料库查询的时候,其实也常常使用到这样的概念,交集、差集、联集、补集…etc,都是集合代数的运算操作。
简单的说,文氏图关注的是集合与集合之间的交集。
集合,在资料库被当作 Table,而 JOIN 的方式,就是一张表如何连结另外一张表的关系。
在 MySQL 中,JOIN 最常见的方式有:
INNER JOIN
LEFT JOIN == LEFT OUTER JOIN
(相同的东西)RIGHT JOIN == RIGHT OUTER JOIN
(相同的东西)FULL OUTER JOIN
本篇也会在介绍几个特别的 JOIN:
CROSS JOIN
FULL JOIN = FULL OUTER JOIN
SELF JOIN
UNION
如果 A 表和 B 表可以有某种关联,则会把他们的双方都有的关联做交集 (Intersection),这个交集後,会出现三个区块 LEFT JOIN
, INNER JOIN
, RIGHT JOIN
,你可以选择任一个区块当作主要的集合,带出另一个集合,像是下表:
不难想像,LEFT JOIN
区块就是以 LEFT JOIN
存在的元素交集 RIGHT JOIN
,此时要是有东西不存在 LEFT JOIN
这区,也就不会带出来; 这件事对 RIGHT JOIN
区块来说亦然,可是 INNER JOIN
则是只针对两边的交集部分进行陈列,所以只要两边没有交集,资料也不会出现。 (有关联的资料才会出现的意思)
为了底下举例方便,我建立了测试的资料表:
-- A: User (使用者表)
CREATE TABLE users (
id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
PersonalInfoId INT NOT NULL)
-- B: PersonalInfo (使用者个人资料)
CREATE TABLE PersonalInfo(
id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
identity VARCHAR(256),
address VARCHAR(512)
)
现在,你如果确定一个 user
就一定会有一个 personal info
个人资料的资料表对应,就属於 1 对 1 的映射关系,你就可以使用 INNER JOIN
来进行查询,一但 user
或 personal info
它不存在各自的表,只要有一个找不到,则这笔资料就不会显示,必须是两边都有才会显示。 (user 或 personal info 存在缺漏资料时)
现在,对这两个表填充一下资料
PersonalInfo:
Users:
然後,写一个 1-1 关系查询的 JOIN:
SELECT * FROM users u
INNER JOIN PersonalInfo pi ON pi.id = u.PersonalInfoId;
此时因为 2
不存在某一处 (user 2
不在 PersonalInfo),则无法被 INNER JOIN 一对一的找到,所以连显示都不会,因此,如果要寻找存在性就不可以使用 INNER JOIN,因为你根本找不出两边的映射,它也不会显示**不完整"**的资料,就像下图。
请参考下图,如果想要完整的列出 左边的表 users
,但 Right 右边
可有可无,或是左右资料量不是 1-1 的大小,则使用 LEFT JOIN
,换句话说,如果要以 users
寻找是否存在一个或更多 personal info
,需要用 users
去 LEFT JOIN PersonalInfo。
比方说,一个使用者有很多订单,所以要查询一个使用者的很多笔订单,就可以使用 LEFT JOIN
来找出多笔。
而 RIGHT
是 LEFT
的对照,只是左右互换,可以把 SELECT * FROM A LEFT JOIN B
, A 跟 B 互换本身就变成 RIGHT JOIN,只是在谁大谁小的问题。
如果说,现在希望检查两张表的资料,任一边 A, B 只要有一边不存在,都 SHOW 出来这个状况,请参考下表图例:
PersonalInfo 少 2, 比 user 多一个 5
Users 有 2,但少一个 5
此时就可以使用 FULL JOIN (mysql 不支援,可使用 LEFT + RIGHT JOIN 的方法) ,或称为 UNION。
此方法的不同之处在於两边资料表都有缺漏资料,或用另一个观点看,两边资料不对称,用此方法找出两边不对称的地方。
针对上述资料的问题,则写一段 SQL 呈现这个状况:
-- LEFT 表为主的资料去查另一张表 (只差在 LEFT JOIN),这会带出隔壁没有的资料
SELECT * FROM users u
LEFT JOIN PersonalInfo pi ON pi.id = u.PersonalInfoId
-- UNION 起来
UNION
-- RIGHT 表为主的资料去查令一张表 (只差在 RIGHT JOIN),这也会带出隔壁没有的资料
SELECT * FROM users u
RIGHT JOIN PersonalInfo pi ON pi.id = u.PersonalInfoId
上述 SQL 中都各自带出了隔壁没有的资料,就容易看出缺失之处,如下图结果:
所以,整个 FULL JOIN 是全部两边集合(不管大小),不是只有中间,如下图:
备注
显然,以逻辑来说,Users
, PersonalInfo
不应该随便缺任一笔资料,友善对待资料,请加上 Foreign Key Check
,您可以少一些白发。
CREATE TABLE users (
id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
PersonalInfoId INT NOT NULL,
CONSTRAINT FK_personalInfo FOREIGN KEY(PersonalInfoId) REFERENCES PersonalInfo(id)
)
SELF JOIN,表示自己 JOIN 自己,在图表中是:
SELF JOIN 要如何示范? 假设 PersonalInfo
的资料中,想要找出同一个地址,且不同人的身分证,见下图示。
则可以这麽查询:
SELECT pi1.identity, pi2.identity, pi1.address
FROM PersonalInfo pi1, PersonalInfo pi2 -- 一次查两表
WHERE pi1.address = pi2.address AND pi1.identity <> pi2.identity; -- 处理两表关系
地址相同,人不同,得到下方的结果,如下图。
什麽是笛卡儿乘积 (Cartesian product) ?
最简单的范例就是 9x9 乘法表,所以先建立一个类似 9x9 乘法表的两个表:
CREATE TABLE A(
num INT PRIMARY KEY
);
CREATE TABLE B(
num INT PRIMARY KEY
);
在 A, B 表分别填入 1~9 (这里不小心填到 10):
建立好资料後,现在要直接对 A, B 做 CROSS JOIN:
SELECT A.num as A, B.num as B, A.num * B.num as 乘积 FROM A
CROSS JOIN B
ORDER BY A.num, B.num;
现在,可以清楚知道,CROSS JOIN 就是把两张表的每一项都 JOIN,而且不指定任何条件,即便 A, B; B, A 交换出现也会 JOIN 每一项,这个也就是笛卡儿乘积 (所有可能的情况)。
备注
使用 CROSS JOIN 产生的笛卡儿乘积绝对会对效能造成很大的负担。
另外在本篇没有介绍到的东西是 DISTINCT
,如果你在单一张表中会出现多笔重复的资料,想要按照某个栏位只选出一笔,则可以使用 DISTINCT
。
References:
[1] https://justcode.ikeepstudying.com/2016/08/mysql-%E5%9B%BE%E8%A7%A3-inner-join%E3%80%81left-join%E3%80%81right-join%E3%80%81full-outer-join%E3%80%81union%E3%80%81union-all%E7%9A%84%E5%8C%BA%E5%88%AB/
[2] https://www.chriswirz.com/materialized-views-in-mysql/
[3] https://www.educba.com/joins-in-mysql/
[4] https://www.techagilist.com/mainframe/db2/outer-join-step-by-step-walkthrough-with-examples/
[5] https://stackoverflow.com/questions/13997365/sql-joins-as-venn-diagram
[6] https://www.techonthenet.com/mysql/joins.php
[7] https://www.guru99.com/joins.html
>>: Day 4 资讯结构与阶层分析- (main content + footer)
Servo 对於简单的角度控制,大家第一个想到的就是伺服马达了吧,大小也适中,非常适合用在机器人上。...
这篇会介绍的几种调用 redux state 的方式,都是工作上遇到的,算是长了知识,网路上也许会有...
上一篇介绍如何在Backtesting写策略,这篇会和大家介绍如何做参数的最佳化。 什麽是参数最佳化...
144. Binary Tree Preorder Traversal 今天挑选的是一题「二元树(...
什麽是知识蒸馏 Knowledge Distillation 知识蒸馏 Knowledge Dist...