资料表集合与文氏图

数学上,文氏图常用作集合论的表达工具,在做资料库查询的时候,其实也常常使用到这样的概念,交集、差集、联集、补集…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

INNER JOIN, LEFT JOIN, RIGHT JOIN

如果 A 表和 B 表可以有某种关联,则会把他们的双方都有的关联做交集 (Intersection),这个交集後,会出现三个区块 LEFT JOIN, INNER JOIN, RIGHT JOIN,你可以选择任一个区块当作主要的集合,带出另一个集合,像是下表:

https://ithelp.ithome.com.tw/upload/images/20210919/20092753nSvG9k8CuB.png

不难想像,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 来进行查询,一但 userpersonal info 它不存在各自的表,只要有一个找不到,则这笔资料就不会显示,必须是两边都有才会显示。 (user 或 personal info 存在缺漏资料时)

现在,对这两个表填充一下资料

PersonalInfo:

https://ithelp.ithome.com.tw/upload/images/20210919/20092753qpnzqDxigh.png

Users:

https://ithelp.ithome.com.tw/upload/images/20210919/20092753ayCWk1wvO1.png

然後,写一个 1-1 关系查询的 JOIN:

SELECT * FROM users u
INNER JOIN PersonalInfo pi ON pi.id = u.PersonalInfoId;

此时因为 2 不存在某一处 (user 2 不在 PersonalInfo),则无法被 INNER JOIN 一对一的找到,所以连显示都不会,因此,如果要寻找存在性就不可以使用 INNER JOIN,因为你根本找不出两边的映射,它也不会显示**不完整"**的资料,就像下图。

https://ithelp.ithome.com.tw/upload/images/20210919/20092753vWvkbk0DXy.png

请参考下图,如果想要完整的列出 左边的表 users,但 Right 右边可有可无,或是左右资料量不是 1-1 的大小,则使用 LEFT JOIN,换句话说,如果要以 users 寻找是否存在一个或更多 personal info,需要用 users 去 LEFT JOIN PersonalInfo。

https://ithelp.ithome.com.tw/upload/images/20210919/20092753I2ozx7eKHh.png

比方说,一个使用者有很多订单,所以要查询一个使用者的很多笔订单,就可以使用 LEFT JOIN 来找出多笔。

RIGHTLEFT 的对照,只是左右互换,可以把 SELECT * FROM A LEFT JOIN B, A 跟 B 互换本身就变成 RIGHT JOIN,只是在谁大谁小的问题。

UNION 和 FULL JOIN

如果说,现在希望检查两张表的资料,任一边 A, B 只要有一边不存在,都 SHOW 出来这个状况,请参考下表图例:

PersonalInfo 少 2, 比 user 多一个 5

https://ithelp.ithome.com.tw/upload/images/20210919/20092753y9k9HRdY0x.png

Users 有 2,但少一个 5

https://ithelp.ithome.com.tw/upload/images/20210919/20092753toffOFkFt6.png

此时就可以使用 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 中都各自带出了隔壁没有的资料,就容易看出缺失之处,如下图结果:

https://ithelp.ithome.com.tw/upload/images/20210919/20092753U8umL5Y7PU.png

所以,整个 FULL JOIN 是全部两边集合(不管大小),不是只有中间,如下图:

https://ithelp.ithome.com.tw/upload/images/20210919/20092753g8ZG0AuT6M.png

备注
显然,以逻辑来说,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

SELF JOIN,表示自己 JOIN 自己,在图表中是:

https://ithelp.ithome.com.tw/upload/images/20210919/20092753b8T4afpkJK.png

SELF JOIN 要如何示范? 假设 PersonalInfo 的资料中,想要找出同一个地址,且不同人的身分证,见下图示。

https://ithelp.ithome.com.tw/upload/images/20210919/20092753QFa39VW9Je.png

则可以这麽查询:

SELECT pi1.identity, pi2.identity, pi1.address 
FROM PersonalInfo pi1, PersonalInfo pi2  -- 一次查两表
WHERE pi1.address = pi2.address AND pi1.identity <> pi2.identity; -- 处理两表关系

地址相同,人不同,得到下方的结果,如下图。

https://ithelp.ithome.com.tw/upload/images/20210919/20092753ComqJ8BjIU.png

笛卡儿乘积与 CROSS JOIN

什麽是笛卡儿乘积 (Cartesian product) ?

最简单的范例就是 9x9 乘法表,所以先建立一个类似 9x9 乘法表的两个表:

CREATE TABLE A(
	num INT PRIMARY KEY
);
CREATE TABLE B(
	num INT PRIMARY KEY
);

在 A, B 表分别填入 1~9 (这里不小心填到 10):

https://ithelp.ithome.com.tw/upload/images/20210919/20092753X5XRX4aoXe.png

建立好资料後,现在要直接对 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;

https://ithelp.ithome.com.tw/upload/images/20210919/20092753DKw0Hitb9g.png

现在,可以清楚知道,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)

【Day20】:Servo控制-By PWM输出

Servo 对於简单的角度控制,大家第一个想到的就是伺服马达了吧,大小也适中,非常适合用在机器人上。...

Redux流程 + 如何调用 redux 的 state

这篇会介绍的几种调用 redux state 的方式,都是工作上遇到的,算是长了知识,网路上也许会有...

Day16 - Shioaji X Backtesting - 参数最佳化

上一篇介绍如何在Backtesting写策略,这篇会和大家介绍如何做参数的最佳化。 什麽是参数最佳化...

LeetCode 双刀流:144. Binary Tree Preorder Traversal

144. Binary Tree Preorder Traversal 今天挑选的是一题「二元树(...

Day 22 : 模型优化 - 知识蒸馏 Knowledge Distillation

什麽是知识蒸馏 Knowledge Distillation 知识蒸馏 Knowledge Dist...