MySql的Join深入（未完待续）

零、背景

本文涉及2个表，券主表Receipt和发券记录表ReceiptIssueRecord，1个订单可以拥有多张券，1张券只会对应1个订单。

建表语句

1
2
3
4
5
6
7
8
9
10
11
12
<-- 券主表 -->
CREATE TABLE `Receipt` (
  `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `OrderId` bigint(20) NOT NULL DEFAULT '0' COMMENT '订单编号',
  `ReceiptNumber` varchar(30) NOT NULL DEFAULT '"' COMMENT '券码',
  `AddTime` datetime NOT NULL DEFAULT '1980-01-01 00:00:00' COMMENT '添加时间',
  `UpdateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`ID`),
  UNIQUE KEY `IX_ReceiptNumber` (`ReceiptNumber`),
  KEY `IX_OrderId` (`OrderId`),
  KEY `IX_UpdateTime` (`UpdateTime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1
2
3
4
5
6
7
8
9
10
11
12
13
<-- 发券记录表 -->
CREATE TABLE `ReceiptIssueRecord` (
  `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键,自增id',
  `OrderId` bigint(20) NOT NULL DEFAULT '-1' COMMENT '订单ID',
  `BeginDate` datetime NOT NULL COMMENT '有效期开始时间',
  `EndDate` datetime NOT NULL COMMENT '有效期结束时间',
  `AddTime` datetime NOT NULL COMMENT '插入时间',
  `UpdateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`ID`),
  UNIQUE KEY `UK_OrderId` (`OrderId`),
  KEY `IX_IssueStatus` (`IssueStatus`),
  KEY `IX_UpdateTime` (`UpdateTime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMPRESSION='lz4' COMMENT='发券记录表';

表内容

券主表

发券记录表

一、多表查询

为了查出分布在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
2
select * from Receipt cross join ReceiptIssueRecord;
<-- 相当于 select * from Receipt, ReceiptIssueRecord -->

其结果是笛卡尔乘积，左表的每一条记录和右表的所有记录都做了连接，结果是极其可怕的。
（注：cross join 不能像其它join一样使用on关键字）

inner join（内连接）

inner join相当于join，带上on关键字，可以在多表连接时过滤不符合条件的记录，而不必等到连接完成后再用where子句筛选，查询语句：

1
2
3
select * 
 from Receipt inner join ReceiptIssueRecord 
  on Receipt.`OrderId` = ReceiptIssueRecord.`OrderId` and Receipt.`OrderId` = 5114049055891395;

结果：

inner join带上on关键字，左表和右表都必须符合on的限定条件，才会拿出来做拼接。
比如，上面的查询会从Receipt表中挑出orderId为5114049055891395的记录，再从ReceiptIssueRecord表中挑出orderId为5114049055891395的记录，2者拼接。

left join（左连接）

left join，也被称作left outer join。相比于inner join，它不要求左表和右表都符合on限定条件，而是把左表的行记录全部记录出来，然后按照on限定到右表查找，若找到则连接，若没有则用null拼接。

1
2
3
select * 
  from ReceiptIssueRecord left join Receipt 
    on ReceiptIssueRecord.`OrderId` =  Receipt.`OrderId`;

结果：

right join（右连接）

right join，和left join相反，全部列出右表所有记录，再从左表查找。

1
2
3
select * 
  from ReceiptIssueRecord right join Receipt 
    on ReceiptIssueRecord.`OrderId` =  Receipt.`OrderId`;

full join（全连接）

full join，是left join和right join的综合，全部列出左表和右表的记录，再针对左表的记录，到右表查，有则拼接无则null，接着针对右边记录，到左表查。

1
2
3
select * 
  from ReceiptIssueRecord full join Receipt 
    on ReceiptIssueRecord.`OrderId` =  Receipt.`OrderId`;

注：理论上，full join的结果是left join和right join的和，不过我在实际操作的时候，sql报错了，有待进一步研究

on VS where

on相对于join，就像是where相对于select。

在join的时候加上on限定，不会对表中所有数据进行连接，仅仅会挑选出符合限定条件的记录，这样就大大减少了查询成本，从千万级降到百千级，所以有人建议join查询时，尽量把条件都放在on里面。

如果同时使用on和where，则会在查询时根据on条件选择性连接，再根据where条件对连接结果进行筛选。