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

更新于 2017-10-09 | 浏览: 10184
文章目录

    例如，商品表与类型表，要查询指定类型名的商品，使用 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 聊聊，或者关注我的个人公众号“大象工具”， 查看更多联系方式

    标签: MySQL

    相关推荐