left-join-optimization

This page has moved or been replaced. The new page is located here:

http://dev.mysql.com/doc/refman/5.5/en/outer-join-optimization.html

Please update any bookmarks that point to the old page.


User Comments
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
  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:
http://www.mysqldiary.com/mysql-left-join/

  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 join...it 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 A.id=B.marks"
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 A.id=B.id"
Result is :
id Name Marks
1 Ram 400
2 Shyam Null
it list all records from table student independent of table B