Hash Join vs Merge Join

Explain the difference between a hash join and a merge join, and describe when the query planner would choose one over the other.

Use PostgreSQL terminology and relate the explanation to the supplied membership query. State the relevant cost, input-ordering, memory, and data-volume considerations.

Output

1. Return creator_id, creator_name, member_id, and membership_tier for matching membership records.
2. Include only active memberships and order by creator_id, then member_id.
3. Explain how PostgreSQL could execute the join using either algorithm.