Abstract
Inequality joins, which join relational tables on inequality
conditions, are used in various applications. While there
have been a wide range of optimization methods for joins in
database systems, from algorithms such as sort-merge join
and band join, to various indices such as
B+-tree,R*-tree and Bitmap, inequality joins have received little attention and queries containing such joins are usually very slow. In this paper, we introduce fast inequality join algorithms. We put columns to be joined in sorted arrays and we use permutation arrays to encode positions of tuples in one sorted array w.r.t. the other sorted array. In contrast to sort-merge join, we use space effcient bit-arrays that enable optimizations, such as Bloom filter indices, for fast computation of the join results. We have implemented a centralized version
of these algorithms on top of PostgreSQL, and a distributed
version on top of Spark SQL. We have compared against
well known optimization techniques for inequality joins and
show that our solution is more scalable and several orders
of magnitude faster.