ThetaJoins

Theta joins are a relational operation that combines tuples from two relations, R and S, when a predicate θ involving attributes from both relations evaluates to true. The predicate can be any comparison operator, including equality, inequality, or other comparisons such as <, >, <=, >=, and <>. When θ is strictly equality, the operation is typically called an equijoin; theta join encompasses equijoins as well as non-equality conditions.

Formally, the theta join R ⨝_θ S yields the set of all concatenated tuples (r, s) where r is drawn from R, s from S, and θ(r, s) holds. The predicate θ may compare an attribute from R with an attribute from S, or involve constants, but it must relate the two relations.

In practice, theta joins can be implemented as a cross product followed by a selection, or directly via a join with a predicate in SQL. For example, SELECT ... FROM R JOIN S ON θ expresses a theta join where the ON clause encodes the predicate. The approach taken affects optimization: equijoins often enable hash or sort-merge joins, while general theta predicates may rely more on nested-loop strategies or specialized indexing.

Applications of theta joins include non-equijoins required for range constraints, time-based joins, and spatial or value-based comparisons. They are foundational in queries that need to relate datasets through inequalities or complex predicates, where a straightforward equijoin would be insufficient.