Your question is valid, assuming you trust the query planner to make the right decision. However, the query planner in most databases is not perfect and can mis

Your question is valid, assuming you trust the query planner to make the right decision. However, the query planner in most databases is not perfect and can miss. We have tried a few different alternatives, including the join, and found that this sub-query option actually works better. A sub query forces the database to execute the first query on routes using the index, then use the result for the second query on sites. It leave nothing for the interpretation of the optimizer which may decide to so some silly thing like full scan on the sites table before performing the join.