Cross-shard JOINs are expensive because they may require moving data across the network. The optimal strategy depends on how tables are partitioned.

Co-located joins (best case):

• Both tables are hash-partitioned by the join key
• Example: users and orders both partitioned by user_id
• Each shard can perform the join locally on its partition
• Coordinator merges partial results
• Zero network overhead after the initial scatter

Example co-located join:

Request:  {"type": "join_query", "msg_id": 1, "left": "users", "right": "orders", "on": "user_id"}
Response: {"type": "join_query_ok", "in_reply_to": 1, "results": [...], "strategy": "co-located", "shuffle_bytes": 0}

Shuffle joins (worst case):

• Tables are partitioned by different keys
• Example: users by user_id, orders by order_id
• Must repartition both tables by the join key
• Or broadcast the smaller table to all shards
• High network overhead

Example shuffle join:

Request:  {"type": "join_query", "msg_id": 2, "left": "users", "right": "reviews", "on": "user_id"}
Response: {"type": "join_query_ok", "in_reply_to": 2, "results": [...], "strategy": "hash-shuffle", "shuffle_bytes": 5242880}

Implementation strategies:

1. Check partitioning metadata for both tables
2. If co-located, execute local joins on each shard
3. If not co-located, choose the cheapest strategy:
   • Broadcast join if one table is small (< 1000 rows)
   • Hash shuffle if both tables are large
4. Return the strategy used and shuffle_bytes for visibility