Every JOIN you add doesn’t just fetch more data, it multiplies your query’s complexity.

Early in my career, I wrote a query joining 5 tables to generate a result. Ran fine in the local machine with test data. In the test machine with real data? 12 seconds. The problem wasn’t the JOIN itself, it was not understanding the cost.

How JOINs Multiply Cost#

-- Example table sizes:
-- orders: 1M rows
-- customers: 100K rows  
-- products: 10K rows

SELECT * FROM orders 
JOIN customers ON orders.customer_id = customers.id
JOIN products ON orders.product_id = products.id;

Without proper indexes, this becomes a Cartesian product nightmare. The database examines exponentially more rows than you expect.

The Three JOIN Strategies#

Databases use different algorithms:

1. Nested Loop Join

  • For each row in table A, scan all matching rows in table B
  • Fast for small tables or when B is well-indexed
  • Catastrophic for large tables without indexes

2. Hash Join

  • Build hash table from smaller table, probe with larger table
  • Good for equality conditions
  • Memory-intensive

3. Merge Join

  • Sort both tables, merge results
  • Efficient for pre-sorted data
  • High cost if sorting required

The database picks based on table size, indexes, and available memory. Sometimes it chooses wrong.

Real-World Impact#

At Oracle, we had a query joining 4 tables taking 12 seconds. After analyzing the execution plan, we chose denormalization:

  • Duplicated frequently-joined customer and product data into the orders table
  • Eliminated 2 JOINs entirely
  • Added triggers to maintain consistency
  • Query time: 40ms

Trade-off accepted: Extra storage and update complexity for 30x faster reads. Worth it for a read-heavy workload.

When to Denormalize#

JOINs have a place, but sometimes they’re the wrong tool:

Keep JOINs when:

  • Data changes frequently (single source of truth)
  • Storage is limited
  • Joins are on indexed columns with high cardinality

Consider denormalization when:

  • Read-heavy workload (10:1 read/write ratio)
  • JOIN cost exceeds storage cost
  • Data rarely changes

Trade-off: Storage and consistency vs. query speed.

The Lesson#

That 5-table JOIN query? We denormalized frequently-accessed reference data into the main transaction table. Eliminated most JOINs. 30x faster.

Denormalization isn’t always the answer, but understanding JOIN costs helps you make the right trade-off.

Conclusion: JOINs multiply execution cost. Profile queries, understand the algorithm, and sometimes the best JOIN is no JOIN.

What’s your JOIN story? Ever chosen denormalization over complexity?