Rewrite MySQL Subqueries as JOINs for Performance

In MySQL databases, subqueries can often lead to performance bottlenecks because the query optimizer may execute them as nested loops or generate inefficient temporary tables. Rewriting these subqueries as JOIN operations is a highly effective optimization technique that allows the MySQL engine to utilize indexes better and execute queries more efficiently. This article provides a straightforward guide on how to identify subqueries suitable for conversion and the step-by-step process of rewriting them into JOIN statements to boost database performance.

Why Rewrite Subqueries as JOINs?

While subqueries are highly readable and easy to write, older versions of MySQL (and even modern versions under certain conditions) struggle to optimize them. MySQL often evaluates subqueries for every single row processed in the outer query, leading to an \(O(N^2)\) execution time.

By contrast, JOIN operations allow the MySQL query optimizer to create a better execution plan. The optimizer can choose the best join order, use indexes more effectively, and avoid the creation of internal temporary tables, resulting in significantly faster execution times.

Scenario 1: Rewriting IN Subqueries as INNER JOINs

One of the most common subquery patterns involves filtering records using the IN operator.

The Subquery Approach

This query finds all employees who work in departments located in ‘New York’:

SELECT name 
FROM employees 
WHERE department_id IN (
    SELECT id 
    FROM departments 
    WHERE location = 'New York'
);

The JOIN Rewrite

To rewrite this, turn the subquery table into an INNER JOIN and move the subquery’s filtering condition to the ON clause or a WHERE clause:

SELECT e.name 
FROM employees e
INNER JOIN departments d 
    ON e.department_id = d.id
WHERE d.location = 'New York';

Why it performs better: The INNER JOIN allows MySQL to look up matching department IDs using indexes on departments.id and employees.department_id in a single pass, rather than evaluating the IN list for every row.

Scenario 2: Rewriting NOT IN or NOT EXISTS as LEFT JOINs

Checking for the absence of records using NOT IN or NOT EXISTS can be particularly slow in MySQL because it often requires a full table scan.

The Subquery Approach

This query finds all customers who have never placed an order:

SELECT customer_name 
FROM customers 
WHERE id NOT IN (
    SELECT customer_id 
    FROM orders
);

The JOIN Rewrite (The Exclusion Join)

You can rewrite this as a LEFT JOIN combined with a WHERE ... IS NULL check. This is known as an “exclusion join” or “antijoin”:

SELECT c.customer_name 
FROM customers c
LEFT JOIN orders o 
    ON c.id = o.customer_id
WHERE o.customer_id IS NULL;

Why it performs better: The LEFT JOIN attempts to match all customers with orders. If a customer has no orders, the columns from the orders table will return NULL. Filtering for o.customer_id IS NULL instantly targets customers without orders. MySQL can optimize this using index lookups on orders.customer_id and stop searching as soon as it finds a match.

Scenario 3: Rewriting Correlated Subqueries

Correlated subqueries refer to columns from the outer query. This forces MySQL to execute the subquery once for every row in the outer table.

The Subquery Approach

This query retrieves the most recent order date for every customer:

SELECT c.customer_name, (
    SELECT MAX(o.order_date) 
    FROM orders o 
    WHERE o.customer_id = c.id
) AS latest_order
FROM customers c;

The JOIN Rewrite

You can rewrite this by creating a derived table (a subquery in the FROM clause) that aggregates the data first, and then joining it to the main table:

SELECT c.customer_name, o.latest_order
FROM customers c
LEFT JOIN (
    SELECT customer_id, MAX(order_date) AS latest_order
    FROM orders
    GROUP BY customer_id
) o ON c.id = o.customer_id;

Why it performs better: Instead of querying the orders table repeatedly for each customer, MySQL aggregates the orders table once and performs a single, highly efficient join.

Key Rules for Converting Queries