SQL STRAIGHT_JOIN

What is SQL STRAIGHT_JOIN?

Forces the MySQL optimizer to join tables in the exact order they are listed in the query.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL STRAIGHT_JOIN:

SQL STRAIGHT_JOIN Full Explanation

STRAIGHT_JOIN is a MySQL specific join operator that overrides the optimizer's default behavior of reordering tables for the lowest estimated cost. By using STRAIGHT_JOIN, you guarantee that the left table is read first, the right table second, and so on for chained joins. This can improve performance when the optimizer misjudges statistics, or when query planners change across MySQL versions. STRAIGHT_JOIN behaves like an INNER JOIN in terms of returned rows; the only difference is the enforced join sequence. Because it bypasses cost-based decisions, use it only after measuring that the forced order is faster or produces the desired execution plan. STRAIGHT_JOIN can appear between two tables or be applied globally by adding the SQL_BIG_RESULT or SQL_SMALL_RESULT options in combination with SELECT STRAIGHT_JOIN (deprecated in recent versions). In modern MySQL, writing SELECT STRAIGHT_JOIN ... is still allowed but the preferred approach is the explicit table1 STRAIGHT_JOIN table2 syntax.

SQL STRAIGHT_JOIN Syntax

SELECT column_list
FROM table1 STRAIGHT_JOIN table2
  ON table1.id = table2.t1_id;

SQL STRAIGHT_JOIN Parameters

Example Queries Using SQL STRAIGHT_JOIN

-- Force join order where large fact table comes first
SELECT o.order_id, c.customer_name
FROM orders STRAIGHT_JOIN customers
  ON orders.customer_id = customers.id
WHERE orders.order_date >= '2024-01-01';

-- Chain of STRAIGHT_JOINs
SELECT p.product_name, o.quantity, c.customer_name
FROM products p
  STRAIGHT_JOIN order_items oi ON p.id = oi.product_id
  STRAIGHT_JOIN orders o       ON oi.order_id = o.id
  STRAIGHT_JOIN customers c    ON o.customer_id = c.id
WHERE p.category = 'Snacks';

Expected Output Using SQL STRAIGHT_JOIN

MySQL executes the joins strictly in the order written, returning only rows that satisfy all ON conditions (same result set as INNER JOIN but potentially different execution time)

Use Cases with SQL STRAIGHT_JOIN

Stabilizing execution plans after a MySQL upgrade
Forcing a selective filter early in the plan when the optimizer chooses otherwise
Debugging performance issues by comparing forced vs optimizer chosen plans
Preventing nested loop explosions on large tables with poor statistics

Common Mistakes with SQL STRAIGHT_JOIN

Assuming STRAIGHT_JOIN changes result set semantics; it only affects plan order
Applying it without measuring, which can slow queries if the optimizer's choice was better
Forgetting indexes on join columns; STRAIGHT_JOIN does not compensate for missing indexes
Mixing with OUTER JOINs expecting the same enforcement (STRAIGHT_JOIN acts like INNER JOIN)

First Introduced In

MySQL 3.23

Frequently Asked Questions

What is the difference between STRAIGHT_JOIN and INNER JOIN?

STRAIGHT_JOIN returns the same rows as INNER JOIN but enforces the written join order. INNER JOIN lets the optimizer reorder tables.

Can STRAIGHT_JOIN slow down my query?

Yes. If the optimizer's chosen plan is better than the forced order, performance can drop. Test both versions before committing.

How do I remove STRAIGHT_JOIN if no longer needed?

Simply replace STRAIGHT_JOIN with INNER JOIN or comma separated joins, then test the execution plan.

Is STRAIGHT_JOIN deprecated?

The table1 STRAIGHT_JOIN table2 syntax is fully supported. The older SELECT STRAIGHT_JOIN style is deprecated but still works in recent MySQL versions.