A theta join is a join that links tables based on a relationship other than equality between two columns. A theta join could use any operator other than the “equal” operator. The following example and procedure show you how to create a theta join that uses the “Between” operator.
The Age_Group table below contains age range information that can be used to analyze data on the age of customers.
You need to include this table in the universe, but there is no common column between the Customer table and the Age_Group table, so you cannot use an equi-join. You create a theta join using the operator “Between” for maximum age range and minimum age ranges. By using a theta join, you infer that a join exists where the value in a row of the Age column in the Customer table is between the values in a row for the Age_Min and Age_Max columns of the Age_Group table. The join is defined by the following expression:
Customer.age between Age_group.age_min and Age_group.age_max
The diagram below shows the joins between Age max, Age min, and Age, and the result set that is returned when the theta join is used in a query run on both Age_Group and Customer tables.
