Why MySQL Optimizer Converts LEFT JOIN to INNER JOIN When Filtering on the Joined Table

During routine query optimization, the author observed that the same SQL statement produced two completely different execution plans, with a LEFT JOIN sometimes being transformed into an INNER JOIN.

When a WHERE condition filters the joined (right) table, MySQL can convert the LEFT JOIN to an INNER JOIN because the filtered rows are guaranteed to be non‑NULL; thus the outer join semantics become unnecessary and the join order can be swapped.

To verify this behavior, the following tables and data were created.

--班级表 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');

Three test cases were executed:

Case 1 – Only the B table (shopInfo) has a non‑NULL WHERE filter, causing the LEFT JOIN to be rewritten as an INNER JOIN.

Case 2 – Both A and B tables have non‑NULL WHERE filters, still resulting in the optimizer converting the LEFT JOIN to an INNER JOIN.

Case 3 – Both tables have filters, but the index on the B table is removed; the optimizer still prefers the INNER JOIN transformation.

Conclusion: MySQL’s optimizer will convert a LEFT JOIN to an INNER JOIN only when the joined (right) table has a WHERE condition that filters out rows and the filter is more selective than the join condition, ensuring that the outer‑join semantics are unnecessary.