MySQL Subquery vs JOIN: Which Is Better and When to Use Each

Many questions can be answered with a subquery or with a join. Which should you use?

query.sql

SQL

CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    city VARCHAR(50) NOT NULL
);

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    ordered_on DATE NOT NULL,
    total DECIMAL(10,2) NOT NULL,
    status VARCHAR(20) NOT NULL
);

INSERT INTO customers (name, city) VALUES
    ('Maya Shah',    'Pune'),
    ('Rohan Verma',  'Delhi'),
    ('Sana Ali',     'Pune'),
    ('Tara Nair',    'Kochi'),
    ('Uday Singh',   'Delhi');

INSERT INTO orders (customer_id, ordered_on, total, status) VALUES
    (1, '2026-05-02',  680.00, 'delivered'),
    (1, '2026-05-20', 1299.00, 'delivered'),
    (2, '2026-05-21',  145.00, 'shipped'),
    (3, '2026-06-01',  450.00, 'delivered'),
    (3, '2026-06-05',   80.00, 'cancelled'),
    (3, '2026-06-18',  999.00, 'shipped'),
    (2, '2026-06-20',  220.00, 'pending');

Same question, two forms

“Customers who have placed an order over 1000.”

query.sql

SQL

1
2
3

SELECT c.name
FROM customers c
WHERE c.id IN (SELECT o.customer_id FROM orders o WHERE o.total > 1000);

Output

OUTPUT

+-----------+
| name      |
+-----------+
| Maya Shah |
+-----------+
1 row in set

query.sql

SQL

SELECT DISTINCT c.name
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.total > 1000;

Output

OUTPUT

+-----------+
| name      |
+-----------+
| Maya Shah |
+-----------+
1 row in set

The join needs DISTINCT, because a customer with two orders over 1000 would otherwise appear twice. The subquery form returns each customer once, without extra work. That is the first difference.

Rules of thumb

Situation	Prefer
You only test whether related rows exist	`EXISTS` / `IN` subquery (no duplicates)
You need columns from the other table	`JOIN`
You need the absence of a related row	`NOT EXISTS` or `LEFT JOIN ... IS NULL`
You compare with an aggregate (average, max)	A scalar subquery, or a derived table joined back
You need several values from the related row	`JOIN`
The relationship is one-to-many and you want one row per parent	Subquery, or `JOIN` + `GROUP BY`

Subqueries that cannot be joins

A scalar subquery in the SELECT list, returning an aggregate for the current row.
NOT EXISTS for “no related row” (though LEFT JOIN ... IS NULL is an alternative).
Comparison with an aggregate over the whole table.

Joins that cannot be subqueries

When you need to show columns from both tables.
Outer joins that keep unmatched rows with NULLs.

Performance in MySQL 8

Old advice said “joins are faster than subqueries”. That was true in MySQL 5.5, which ran many subqueries badly. MySQL 5.6 and later optimise IN and EXISTS subqueries into semi-joins, and 8.0.17 added anti-joins for NOT EXISTS and NOT IN. For the usual shapes, the plans are the same, or very similar. Check with EXPLAIN:

query.sql

SQL

EXPLAIN FORMAT=TRADITIONAL SELECT c.name FROM customers c WHERE c.id IN (SELECT o.customer_id FROM orders o WHERE o.total > 1000);

Output

OUTPUT

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                                     |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------------+
|  1 | SIMPLE      | c     | NULL       | ALL  | PRIMARY       | NULL | NULL    | NULL |    5 |   100.00 | NULL                                                      |
|  1 | SIMPLE      | o     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    7 |    14.29 | Using where; FirstMatch(c); Using join buffer (hash join) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------------+
2 rows in set

When both rows say SIMPLE, MySQL has already turned the subquery into a join.

What can be slow:

Correlated scalar subqueries in the select list over big tables: one execution per row.
IN with a very large list produced by an unindexed subquery.
Subqueries that include LIMIT, GROUP BY or UNION (they must be materialised).

Counting: join vs subquery

“Customers and their number of orders”:

query.sql

SQL

1
2
3

SELECT c.name, (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) AS orders
FROM customers c
ORDER BY c.id;

Output

OUTPUT

+-------------+--------+
| name        | orders |
+-------------+--------+
| Maya Shah   |      2 |
| Rohan Verma |      2 |
| Sana Ali    |      3 |
| Tara Nair   |      0 |
| Uday Singh  |      0 |
+-------------+--------+
5 rows in set

query.sql

SQL

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

Output

OUTPUT

+-------------+--------+
| name        | orders |
+-------------+--------+
| Maya Shah   |      2 |
| Rohan Verma |      2 |
| Sana Ali    |      3 |
| Tara Nair   |      0 |
| Uday Singh  |      0 |
+-------------+--------+
5 rows in set

Same results. The correlated subquery is easy to read. The join with GROUP BY is usually better when the query needs more than one aggregate (a count and a sum and an average), because it reads the orders once.

Readability

Choose the form that states the question best:

“Customers for whom there exists an order over 1000″: EXISTS.
“Orders with their customer’s name”: JOIN.
“Products above the average“: scalar subquery.

If a query grows complicated, use CTEs to name the parts (CTEs).

Conclusion

There is no universal winner in MySQL 8. Write the clearest version, add the right indexes (orders.customer_id) and confirm with EXPLAIN. Do not rewrite for speed without measuring.

lightbulb

Test with data

A rewrite that looks equivalent may differ with NULLs and duplicates. Compare the results of both forms on real-sized data before replacing one with the other.