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

例如，商品表与类型表，要查询指定类型名的商品，使用 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 聊聊， 查看更多联系方式