INNER JOIN vs Correlated Subquery in SQL: What Is Actually Faster?

Question

Most SQL developers hear the same rule early: always prefer INNER JOIN over correlated subqueries, because subqueries create an N+1 performance problem.

That rule is useful, but it is not always true.

I tested this on four engines (MySQL, Oracle, PostgreSQL, SQLite) and got mixed results. In some cases, the correlated subquery was faster than the join query.

If you want to validate the same patterns yourself, you can run these examples in live sandboxes and practice similar SQL tasks on sqltest.online.

What You Will Learn

• what INNER JOIN does best;
• when a correlated subquery can be competitive;
• why execution plans matter more than "golden rules";
• how to benchmark both approaches correctly.

What Is INNER JOIN in SQL?

INNER JOIN returns only rows that match in both tables.

Why it matters: it is usually the cleanest set-based way to combine data, and often gives the optimizer strong options.

SELECT
    c.customer_id,
    c.name,
    o.order_id
FROM customers c
INNER JOIN orders o
    ON c.customer_id = o.customer_id;

In many workloads, this is exactly what you want. But performance still depends on table sizes, indexes, and optimizer decisions.

Benchmark Scenario

I used a small outer table and a larger inner table:

• customers: 25 rows
• orders: 1000 rows
• index on orders(customer_id)

Task: return each customer with the number of their orders.

Two Query Patterns

Pattern A: JOIN + GROUP BY

SELECT
    c.customer_id,
    COUNT(o.order_id) AS orders_count
FROM customers c
LEFT JOIN orders o
    ON c.customer_id = o.customer_id
GROUP BY c.customer_id;

Note: I used LEFT JOIN here to keep customers with zero orders. If you only need matched rows, use INNER JOIN.

Pattern B: Correlated Subquery

SELECT
    c.customer_id,
    (
        SELECT COUNT(o.order_id)
        FROM orders o
        WHERE o.customer_id = c.customer_id
    ) AS orders_count
FROM customers c;

The second form looks like classic N+1, but an index can make each lookup very cheap.

How to Inspect the Real Cost

Use execution plans, not assumptions:

• MySQL / PostgreSQL: EXPLAIN ANALYZE
• SQLite: EXPLAIN QUERY PLAN
• Oracle: EXPLAIN PLAN FOR ... + DBMS_XPLAN.DISPLAY

Results Summary Across Engines

MySQL 8 and Oracle 23c

In my tests, the correlated subquery often won.

Why: with a small outer table (25 customers), the optimizer could do very fast indexed lookups on orders(customer_id) per customer. That was cheaper than building larger join/group intermediate work.

PostgreSQL 16

JOIN + GROUP BY won in my test.

Why: PostgreSQL produced an efficient set-based plan and handled the join/aggregate path very well.

SQLite

Practical tie.

Both variants ended with very similar plans and near-identical timing on this dataset.

Why the INNER JOIN Rule Sometimes Fails

The "always use INNER JOIN" advice fails when it ignores three variables:

1. Database engine optimizer behavior
2. Data distribution and table size ratio
3. Index availability and quality

SQL performance is not about syntax preference. It is about the physical plan your engine chooses.

Common Mistakes

• assuming INNER JOIN is always faster without checking plans;
• forgetting that INNER JOIN removes non-matching rows;
• testing once without warm-up runs;
• benchmarking without a supporting index;
• comparing query text instead of comparing actual execution plans.

Practical Checklist

Before choosing INNER JOIN or correlated subquery:

1. Add the right index (here: orders(customer_id)).
2. Run both query variants.
3. Capture execution plans.
4. Compare timing over several runs.
5. Pick the query that is both correct and fast for your engine.

Final Takeaway

INNER JOIN is a strong default and often the right choice, but it is not a universal performance guarantee.

The best query is the one that produces the best execution plan on your actual database, with your actual data.