Home » DBMS

DBMS | Extended Operators in Relational Algebra

Extended Operators in Relational Algebra: In this tutorial, we will learn about the extended operators, their types with the examples in Relational Algebra. By Anushree Goswami Last updated : May 28, 2023

What are Extended Operators in Relational Algebra?

In Relational Algebra, Extended Operators are those operators that are derived from the basic operators. We already have discussed Basic Operators in the previous section. Now let us discuss the Extended Operators and how they are beneficial in Relational Algebra.

Types of Extended Operators in Relational Algebra

There are mainly three types of extended operators in relational algebra, namely:

1. Intersection
2. Divide
3. Join

1) Intersection

Intersection works on the relation as 'this and that'. In relational algebra, A ∩ B returns a relation instance that contains every tuple that occurs in relation to instance A and relation instance B (both together). Here, A and B need to be union-compatible, and the schema of both result and A must be identical.

Example

Let us consider two tables named as A and B.

A –

RollNo	Name	Marks
1	Aashi	98
3	Anjali	79
4	Brijesh	88

B –

RollNo	Name	Marks
1	Aashi	98
2	Abhishek	87
3	Anjali	79
4	Brijesh	88

Query -

SELECT * FROM A INTERSECT SELECT * FROM B;

RollNo	Name	Marks
1	Aashi	98
3	Anjali	79
4	Brijesh	88

2) Divide

Divide operator is used for the queries that contain the keyword ALL.

Example

Find all the students who has chosen additional subjects Machine Learning and Data Mining.

Student –

Student Name	Subject
Ashish	Machine Learning
Ashish	Data Mining
Shivam	Network Security
Shivam	Data Mining
Tarun	Network Security
Tarun	Machine Learning
Yash	Machine Learning
Yash	Data Mining

Subject –

Student Name
Machine Learning
Data Mining

Output: Student ÷ Subject

Student
Ashish
Yash

3) Join

Join operation is as its name suggest, to join or combine two or more relations' information. Join can also be defined as a cross-product followed by selection and projection. There are several varieties of Join operation. Let's discuss all of them one by one.

Student1 –

RollNo	Name	Marks
1	Ashish	98
2	Shivam	72
3	Tarun	53
4	Yash	89

Student2 –

RollNo	Name	Marks
1	Anjali	99
4	Dinesh	79
5	Harsh	95
7	Kartik	88

3.1) Condition Join

When you want to join two relations based on the given condition, it is termed as Condition Join. It is denoted by the symbol ⋈_c.

For e.g. – Select the students from Student1 table whose RollNo is greater than the RollNo of Student2 table.

Student1⋈_{cStudent1.RollNo>Student2.RollNo}Student2

Example

SELECT * FROM Student1, Student2 WHERE Student1.RollNo > Student2.RollNo;

Output –

RollNo	Name	Marks	RollNo	Name	Marks
2	Shivam	72	1	Anjali	99
3	Tarun	53	1	Anjali	99
4	Yash	89	1	Anjali	99

3.2) Equi Join

It is a special case of Condition Join. When you want to join two relations based on the equality condition, it is termed as Equi Join. It is denoted by the symbol ⋈.

For e.g. - Select the students from Student1 table whose RollNo is equalto the RollNo of Student2 table.

Student1⋈_{Student1.RollNo=Student2.RollNo}Student2

Example

SELECT * FROM Student1, Student2 WHERE Student1.RollNo=Student2.RollNo;

Output –

RollNo	Name	Marks	RollNo	Name	Marks
1	Ashish	98	1	Anjali	99
4	Yash	89	4	Dinesh	79

3.3) Natural Join

Natural Join is that type of join in which equijoin is by default applied to all the attributes in two or more relation. Its specialty is if you want to consider the equality between two relations, you don't need to define the equality; it is predefined for all the attributes if you use Natural Join. It is denoted by the symbol ⋈.

For e.g. - Select the students from Student1 table whose RollNo is equal to the RollNo of Student2 table.

Student1⋈Student2

Example

SELECT * FROM Student1 NATURAL JOIN Student2;

Output –

RollNo	Name	Marks	RollNo	Name	Marks
1	Ashish	98	1	Anjali	99
4	Yash	89	4	Dinesh	79

There is also one more type of Join Outer Join which further gets divided into Left Outer Join, Right Outer Join and Full Outer Join. We will discuss it in other section in Inner Join VS Outer Join.