×

DBMS Tutorial

DBMS Practice

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.RollNoStudent2

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.RollNoStudent2

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.



Comments and Discussions!

Load comments ↻





Copyright © 2024 www.includehelp.com. All rights reserved.