MySQL 5.0 Reference Manual  /  ...  /  LEFT JOIN and RIGHT JOIN Optimization LEFT JOIN and RIGHT JOIN Optimization

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.

  • If you use LEFT JOIN to find rows that do not exist in some table and you have the following test: col_name IS NULL in the WHERE part, where col_name is a column that is declared as NOT NULL, MySQL stops searching for more rows (for a particular key combination) after it has found one row that matches the LEFT JOIN condition.

The implementation of RIGHT JOIN is analogous to that of LEFT JOIN with the roles of the tables reversed.

The join optimizer calculates the order in which tables should be joined. The table read order forced by LEFT JOIN or STRAIGHT_JOIN helps the join optimizer do its work much more quickly, because there are fewer table permutations to check. Note that this means that if you do a query of the following type, MySQL does a full scan on b because the LEFT JOIN forces it to be read before d:

  FROM a JOIN b LEFT JOIN c ON (c.key=a.key)
  LEFT JOIN d ON (d.key=a.key)
  WHERE b.key=d.key;

The fix in this case is reverse the order in which a and b are listed in the FROM clause:

  FROM b JOIN a LEFT JOIN c ON (c.key=a.key)
  LEFT JOIN d ON (d.key=a.key)
  WHERE b.key=d.key;

For a LEFT JOIN, if the WHERE condition is always false for the generated NULL row, the LEFT JOIN is changed to a normal join. For example, the WHERE clause would be false in the following query if t2.column1 were NULL:

SELECT * FROM t1 LEFT JOIN t2 ON (column1) WHERE t2.column2=5;

Therefore, it is safe to convert the query to a normal join:

SELECT * FROM t1, t2 WHERE t2.column2=5 AND t1.column1=t2.column1;

This can be made faster because MySQL can use table t2 before table t1 if doing so would result in a better query plan. To provide a hint about the table join order, use STRAIGHT_JOIN. (See Section 13.2.8, “SELECT Syntax”.)

Download this Manual
User Comments
  Posted by Peter Warnock on January 12, 2005
When using more than one left join, the first occurrence of NULL will cause latter fields to be reported NULL with the USING(field) clause.

instead of :

t1 LEFT JOIN t2 ON ( = LEFT JOIN t3 ON (

  Posted by Mark Malakanov on May 9, 2006
Outer joins can work inefficiently when joining columns are in different character sets.

In my case query worked very slow when "the outer" table had utf8 joining columns, and a second table had latin1 ones.
Though, query worked fast when the outer table had latin1 joining columns, and a second table had utf8 ones.

Join was efficient in any direction when columns that tables outer joined were the same character set, ether both latin1, or both utf8.

  Posted by Ilan Hazan on July 6, 2011
It is a common mistake to think that there is no difference putting the conditions in the ON clause and the WHERE clause.
well, there is a big difference. The following post explains it with examples:

  Posted by Nicolas LESCURE on December 6, 2011
got the same pb as Mark Malakanov and the same solution - extremely slow query during a left outer happens that the join was done on one column defined with CHARACTER SET utf8 COLLATE utf8_bin and the other one without character set specification ( even if by default it was utf-8 ) - changing column definition solved the problem
  Posted by Amit Thakur on July 1, 2013
Left Join - Left Join is used to retrieve data from one table independent from the availability of data in another table.
Ex - We have a table student(id, name) values (1, 'Ram'),( 2, 'Shyam');
another table marks(id,marks,semester) values(1,400,'First');
We have to list all students from table student and marks of those student which are in marks table
If we write a query without join - "select A.*,B.marks from student as A, marks as B where"
Result is - 1,Ram,400 it give only one student which is available in both table.
If we have to list all student from the student table then we should use join. our query will be -
"select A.*, B.marks from student as A left join marks as B on"
Result is :
id Name Marks
1 Ram 400
2 Shyam Null
it list all records from table student independent of table B
Sign Up Login You must be logged in to post a comment.