Sort-merge join

The sort-merge join (also known as merge join) is a join algorithm and is used in the implementation of a relational database management system.

The basic problem of a join algorithm is to find, for each distinct value of the join attribute, the set of tuples in each relation which display that value. The key idea of the sort-merge algorithm is to first sort the relations by the join attribute, so that interleaved linear scans will encounter these sets at the same time.

In practice, the most expensive part of performing a sort-merge join is arranging for both inputs to the algorithm to be presented in sorted order. This can be achieved via an explicit sort operation (often an external sort), or by taking advantage of a pre-existing ordering in one or both of the join relations. ^[1] The latter condition, called interesting order, can occur because an input to the join might be produced by an index scan of a tree-based index, another merge join, or some other plan operator that happens to produce output sorted on an appropriate key. Interesting orders need not be serendipitous: the optimizer may seek out this possibility and choose a plan that is suboptimal for a specific preceding operation if it yields an interesting order that one or more downstream nodes can exploit.

Let's say that we have two relations ${\displaystyle R}$ and ${\displaystyle S}$ and ${\displaystyle |R|<|S|}$. ${\displaystyle R}$ fits in ${\displaystyle P_{r}}$ pages memory and ${\displaystyle S}$ fits in ${\displaystyle P_{s}}$ pages memory. So, in the worst case sort-merge join will run in ${\displaystyle O(P_{r}+P_{s})}$ I/Os. In the case that ${\displaystyle R}$ and ${\displaystyle S}$ are not ordered the worst case time cost will contain additional terms of sorting time: ${\displaystyle O(P_{r}+P_{s}+P_{r}\log(P_{r})+P_{s}\log(P_{s}))}$, which equals ${\displaystyle O(P_{r}\log(P_{r})+P_{s}\log(P_{s}))}$ (as linearithmic terms outweigh the linear terms, see Big O notation – Orders of common functions).

Pseudocode

For simplicity, the algorithm is described in the case of an inner join of two relations left and right. Generalization to other join types is straightforward. The output of the algorithm will contain only rows contained in the left and right relation and duplicates form a Cartesian product.

functionSort-MergeJoin(left: Relation,right: Relation,comparator: Comparator){result=newRelation()// Ensure that at least one element is presentif(!left.hasNext()||!right.hasNext()){returnresult}// Sort left and right relation with comparatorleft.sort(comparator)right.sort(comparator)// Start Merge Join algorithmleftRow=left.next()rightRow=right.next()outerForeverLoop:     while(true){while(comparator.compare(leftRow,rightRow)!=0){if(comparator.compare(leftRow,rightRow)<0){// Left row is less than right rowif(left.hasNext()){// Advance to next left rowleftRow=left.next()}else{breakouterForeverLoop}}else{// Left row is greater than right rowif(right.hasNext()){// Advance to next right rowrightRow=right.next()}else{breakouterForeverLoop}}}// Mark position of left row and keep copy of current left rowleft.mark()markedLeftRow=leftRowwhile(true){while(comparator.compare(leftRow,rightRow)==0){// Left row and right row are equal// Add rows to resultresult=add(leftRow,rightRow)// Advance to next left rowleftRow=left.next()// Check if left row existsif(!leftRow){// Continue with inner forever loopbreak}}if(right.hasNext()){// Advance to next right rowrightRow=right.next()}else{breakouterForeverLoop}if(comparator.compare(markedLeftRow,rightRow)==0){// Restore left to stored markleft.restoreMark()leftRow=markedLeftRow}else{// Check if left row existsif(!leftRow){breakouterForeverLoop}else{// Continue with outer forever loopbreak}}}}returnresult}

Since the comparison logic is not the central aspect of this algorithm, it is hidden behind a generic comparator and can also consist of several comparison criterias (e.g. multiple columns). The compare function should return if a row is less(-1), equal(0) or bigger(1) than another row:

functioncompare(leftRow: RelationRow,rightRow: RelationRow):number{// Return -1 if leftRow is less than rightRow// Return 0 if leftRow is equal to rightRow// Return 1 if leftRow is greater than rightRow}

Note that a relation in terms of this pseudocode supports some basic operations:

interfaceRelation{// Returns true if relation has a next row (otherwise false)hasNext():boolean// Returns the next row of the relation (if any)next():RelationRow// Sorts the relation with the given comparatorsort(comparator: Comparator):void// Marks the current row indexmark():void// Restores the current row index to the marked row indexrestoreMark():void}

Simple C# implementation

Note that this implementation assumes the join attributes are unique, i.e., there is no need to output multiple tuples for a given value of the key.

publicclassMergeJoin{// Assume that left and right are already sortedpublicstaticRelationMerge(Relationleft,Relationright){Relationoutput=newRelation();while(!left.IsPastEnd()&&!right.IsPastEnd()){if(left.Key==right.Key){output.Add(left.Key);left.Advance();right.Advance();}elseif(left.Key<right.Key)left.Advance();else// if (left.Key > right.Key)right.Advance();}returnoutput;}}publicclassRelation{privateList<int>list;publicconstintENDPOS=-1;publicintposition=0;publicintPosition=>position;publicintKey=>list[position];publicboolAdvance(){if(position==list.Count-1||position==ENDPOS){position=ENDPOS;returnfalse;}position++;returntrue;}publicvoidAdd(intkey){list.Add(key);}publicboolIsPastEnd(){returnposition==ENDPOS;}publicvoidPrint(){foreach(intkeyinlist)Console.WriteLine(key);}publicRelation(List<int>list){this.list=list;}publicRelation(){this.list=newList<int>();}}

See also

• Hash join
• Nested loop join

References

1. "Sort-Merge Joins". www.dcs.ed.ac.uk. Retrieved 2022-11-02.

External links

C# Implementations of Various Join Algorithms