MySQL inner join 与 left/right outer join 的区别及查询逻辑

更新日期: 2017-10-09 阅读次数: 4515 分类: MySQL

例如,商品表与类型表,要查询指定类型名的商品,使用 inner join 还是 left outer join 呢?

用 left outer join 肯定没有问题,但是用 inner join 是否可以呢?

用真实数据来测试一下

Product 表

CREATE TABLE `product` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `category_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4;

1	苹果	1
2	大白菜	2
3	莱阳梨	1
4	樱桃	1
5	水蜜桃	1

category 表

CREATE TABLE `category` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;

1	水果
2	蔬菜

查询出 category name 为水果的所有 product

select product.name, category.name as category from product
left outer join `category`
on category.id = product.category_id
where category.name = '水果'

select product.name, category.name as category from product
inner join `category`
on category.id = product.category_id
where category.name = '水果'

这两条 SQL 的运行结果居然是一样的。这跟我的预期是有出入的,因为我以为 inner join 的结果会是只有随机一个水果 product 得到保留。而实际结果都是

苹果	水果
莱阳梨	水果
樱桃	水果
水蜜桃	水果

为什么会是一样的结果呢?

要搞明白,首先查一下 inner join 与 left outer join 的处理逻辑的分别是什么?

逻辑查询处理顺序

例如

(5) select * 
(1) from t1
(3) inner join t2
(2) on t1.xx = t2.id
(4) where t2.name = xxx

左侧标出的序号为逻辑查询顺序 (参考:MySQL 技术内幕 SQL 编程 - 逻辑查询处理)

这里的每一步都会产生一张虚拟表,做为下一步操作的输入。

  1. t1 与 t2 执行笛卡尔集,产生虚拟表 VT1
  2. 对 VT1 应用 ON 条件过滤,符合条件的插入虚拟表 VT2
  3. inner join 不参与此步逻辑,当为 outer left/right join 时,将保留表(left/right 表)中未匹配的行做为外部行添加到 VT2, 形成虚拟表 VT3.
  4. 对 VT3 应用 where 条件过滤,结果插入 VT4

这样看,上面这个例子执行结果一致,就很容易理解了。

笛卡尔集的结果

select product.name, category.name as category2 from product
inner join `category`

苹果	水果
苹果	蔬菜
大白菜	水果
大白菜	蔬菜
莱阳梨	水果
莱阳梨	蔬菜
樱桃	水果
樱桃	蔬菜
水蜜桃	水果
水蜜桃	蔬菜

而 left outer join 则不允许不加 ON 条件

select product.name, category.name as category from product
left outer join `category`

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 2

ON 过滤条件之后,结果一定一致么?

select product.name, category.name as category from product
left outer join `category`
on category.id = product.category_id

select product.name, category.name as category2 from product
inner join `category`
on category.id = product.category_id

这两条执行的结果已经一致了,因为对同样的笛卡尔集执行了同样的操作。

苹果	水果
大白菜	蔬菜
莱阳梨	水果
樱桃	水果
水蜜桃	水果

但是,如果 product 中包含了 category 中没有的 category_id,那么结果就完全不同了。

例如,product 中增加一条 category_id 为 3 的行,该 category_id 在 category 中并不存在。

select product.name, category.name as category from product
left outer join `category`
on category.id = product.category_id

苹果	水果
大白菜	蔬菜
莱阳梨	水果
樱桃	水果
水蜜桃	水果
大粪	NULL

select product.name, category.name as category2 from product
inner join `category`
on category.id = product.category_id

苹果	水果
大白菜	蔬菜
莱阳梨	水果
樱桃	水果
水蜜桃	水果

简称

  • inner join -> join (cross join)
  • left outer join -> left join

关于作者

我是来自山东烟台的一名开发者,喜欢瞎折腾,顺便记记笔记。有敢兴趣的话题,欢迎加微信 zhongwei 聊聊。 白天工地搬砖,晚上哄熊孩子,可能回复有点慢,见谅。 查看更多联系方式

相关文章

爱评论不评论

近期节日

2020年02月24日 第三世界青年日
2020年02月24日 龙抬头
2020年02月28日 世界居住条件调查日
2020年03月01日 国际海豹日
2020年03月03日 全国爱耳日
2020年03月05日 学雷锋日
2020年03月05日 惊蛰
2020年03月08日 三八妇女节
2020年03月12日 植树节
2020年03月14日 白色情人节
2020年03月15日 消费者权益日
2020年03月17日 国际航海日
查看更多节日