Minimizing joins and subqueries in dynamic queriesfor Drupal 11 , 10 , 9 , and 8

A join lets the database combine rows from two tables in a single pass — powerful, but every join adds real work: the database must match rows from one table against another, and the cost of that matching grows fast as tables grow. A dynamic query with three unnecessary joins and a nested subquery isn't just harder to read — it's frequently the single slowest thing running on a page.

What you'll learn in this lesson

• How to add a join correctly, and when you genuinely need one
• Why a subquery in the WHERE clause is often replaceable with a join, and why that usually helps
• How to spot a join you added out of habit rather than necessity

A real join, done correctly

$query = \Drupal::database()->select('performance_example_log', 'e');
$query->join('users_field_data', 'u', 'e.uid = u.uid');
$query->addField('e', 'id');
$query->addField('u', 'name', 'username');
$query->condition('e.event_type', 'login');
$result = $query->execute()->fetchAll();

This is a legitimate join: the query genuinely needs a column (username) that only exists in a different table (users_field_data), so it joins once, on an indexed column (uid), to get it in the same result set. That's exactly what joins are for. The problem isn't joins themselves — it's joins added when the data was actually already available, or added multiple times when one would do.

Replacing a subquery with a join

// Slower: a subquery re-runs conceptually for every outer row.
$query = \Drupal::database()->select('performance_example_log', 'e');
$query->condition('e.uid', \Drupal::database()->select('users_field_data', 'u')
  ->fields('u', ['uid'])
  ->condition('u.status', 1), 'IN');

// Faster: the database can plan one combined operation instead.
$query = \Drupal::database()->select('performance_example_log', 'e');
$query->join('users_field_data', 'u', 'e.uid = u.uid');
$query->condition('u.status', 1);

Both return the same rows, but the join version gives the database's query planner a single combined operation to optimize, rather than a nested subquery it may have to evaluate repeatedly. This isn't universally true for every database engine and every subquery shape, but as a starting rule of thumb: if a subquery in your WHERE clause is just checking membership against another table's rows, try rewriting it as a join first and compare.

Quick check: a query joins three tables but only ever selects columns from the first and filters on a column from the second — the third table's join contributes nothing to the result. What should you do? (Remove the third join entirely — it costs the database real work for zero benefit to this specific query, even though it might look "more complete" left in.)

Key takeaways

• Every join adds real matching work — only join a table when you genuinely need a column or filter from it.
• A WHERE ... IN (subquery) pattern can frequently be rewritten as a join, often with a meaningful performance improvement.
• Audit joins periodically for ones that no longer contribute any selected column or condition — they're pure overhead once that happens.
• Always index the columns a join matches on (like uid above) — an unindexed join column defeats most of the benefit of joining efficiently in the first place.

Coming up next

Writing efficient queries only gets you so far if you can't tell which queries are actually slow in the first place. Next, we look at using Drupal's own logs to find the queries actually worth optimizing.