MySQL STRAIGHT JOIN Tutorial & Examples

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.

.

Straight Join

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.

Left table?

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

More Information

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.

This entry was posted in Join Tutorials and tagged , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>