5.2.7 How MySQL Optimises LEFT JOIN and RIGHT JOIN

A LEFT JOIN B join_condition in MySQL is implemented as follows:

• The table B is set to be dependent on table A and all tables that A is dependent on.
• The table A is set to be dependent on all tables (except B) that are used in the LEFT JOIN condition.
• The LEFT JOIN condition is used to decide how we should retrieve rows from table B. (In other words, any condition in the WHERE clause is not used).
• All standard join optimisations 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 optimisations 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 ON condition, then an extra B row is generated with all columns set to NULL.
• If you use LEFT JOIN to find rows that don'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.

RIGHT JOIN is implemented analogously as LEFT JOIN.

The table read order forced by LEFT JOIN and STRAIGHT JOIN will help the join optimiser (which calculates in which order tables should be joined) to do its work much more quickly, as there are fewer table permutations to check.

Note that the above means that if you do a query of type:

SELECT * FROM a,b LEFT JOIN c ON (c.key=a.key) LEFT JOIN d (d.key=a.key)
         WHERE b.key=d.key

MySQL will do a full scan on b as the LEFT JOIN will force it to be read before d.

The fix in this case is to change the query to:

SELECT * FROM b,a LEFT JOIN c ON (c.key=a.key) LEFT JOIN d (d.key=a.key)
         WHERE b.key=d.key

Starting from 4.0.14 MySQL does the following left join optimization:

If the WHERE condition is always be false for the generated NULL row, the LEFT JOIN is changed to a normal join.

For example, in the following query the WHERE clause would be false if t2.column would be NULL so it's safe to convert to a normal join.

SELECT * FROM t1 LEFT t2 ON (column) WHERE t2.column2 =5;
->
SELECT * FROM t1,t2 WHERE t2.column2=5 AND t1.column=t2.column;

this can be made faster as MySQL can now use table t2 before table t1 if this would result in a better query plan. To force a specific table order one should use STRAIGHT JOIN.

User Comments

This gave me trouble for a while so I thought I'd
pass it on. If you want a left join with a
condition for the right table (the row that may
or may not be there) you have to put the
condition in the join clause, not the where
clause.

Here's an example:
SELECT
*
FROM
t1 LEFT JOIN t2 ON
(t1.id_t1 = t2.id_t1 AND t2.id_t2 = 345)
WHERE
t1.id_level=17;

I appologize if that's common knowledge but this
note would have saved me hours.
Thanks

Add your own comment.