mysql outer inner_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 编程 - 逻辑查询处理)

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

t1 与 t2 执行笛卡尔集，产生虚拟表 VT1

对 VT1 应用 ON 条件过滤，符合条件的插入虚拟表 VT2

inner join 不参与此步逻辑，当为 outer left/right join 时，将保留表(left/right 表)中未匹配的行做为外部行添加到 VT2, 形成虚拟表 VT3.

对 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