Joins In DBMS

(part 1)

There are several types of joins used in relational algebra to get results from two or more tables.

Theta join

The theta join operation defines a relation that contains tuples satisfying the predicate from the Cartesian product of R and S. Theta can be <,>,<=,>=,=,≠

Example 

Here output will be the tuples that have same “sid” in both tables (Cartesian product).

R

sid	sname	rating	age
22	Dustin	7	45.0
31	Lubber	8	55.5
58	Rusty	10	35.0

S

sid	bid	day
22	101	7/10
31	103	8/02

Here from S and R Cartesian product is (RxS) as follows.

RxS

sid	sname	rating	age	sid	bid	day
22	Dustin	7	45.0	22	101	7/10
22	Dustin	7	45.0	58	103	8/02
31	Lubber	8	55.5	22	101	7/10
31	Lubber	8	55.5	58	103	8/02
58	Rusty	10	35.0	22	101	7/10
58	Rusty	10	35.0	58	103	8/02

But from above theta join we can have only tuples that have same “sid” in two “sid” fields in RxS

sid	sname	rating	age	sid	bid	day
22	Dustin	7	45.0	22	101	7/10
22	Dustin	7	45.0	58	103	8/02
31	Lubber	8	55.5	22	101	7/10
31	Lubber	8	55.5	58	103	8/02
58	Rusty	10	35.0	22	101	7/10
58	Rusty	10	35.0	58	103	8/02

Highlighted tuples are the result of theta join.

Equi join

This is also a particular type of theta join. But it always deals with equal operator.

 From theta join : 

This can be expressed using equi join as

Natural join

The natural join is an equi join of the two relations R and S over all common attributes. One occurrence of each common attribute is dominated from the result.

In equi join we has below results.

Here we have two “sid” columns. But in natural join we have only one column from duplicated columns. 

sid	sname	rating	age	bid	day
22	Dustin	7	45.0	101	7/10
58	Rusty	10	35.0	103	8/02

To be continued...