Tag: SQL

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.