0:00
So, welcome back. So now,
we're going to talk about the join operator.
And more specifically, the theta-join.
So, join between two different relation in the database, r and s,
is similar to the Cartesian products,
except that, the Cartesian products,
you perform a selection on all the combined tuples of the Cartesian product,
and the selection predicate is theta.
So, that's a selection predicate.
So, what does it mean?
So, Cartesian product just find all combinations of tuples with an r and s,
and combine them together.
Theta-join, it does not just find all combinations.
Yes, if you think of it as if it applies a Cartesian product, find all combinations,
and for all the found combinations that applies a predicate, or a condition,
on these tuples, and filter out the combined tuples that do not satisfy this condition.
So, an example query is if you have two tables,
you have the CSE professors,
these are computer science professors,
and you have another table that has
all the courses that each professor is currently teaching.
And the join operator you want to apply,
you want to find all the courses that each professor is currently teaching,
and you want to find the information of the professor and the information of the course.
So, this is a join between the cse_prof table and the teaches,
the currently teaches table, or relation.
And again, the schema of the output of
the join is also similar to the schema of the output of
the Cartesian product that combines
both the schema of cse_prof and the schema of the teaches.
So, this is teaches,
sorry, instead of cse_teaching,
and this is cse-prof.
If you want to have a join between both of them, again,
like we said in last slide,
cse_profs and then you do the join,
and the theta condition here is,
you want the name in the cse_profs table is equal to the tname in the teaches table.
2:41
So, what you do here is that the same way you did in Cartesian product,
you go through cse_prof, you say Professor1.
So, in Professor1, all the combination will be like you merged all of these together,
but the condition needs to be name equal tname.
So, you say, "Okay, name a Professor1."
It matches here, professor1,
so, this tuple is out.
And then Professor1 matches this tname here also, in the teaches.
So, this tuple is also part of the result.
But Professor1 and professor2, they do not match.
So, this theta condition
Mohamed SarwatAssistant Professor
