nonequijoin

Nonequijoin, also known as a non-equijoin, is a type of join in relational databases where the join predicate uses an operator other than equality. Unlike an equijoin, which combines rows when keys are exactly equal (for example, A.id = B.id), a nonequijoin uses conditions such as <, >, <=, >=, !=, BETWEEN, or LIKE. In many database texts, the broader term theta join is used for a join with arbitrary comparison predicates, with nonequijoin referring specifically to non-equality predicates.

Non-equijoins are commonly used for range- or pattern-based joins. Examples include joining two tables on a date range (A.date BETWEEN B.start_date AND B.end_date), finding records where a value falls above another table’s threshold (A.value > B.threshold), or matching rows by a non-exact pattern (A.name LIKE B.pattern). These joins can also express inequalities across keys or attributes that do not have a one-to-one correspondence.

Performance and optimization considerations are important with nonequijoins. Because the join condition often does not preserve simple hash-based or index-friendly equality, execution plans may rely on nested loops or sort-merge strategies and can be more expensive for large datasets. Strategies to improve performance include careful indexing on the join attributes, partitioning, and, where possible, rewriting the query to reduce the amount of data involved in the join.

In practice, nonequijoins are a flexible tool for querying relational data when exact key matches are not sufficient, enabling range checks, temporal joins, and pattern-based correlations within standard SQL.