Relational Algebra - Theta-join

1 - About

A theta-join is a difficult/complex join where the condition is not a equality.

Example:

  • Band join
  • or range join.

A theta is a join that links tables based on a relationship other than the equality between two columns.

A theta join could use any other operator than the equal operator.

A theta may not have any join key in the sql but you still have a join physically (ie when running the sql).

3 - Notation

Algebraic rule

Physically, the join of R1 and R2 with the condition theta is the same than the selection of the cross product of R1 and R2 with the condition theta.

4 - Example

4.1 - Find all customer in a age range

4.2 - Find all the hospitals within 5 miles of a school


select distinct h.name
from hospitals h, schools s
where 
distance(h.location,s.location) < 5

where: Distance

4.3 - Find all user clicks made within 5 seconds of page load

Find all user clicks made within 5 seconds of page load


select *
from Clicks c, PageLoads p
where 
abs(c.click_time - p.load_time) < 5


Data Science
Data Analysis
Statistics
Data Science
Linear Algebra Mathematics
Trigonometry

Powered by ComboStrap