Theta Join

Definition

A Theta Join (symbol: ⋈θ) combines two relations by taking their Cartesian Product and keeping only the rows that satisfy an arbitrary condition θ (theta) — theta can be ANY comparison operator (=, <, >, <=, >=, <>), not just equality. It is the most general join type; every other join (Equi, Natural, Outer) is a special case of it.

How It Works

sql
SELECT e.name, e.salary, s.min_salary, s.max_salary
FROM employees e
JOIN salary_bands s ON e.salary BETWEEN s.min_salary AND s.max_salary;

Here the join condition is a RANGE comparison, not equality — this is a genuine Theta Join, since θ is BETWEEN (itself built from >= and <=), not =. This is exactly why Theta Join is more general than an Equi Join (7.9): the join condition can be any comparison, and different rows can be matched by entirely different kinds of relationships depending on what θ expresses.

Edge Cases and Pitfalls

• Most real-world joins ARE equality joins (matching a foreign key to a primary key) — genuine non-equality Theta Joins (using <, >, BETWEEN) are less common but appear in real problems like this salary-band example, or "find pairs of events where one started before the other ended."
• A Theta Join with an always-true condition (ON 1=1) degenerates into exactly a Cartesian Product — confirming that Cartesian Product is indeed the special case of Theta Join with a trivial condition.
• Theta Join conditions can combine multiple comparisons with AND/OR, just like a Selection's condition can — the formal definition (Cartesian Product + Selection with condition θ) makes this natural.

Key Takeaways

• Theta Join (⋈θ) = Cartesian Product filtered by ANY comparison condition θ, not just equality.
• It's the most general join; Equi Join, Natural Join, and Outer Join are all more specific variants of it.
• Range-based join conditions (BETWEEN, <, >) are genuine, real-world Theta Joins beyond the common equality case.