目录
3.1 左连接: A left join B on A.id=B.id, 返回记录数与A表记录数一致,无论A/B表是否能匹配上
3.3 使用 where ... is null 子句的 left join
【主旨:MySQL中 left join 的on和where的差异】
1 建表
drop table ASM_Product if exists;
create table ASM_Product(
ID int(11) unsigned not null auto_increment comment '主键ID',
Amount int(11) unsigned default null comment '数量',
PRIMARY KEY(ID)
)ENGINE=InnoDB CHARSET=utf8mb4 COMMENT='产品表'
drop table ASM_ProductDetails if exists;
create table ASM_ProductDetails(
ID int(11) unsigned not null auto_increment comment '主键ID',
Weight int(11) unsigned default null comment '重量',
Exist int(11) unsigned default null comment '是否有库存',
PRIMARY KEY(ID)
)ENGINE=InnoDB CHARSET=utf8mb4 COMMENT='产品详细信息表'
2 插入数据
insert into ASM_Product(Amount) values (100),(200),(300),(400);
insert into ASM_ProductDetails(ID, Weight,Exist) values (2,22,0),(4,44,1),(5,55,0),(6,66,1);
select * from ASM_Product; select * from ASM_ProductDetails;
3 测试
3.1 左连接: A left join B on A.id=B.id, 返回记录数与A表记录数一致,无论A/B表是否能匹配上
select * from ASM_Product p left join ASM_ProductDetails pd on p.ID=pd.ID;
3.2 左连接中, on条件与where条件过滤的差异
" A left join B on 条件表达式 " :"on条件表达式中的条件" 用来决定如何从B表中检索数据行,这个过程是A表与B表的匹配阶段。如果B表中没有任何一行记录匹配 "on条件表达式中的条件",将会额外生成一行所有列都为null的数据行。在匹配阶段,“where子句中的条件” 都不会起作用,直到匹配阶段结束之后,“where子句中的条件”才会起作用,它将从匹配阶段产生的数据中检索过滤。
(1)使用 ON条件 决定了从 left join 的ASM_ProductDetails表中检索符合ON条件的所有数据行。
select * from ASM_Product p left join ASM_ProductDetails pd on p.ID=pd.ID and pd.ID=2;
(2)left join 之后,使用 where子句 从 left join 的数据中过滤掉不符合where条件的数据行。
select * from ASM_Product p left join ASM_ProductDetails pd on p.ID=pd.ID where pd.ID=2;
(3)所有来自ASM_Product表的数据行都被检索到了,但没有在ASM_ProductDetails表中匹配到记录。
select * from ASM_Product p left join ASM_ProductDetails pd on p.ID=pd.ID and p.Amount=100;
(4)所有来自ASM_Product表的数据行都被检索到了,同时在ASM_ProductDetails表中也匹配到了记录。
select * from ASM_Product p left join ASM_ProductDetails pd on p.ID=pd.ID and p.Amount=200;
3.3 使用 where ... is null 子句的 left join
select * from ASM_Product p left join ASM_ProductDetails pd on p.ID = pd.ID and pd.Weight != 44 and pd.Exist = 0 where pd.ID is null;
解析:先过滤on条件获取到B表的结果集,合并到A表后,再对结果集用where条件过滤。
select * from ASM_Product p left join ASM_ProductDetails pd on p.ID = pd.ID and pd.Weight != 44 and pd.Exist = 1 where pd.ID is null;
解析:先过滤on条件获取到B表的结果集,合并到A表后,再对结果集用where条件过滤。
参考:https://www.oschina.net/question/89964_65912