Correlated Subqueries vs Joins

In this lesson, you will learn how to choose between correlated subqueries and relational joins to ensure your queries remain performant on resource-constrained embedded systems.

The Mechanism of Comparison

When you query related data in a system like SQLite, you often face a choice: do you use a JOIN or a correlated subquery?

A correlated subquery is a nested query that depends on the outer query for its values. It executes once for every single row processed by the outer query. If your outer table has 1,000 rows, the subquery executes 1,000 times.

-- Correlated Subquery approach
SELECT name, (SELECT count(*) FROM orders WHERE orders.user_id = users.id)
FROM users;

A JOIN, conversely, attempts to process the relationship between tables in a single pass—or via a hash/merge join—based on your indexing strategy.

Performance in Embedded Contexts

On low-power or embedded devices, CPU cycles and I/O overhead are at a premium.

1. I/O and Cache Pressure: In a correlated subquery, the database engine must repeatedly re-scan or perform index lookups for the child table. If the data isn't perfectly cached in memory, this generates significant, redundant I/O operations.
2. The Virtual Database Engine (VDBE) Overhead: Because correlated subqueries force the VDBE to execute a loop for every row, they suffer from higher overhead compared to a JOIN, which the query planner can optimize into more efficient operations like a "Nested Loop Join" or "Merge Join" that better utilizes indexes.
3. Memory Constraints: Joins often create intermediate sets in memory. On extremely memory-constrained devices, a massive JOIN might spill to a temporary table on disk (using your database's page management system), whereas a subquery might stay within the current memory page. However, for most use cases, the JOIN remains the preferred method because the query planner has more flexibility to choose an efficient path.

When writing for embedded systems, rely on JOIN syntax whenever possible; it gives the database engine the structural visibility it needs to optimize the execution plan, minimizing the CPU and battery-draining "nested loops" inherent in correlated subqueries.

Key Takeaway: Correlated subqueries execute once per outer row, causing high overhead; Joins allow the query planner to utilize indexing and efficient set-based logic, which is critical for performance on embedded hardware.