零、背景
本文涉及2个表,券主表Receipt和发券记录表ReceiptIssueRecord,1个订单可以拥有多张券,1张券只会对应1个订单。
建表语句
1 | <-- 券主表 --> |
1 | <-- 发券记录表 --> |
表内容
券主表
发券记录表
一、多表查询
为了查出分布在2个表里的数据,我们会在sql语句的from子句里写上2个表名:
1 | select * from Receipt, ReceiptIssueRecord where Receipt.OrderId = ReceiptIssueRecord.OrderId |
结果:
为什么会这样的呢?我们对上面的查询语句做下修改,去掉where子句,看看有什么变化,拭目以待:
1 | select * from Receipt, ReceiptIssueRecord |
结果:
来自UC小编的震惊!去掉where子句,我们得到的是2个表的乘积(5 * 4 = 20),也就是笛卡尔乘积,总20条记录!此时,我们不难明白为何上面的sql查询结果只有3条,因为它限定了左边的orderId=右边的orderId。
开大脑洞想一想,假若线上券主表有1w记录,发券记录表有1K记录,我们这个sql语句,随随便便跑出来了1千万条结果!
所谓的笛卡尔乘积,实力过于强悍~我军无法与之抗衡,迅速撤退!
结论:在from子句里,多个表用逗号连接起来,结果=笛卡尔乘积。
(注:后面吃瓜群众会发现,这种多表查询,本质上是交叉连接cross join)
二、Join查询
Join,根据多个表的列,从左到右,将多个表拼接起来。
Join分为几种,cross join,inner join,left join,right join,full join和join。
cross join(交叉连接)
cross join即是第一节里多表查询的方式,查询语句:
1 | select * from Receipt cross join ReceiptIssueRecord; |
其结果是笛卡尔乘积,左表的每一条记录和右表的所有记录都做了连接,结果是极其可怕的。
(注:cross join 不能像其它join一样使用on关键字)
inner join(内连接)
inner join相当于join,带上on关键字,可以在多表连接时过滤不符合条件的记录,而不必等到连接完成后再用where子句筛选,查询语句:
1 | select * |
结果:
inner join带上on关键字,左表和右表都必须符合on的限定条件,才会拿出来做拼接。
比如,上面的查询会从Receipt表中挑出orderId为5114049055891395的记录,再从ReceiptIssueRecord表中挑出orderId为5114049055891395的记录,2者拼接。
left join(左连接)
left join,也被称作left outer join。相比于inner join,它不要求左表和右表都符合on限定条件,而是把左表的行记录全部记录出来,然后按照on限定到右表查找,若找到则连接,若没有则用null拼接。
1 | select * |
结果:
right join(右连接)
right join,和left join相反,全部列出右表所有记录,再从左表查找。
1 | select * |
full join(全连接)
full join,是left join和right join的综合,全部列出左表和右表的记录,再针对左表的记录,到右表查,有则拼接无则null,接着针对右边记录,到左表查。
1 | select * |
注:理论上,full join的结果是left join和right join的和,不过我在实际操作的时候,sql报错了,有待进一步研究
on VS where
on相对于join,就像是where相对于select。
在join的时候加上on限定,不会对表中所有数据进行连接,仅仅会挑选出符合限定条件的记录,这样就大大减少了查询成本,从千万级降到百千级,所以有人建议join查询时,尽量把条件都放在on里面。
如果同时使用on和where,则会在查询时根据on条件选择性连接,再根据where条件对连接结果进行筛选。