For an equality join between two very large tables, when would you expect a sort-merge join to be the better physical operator than a hash join, and what properties of the data, the storage layout and the machine drive that judgement?

## Framing the decision Both operators solve the same problem: find equal keys across two large inputs. Hash join makes keys comparable by hashing; merge join makes them comparable by sorting. Every difference in fit follows from that. ## Existing order is the strongest signal Sorting is the merge join's whole cost. If the tables are indexed or physically clustered on the join key so both sides can be read in order, the join is one linear pass with near-zero memory. That is the case worth engineering deliberately for a join that runs constantly at scale. Conversely, if both sides need full sorts, the merge join pays O(N log N) plus temp I/O twice, and a hash join whose build side fits usually wins outright. ## Memory and degradation shape Hash join's profile is a cliff: while the build side fits its grant it is near-optimal; when it does not, it partitions both inputs, writes them out, processes partition pairs, and if a partition still does not fit it recurses. A sort's profile is a slope: less memory means more merge passes, each large sequential reads and writes. When both inputs are far larger than memory, the slope is often kinder than the cliff, and merge join runtime is easier to predict — which matters when there is a latency budget. ## Ordering as a plan-level asset Cost the plan, not the operator. A merge join emits its result ordered by the join key. If a later grouping, a window partitioned by that key, another merge join, or a final ORDER BY wants that order, the merge join has paid for something the plan needed anyway. Chains of merge joins on a shared key propagate order through the pipeline and can remove several sorts. ## Skew, from both angles Skew hurts each algorithm in its own way. In a hash join, a hot key or an unlucky distribution makes one partition enormous and one worker does most of the work. In a merge join, a hot key means one giant equal-key group with rewinds and possible materialisation. What differs is that merge join pain is proportional to true output size, while a hash join can be hurt by distribution alone even when the output is small. ## Parallelism and distribution In a parallel or distributed engine, hash join pairs naturally with hash partitioning on the join key: both sides are repartitioned and each worker joins its slice. Merge join pairs with range partitioning and order-preserving exchanges, which is more sensitive to skewed ranges but reuses sorted layouts when data is already stored that way — sorted or clustered storage formats make merge-style joins attractive again at scale. ## Predicate shape Hash join needs an equality predicate; so does classic merge join, although sorted inputs also make range and band conditions tractable in engines that support them. With no usable equality predicate at all, neither is available and the engine falls back to a loop-based strategy. ## How I would actually decide Measure first: check true cardinalities and the key's frequency distribution, then check whether either side already has a usable order. If the join is a permanent, high-volume part of the workload, decide the storage layout — clustering or indexes on the join key — and let merge join become the cheap steady state. For ad-hoc analytics over unordered data on a machine with generous memory, hash join is the default and I would not fight it. The trap is choosing an operator in the abstract instead of choosing the data layout that makes one of them cheap.