This tutorial shows you how to write straight joins in MySQL and when an it makes sense to do so. Included are a general description, syntax examples and a comparison of straight and inner joins.
A STRAIGHT_JOIN identifies and combines matching rows which are stored in two related tables. This is what an inner join also does. The difference between an inner join and a straight join is that a straight join forces MySQL to read the left table first.
To see if the STRAIGHT_JOIN hint would make sense, you have to use EXPLAIN to analyze your queries. When you feel that another join order could improve the performance, use a straight join.
The STRAIGHT_JOIN keyword or hint, forces MySQL to read the left table first. And what is the left table exactly? When you join two tables, you always have a left and a right table:
- left table is listed on the left side of the STRAIGHT_JOIN keyword
- right table is listed on the right side of the STRAIGHT_JOIN keyword
SELECT * FROM leftTable l STRAIGHT_JOIN rightTable r ON l.id = r.id
For more information on the STRAIGHT JOIN syntax which is equivalent to the syntax of inner joins, please see our description of MySQL inner joins. However, keep in mind that the MySQL join optimizer does a good job most of the time.