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
); -- 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. 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; 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; 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; 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. Pros: Handles all customers, even those without orders. Theory: One optimized operation to join and aggregate. 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; 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; 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; 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. 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 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 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 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 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! 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! 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.

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

About Author

Comments

TOPICS

THIS ARTICLE WAS FEATURED IN

Related Stories

A Tool to Help Fix SQL Code Using ChatGPT

2019 Database Trends – SQL vs. NoSQL, Top Databases, Single vs. Multiple Database Use

5 Best Courses to Learn MySQL Database for Beginners

58 Stories To Learn About Mysql

The Noonification: Using Git Hooks with MySQL (9/30/2023)

A Basic Guide to MySQL Tests Automation

A Tool to Help Fix SQL Code Using ChatGPT

2019 Database Trends – SQL vs. NoSQL, Top Databases, Single vs. Multiple Database Use

5 Best Courses to Learn MySQL Database for Beginners

58 Stories To Learn About Mysql

The Noonification: Using Git Hooks with MySQL (9/30/2023)

A Basic Guide to MySQL Tests Automation

Light-Mode

Classic

Newspaper

Minty

Dark-Mode

Neon Noir

Minty

HN StartUps