MySQL – How to do Straight Join in MySQL?

In an earlier post, we have learnt a new type of join called NATURAL JOIN supported in MySQL. In this post will see another type of join called STRAIGHT_JOIN.

STRAIGHT_JOIN is actually an INNER JOIN but it forces the order in which data should be processed.

As per MySQL’s documentation at, the definition is given below

“STRAIGHT_JOIN is similar to JOIN, except that the left table is always read before the right table. This can be used for those (few) cases for which the join optimizer puts the tables in the wrong order.”

Let us create the following data

CREATE TABLE items(item_id INT, item_description VARCHAR(100));
CREATE TABLE sales(sales_id INT auto_increment KEY,item_id INT, sales_date DATETIME, sales_amount DECIMAL(12,2));
INSERT INTO items VALUES (1,'Television');
INSERT INTO items VALUES (2,'Mobile');
INSERT INTO items VALUES (3,'laptop');
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (1,'2014-01-01',1200);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (2,'2014-01-02',200);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (3,'2014-01-09',1700);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (3,'2014-01-29',1700);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (3,'2014-02-11',1700);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (1,'2014-02-16',1200);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (2,'2014-02-16',200);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (2,'2014-02-20',200);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (2,'2014-02-20',200);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (2,'2014-02-22',200);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (3,'2014-02-24',1700);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (1,'2014-02-24',1200);


STRAIGHT_JOIN can be used in two methods

Method 1 Join tables using STRAIGHT_JOIN

SELECT t1.item_description,t2.sales_id,t2.sales_date,t2.sales_amount
FROM sales AS t2 STRAIGHT_JOIN items AS t1 ON t1.item_id=t2.item_id;


Method 2 Use INNER JOIN to join tables and use STRAIGHT_JOIN in the SELECT clause itself

SELECT STRAIGHT_JOIN t1.item_description,t2.sales_id,t2.sales_date,t2.sales_amount
FROM sales AS t2 INNER JOIN items AS t1 ON t1.item_id=t2.item_id;


Both the queries produce the following result

item_description sales_id sales_date sales_amount
 Television 1 1/1/2014 0:00 1200
 Mobile 2 1/2/2014 0:00 200
 laptop 3 1/9/2014 0:00 1700
 laptop 4 1/29/2014 0:00 1700
 laptop 5 2/11/2014 0:00 1700
 Television 6 2/16/2014 0:00 1200
 Mobile 7 2/16/2014 0:00 200
 Mobile 8 2/20/2014 0:00 200
 Mobile 9 2/20/2014 0:00 200
 Mobile 10 2/22/2014 0:00 200
 laptop 11 2/24/2014 0:00 1700
 Television 12 2/24/2014 0:00 1200

However the real test is when we execute the STRAIGHT_JOIN syntax with EXPLAIN command before SELECT statement and observe the order of table joined.

Method STRAIGHT JOIN

EXPLAIN SELECT STRAIGHT_JOIN t1.item_description,t2.sales_id,t2.sales_date,t2.sales_amount
FROM sales AS t2 INNER JOIN items AS t1 ON t1.item_id=t2.item_id;

Now let us see the result where we observe the order of tables used in Join.

You will notice that Sales Table which is aliased as t2 is first used in the result set.

Method INNER JOIN (without Straight Join)

EXPLAIN SELECT t1.item_description,t2.sales_id,t2.sales_date,t2.sales_amount
FROM sales AS t2 INNER JOIN items AS t1 ON t1.item_id=t2.item_id;

Now let us see the result where we observe the order of tables used in Join.

You will notice that Sales Table which is aliased as t2 is second used in the result set. That means MySQL have used internal optimizer and re-ordered the table order for maximum performance.

Reference: Pinal Dave (https://blog.sqlauthority.com)