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

更新日期: 2017-10-09 阅读次数: 8834 分类: 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 聊聊, 查看更多联系方式