mysql left join快速转inner join的过程_Mysql

Mysql

MsSql Mysql mariadb oracle DB2 mssql2008 mssql2005 SQLite PostgreSQL MongoDB Redis Access

上一篇: 解决Mysql的left join无效及使用... 下一篇:MySQL 十大常用字符串函数详解

mysql left join快速转inner join的过程

发布时间：2022-04-18 发布网站：脚本宝典

脚本宝典收集整理的这篇文章主要介绍了mysql left join快速转inner join的过程，脚本宝典觉得挺不错的，现在分享给大家，也给大家做个参考。

在日常优化过程中，发现一个怪事情，同一个SQL出现两个完全不一样执行计划，left join 连驱动表都可以变成不一样。

对于left join，如果where条件里有被关联表过滤，left join有可能被转成inner join ,本案例中shopInfo有ShopCategory = 'LOC'过滤条件；保证shopInfo的记录非NULL，因此left join在优化过程中可以转为inner join。那么O和S的JOIN顺序就是可以交换的。

验证结论：

创建表：

--班级表
CREATE TABLE T_CLASS(
  class_id int not null,
  class_name VArchAR2(100)
);
添加索引
ALTER TABLE T_CLASS add index inx_class_id(class_id);

--学生表
CREATE TABLE T_STUDENT(
  student_id int not null,
  class_id int not null,
  student_name VARCHAR(100),
  age int,
  sex int 
)
添加索引
alter table T_STUDENT add index index_age(AGE);

--班级数据
insert into T_CLASS (CLASS_ID, CLASS_NAME)
values (1, '一班');

insert into T_CLASS (CLASS_ID, CLASS_NAME)
values (2, '二班');

insert into T_CLASS (CLASS_ID, CLASS_NAME)
values (3, '三班');

insert into T_CLASS (CLASS_ID, CLASS_NAME)
values (4, '四班');

insert into T_CLASS (CLASS_ID, CLASS_NAME)
values (5, '五班');

--学生数据
insert into T_STUDENT (STUDENT_ID, CLASS_ID, STUDENT_NAME, AGE, SEX)
values (1, 1, '李1', 3, '1');

insert into T_STUDENT (STUDENT_ID, CLASS_ID, STUDENT_NAME, AGE, SEX)
values (2, 1, '李2', 2, '1');

insert into T_STUDENT (STUDENT_ID, CLASS_ID, STUDENT_NAME, AGE, SEX)
values (3, 1, '李3', 3, '1');

insert into T_STUDENT (STUDENT_ID, CLASS_ID, STUDENT_NAME, AGE, SEX)
values (4, 2, '李4', 4, '1');

insert into T_STUDENT (STUDENT_ID, CLASS_ID, STUDENT_NAME, AGE, SEX)
values (5, 2, '李5', 3, '2');

insert into T_STUDENT (STUDENT_ID, CLASS_ID, STUDENT_NAME, AGE, SEX)
values (6, 2, '李6', 3, '1');

insert into T_STUDENT (STUDENT_ID, CLASS_ID, STUDENT_NAME, AGE, SEX)
values (7, 3, '李7', 6, '2');

insert into T_STUDENT (STUDENT_ID, CLASS_ID, STUDENT_NAME, AGE, SEX)
values (8, 3, '李8', 4, '2');

insert into T_STUDENT (STUDENT_ID, CLASS_ID, STUDENT_NAME, AGE, SEX)
values (9, 2, '李9', 2, '2');

insert into T_STUDENT (STUDENT_ID, CLASS_ID, STUDENT_NAME, AGE, SEX)
values (10, 2, '李10', 3, '1');

insert into T_STUDENT (STUDENT_ID, CLASS_ID, STUDENT_NAME, AGE, SEX)
values (11, 3, '李11', 3, '2');

insert into T_STUDENT (STUDENT_ID, CLASS_ID, STUDENT_NAME, AGE, SEX)
values (12, 2, '李12', 8, '2');

insert into T_STUDENT (STUDENT_ID, CLASS_ID, STUDENT_NAME, AGE, SEX)
values (13, 1, '李13', 6, '2');

案例1：B表有where条件且不为null