MySQL: left join statement Optimization

The left join keyword returns all rows from the left table (table_name1), even if no matching row exists in the right table (table_name2.

If at least one match exists in the table, the inner join keyword returns the row.

Table:

A1 and A2 Fields

Table B:

B1 and B2 Fields

Table C:

C1 and C2 Fields


Assume that the following left join queries exist:

Select * from a left join B on B. b2 = A. A1 where B. b1 = 1;

The following conditions are met: The where condition contains the field (B. B1) in table B, and the field is not null.

Optimized:
Select * from a inner join B on B. b2 = A. A1 where B. b1 = 1;

Equivalent to: Select * from a, B where B. b2 = A. A1 and B. b1 = 1;

Three tables left join:

Select * from a left join B on B. b2 = A. A1 left join C on C. C1 = B. B2 where c. C1 = 1;

The following conditions are met: The where condition contains a field (C. C1) in table C, and the field is not null.

Optimized:
Select * from a left join B on B. b2 = A. A1 inner join C on C. C1 = B. B2 where c. C1 = 1;
Equivalent: Select * from a left join B on B. b2 = A. A1, c Where C. C1 = B. B2 and C. C1 = 1;

If B2 is found in the WHERE clause, optimize it again:
Select * from a inner join B on B. b2 = A. A1, c Where C. C1 = B. B2 and C. C1 = 1;

Equivalent to: Select * from a, B, c Where B. b2 = A. A1 and C. C1 = B. B2 and C. C1 = 1;