10.6 How MySQL optimizes LEFT JOIN

A LEFT JOIN B is in MySQL implemented as follows

• The table B is set to be dependent on table A.
• The table A is set to be dependent on all tables (except B) that are used in the LEFT JOIN condition.
• All LEFT JOIN conditions are moved to the WHERE clause.
• All standard join optimizations are done, with the exception that a table is always read after all tables it is dependent on. If there is a circular dependence then MySQL will issue an error.
• All standard WHERE optimzations are done.
• If there is a row in A that matches the WHERE clause, but there wasn't any row in B that matched the LEFT JOIN condition, then an extra B row is generated with all columns set to NULL.
• If you use LEFT JOIN to find rows that doesn't exist in some table and you have the following test: column_name IS NULL in the WHERE part, where column_name is a column that is declared as NOT NULL, then MySQL will stop searching after more rows (for a particular key combination) after it has found one row that matches the LEFT JOIN condition.