Is JOIN Faster Than Correlated Subqueries? Taking a Look and Subsequently Debunking the Myth

Hey there, fellow developers! If you've ever dabbled in SQL, you've probably heard the golden rule: "Never use correlated subqueries in SELECT—they're a recipe for N+1 disasters!" Instead, we're told to always opt for JOINs because they're set-based, efficient, and lightning-fast.

\ But is this rule set in stone? I decided to put it to the test across four popular database systems: MySQL 8.0, Oracle 23c, PostgreSQL 16, and SQLite 3.45. Spoiler alert: The results were eye-opening. Sometimes, the "bad" correlated subquery outperformed the "good" JOIN. Let's dive in and see why.

The Test Setup: Customers and Orders

To keep things fair, I used a simple schema with two tables:

Customers: A small table with 25 rows of customer data.

Orders: A larger table with 1,000 rows of orders, linked via a foreign key. The goal? Count the number of orders per customer, including those with zero orders.

\ Here's the schema (using MySQL syntax for reference):

-- Table of customers CREATE TABLE customers ( customer_id INT PRIMARY KEY, name VARCHAR(255) NOT NULL ); -- Table of orders CREATE TABLE orders ( order_id INT PRIMARY KEY, customer_id INT, order_date DATETIME, FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE CASCADE );

Data was populated with random values to simulate real-world scenarios.

The Two Queries: JOIN vs. Correlated Subquery

I compared two approaches to achieve the same result.

1. The "Good" Way – JOIN + GROUP BY - This is the set-based, relational approach everyone loves:

   \

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;

\

• Pros: Handles all customers, even those without orders.
• Theory: One optimized operation to join and aggregate.

\

1. The "Bad" Way – Correlated Subquery This is the row-by-row method we're warned against:

   \

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;

\

• Pros: Also includes customers with zero orders.
• Theory: Executes a subquery for each customer—classic N+1 problem.

\

Testing Across Databases: The Results

I ran both queries on online SQL testers (links provided below) and analyzed execution times and plans using EXPLAIN. Here's what happened.

MySQL 8.0: Subquery Wins!

Execution Times: Subquery ~14 ms vs. JOIN ~16 ms.

\ Why? The subquery triggered a Nested Loop plan with fast index lookups (25 quick searches). JOIN used Hash Join + Aggregate, which was overkill for small data.

\ Key Insight: With an index on orders.customer_id, the subquery wasn't N+1—it was efficient Nested Loops.

\ Test Link: MySQL Tester

Oracle 23c: Subquery Dominates!

Execution Times: Subquery ~2.4 ms vs. JOIN ~15 ms.

\ Why? Similar to MySQL—Nested Loop for subquery vs. Hash Join for JOIN. The subquery avoided heavy aggregation overhead.

\ Key Insight: Indexes are crucial; without them, Oracle falls back to full scans.

\ Test Link: Oracle Tester

PostgreSQL 16: JOIN Takes the Lead

Execution Times: JOIN ~0.6 ms vs. Subquery ~1.9 ms.

\ Why? PostgreSQL's optimizer rewrote the subquery into a JOIN-like plan, but the explicit JOIN was slightly faster. Subquery showed 25 sub-plan executions (mild N+1).

\ Key Insight: PostgreSQL is smart—indexes level the playing field.

\ Test Link: PostgreSQL Tester

SQLite 3.45: A Tie!

Execution Times: Both ~1 ms.

\ Why? Plans were nearly identical: SCAN on customers + SEARCH on orders via index. No N+1 effect.

\ Key Insight: SQLite's simplicity made both queries efficient; choose based on readability.

\ Test Link: SQLite Tester

Key Takeaways: No Silver Bullet

The "JOIN is always faster" myth crumbles because performance depends on:

• Database Optimizer: PostgreSQL rewrites queries; MySQL/Oracle follow your syntax more literally.
• Data Size: Small outer tables (like our 25 customers) favor Nested Loops; large ones benefit from Hash Joins.Indexes: Without an index on orders.customer_id, subqueries tank. With it, they shine.
• Bottom Line: Don't blindly follow rules. Always run EXPLAIN (or EXPLAIN ANALYZE) to see the actual execution plan. Test with your data!

\ What are your experiences with JOINs vs. subqueries? Drop a comment below!

\ This article is based on real testing and analysis. Links to testers are provided for you to verify the results.