From LEFT JOIN and RIGHT JOIN Optimization

The join optimizer calculates the order in which tables should be joined.

 

MySQL implements an A LEFT JOIN B join_condition as follows:

  • Table B is set to depend on table A and all tables on which A depends.
  • Table A is set to depend on all tables (except B) that are used in the LEFT JOIN condition.
  • The LEFT JOIN condition is used to decide how to retrieve rows from table B. (In other words, any condition in the WHERE clause is not used.)
  • All standard join optimizations are performed, with the exception that a table is always read after all tables on which it depends. If there is a circular dependence, MySQL issues an error.
  • All standard WHERE optimizations are performed.
  • If there is a row in A that matches the WHERE clause, but there is no row in B that matches the ON condition, an extra B row is generated with all columns set to NULL.