Join Strategy: Merge vs Hash | Dataford Interview Questions

Explain the difference between a merge join and a hash join in SQL Server, and when the optimizer would choose each based on sorted inputs, memory use, and data volume.

Use the provided PostgreSQL tables to demonstrate the joined result, then explain how the optimizer may choose between these strategies. Discuss how sorted inputs, indexes, cardinality, and insufficient memory affect the plan.

Output

Return one row per matching pair, with join_key, left_row_id, right_row_id, and left_value.
Include only rows with matching non-NULL keys.
Order by join_key, left_row_id, and right_row_id ascending.

Schema

left_input

Column	Type	Description
row_idPK	INT	Unique identifier for the left input row
join_key	INT	Nullable equality join key
value	VARCHAR(50)	Value associated with the left input row

right_input

Column	Type	Description
row_idPK	INT	Unique identifier for the right input row
join_key	INT	Nullable equality join key
value	VARCHAR(50)	Value associated with the right input row

Your question is Join Strategy: Merge vs Hash. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem