What is join operation in relational algebra (DBMS)?

DBMS Database Big Data Analytics

Join operation combines the relation R1 and R2 with respect to a condition. It is denoted by ⋈.

The different types of join operation are as follows −

Theta join
Natural join
Outer join − It is further classified into following types −
- Left outer join.
- Right outer join.
- Full outer join.

Theta join

If we join R1 and R2 other than the equal to condition then it is called theta join/ non-equi join.

Example

Consider R1 table

RegNo	Branch	Section
1	CSE	A
2	ECE	B
3	CIVIL	A
4	IT	B
5	IT	A

Table R2

Name	RegNo
Bhanu	2
Priya	4

R1 ⋈ R2 with condition R1.regno > R2.regno

RegNo	Branch	Section	Name	Regno
3	CIVIL	A	Bhanu	2
4	IT	B	Bhanu	2
5	IT	A	Bhanu	2
5	IT	B	Priya	4

In the join operation, we select those rows from the cartesian product where R1.regno>R2.regno.

Join operation = select operation + cartesian product operation

Natural join

If we join R1 and R2 on equal condition then it is called natural join or equi join. Generally, join is referred to as natural join.

Natural join of R1 and R2 is −

{ we select those tuples from cartesian product where R1.regno=R2.regno}

R1 ⋈ R2

Regno	Branch	Section	Name
2	-	-	Bhanu
4	-	-	priya

Outer join

It is an extension of natural join to deal with missing values of relation.

Consider R1 and R2 shown below −

Table R1

RegNo	Branch	Section
1	CSE	A
2	ECE	B
3	CIVIL	A
4	IT	B
5	IT	A

Table R2

Name	Regno
Bhanu	2
Priya	4
Hari	7

Outer join is of three types. These are explained below −

Left outer join

It is denoted by R1 ⋈ R2.

RegNo	Branch	Section	Name	Regno
2	-	-	Bhanu	2
4	-	-	Priya	4
1	-	-	NULL	NULL
3	-	-	NULL	NULL
5	-	-	NULL	NULL

Here all the tuples of R1(left table) appear in output.

The mismatching values of R2 are filled with NULL.

Left outer join = natural join + mismatch / extra tuple of R1

Right outer join

It is denoted by R1 ⋈ R2

Here all the tuples of R2(right table) appear in output. The mismatching values of R1 are filled with NULL.

RegNo	Branch	Section	Name	Regno
2	-	-	Bhanu	2
4	-	-	Priya	4
NULL	NULL	NULL	Hari	7

Right outer join = natural join+ mismatch/extra tuple of R2.

Full outer join

It is denoted by R1 ⋈ R2.

Full outer join=left outer join U right outer join.

RegNo	Branch	Section	Name	Regno
2	-	-	Bhanu	2
4	-	-	Priya	4
1	-	-	NULL	NULL
3	-	-	NULL	NULL
5	-	-	NULL	NULL
NULL	NULL	NULL	Hari	7

Example

Given below is the picture of full outer join −

Bhanu Priya

Published on 06-Jul-2021 14:31:41

Previous Page Print Page