SQL Reference - STRAIGHT

STRAIGHT_JOIN

STRAIGHT_JOIN forces the MySQL server to join the tables in the order in which they appear in the FROM clause, overriding the default behavior of the MySQL optimizer to determine the most efficient join order based on table size, indexes, and other factors. This becomes useful when the optimizer's choice of table join order is not optimal for a particular query.

Example

SELECT STRAIGHT_JOIN orders.order_id, products.product_name
FROM orders
STRAIGHT_JOIN products
ON orders.product_id = products.product_id
LIMIT 10;

Output

| order_id | product_name   |
|----------|----------------|
| 1        | Laptop         |
| 2        | Mobile Phone   |
| 3        | Television     |
| 4        | Refrigerator   |
| 5        | Washing Machine|
| 6        | Oven           |
| 7        | Microwave      |
| 8        | Blender        |
| 9        | Toaster        |
| 10       | Fridge         |

Explanation

The STRAIGHT_JOIN keyword in MySQL enforces the table join order based on how the tables appear in the query. In this example, we join ‘orders’ and ‘products’ tables based on the product_id. The join sequence begins with the ‘orders’ table, matching records from the ‘products’ table. This shows the first 10 records of order_id and corresponding product_name.