Extended Operators in DBMS
Extended Operators in DBMS
On this page, we will learn about Extended Operators in DBMS. There are different types of Extended Operators which is explained below in brief.
Relational Algebra in DBMS
Relational algebrais a widely used procedural query language.- Relational algebra collects instances of relations as input and gives occurrences of relations as output by using various operations.
- Relational algebra operations are performed recursively on a relation.
- The output of relational algebra operations is a new relation, which can be formed from one or more input relations.
Extended Operators in Relational Algebra:
Extended operators in Relational algebra are:
- Natural Join (⋈)
- Left, Right, Full outer join (⟕, ⟖, ⟗)
- Intersection (∩)
Learn more about Relational Algebra here on this page.
Natural Join (⋈)
- JOIN operation also allows joining variously related tuples from different relations.
- Natural join can only be performed if there is a common attribute (column) between the relations.
- The name and type of the attribute must be same.
Example:
Consider the following two tables:Table C
| Num | Square |
|---|---|
| 2 | 4 |
| 3 | 9 |
Table D
| Num | Cube |
|---|---|
| 2 | 8 |
| 3 | 27 |
C ⋈ D
| Num | Square | Cube |
|---|---|---|
| 2 | 4 | 8 |
| 3 | 9 | 27 |
Outer Join
- In an outer join, along with tuples that satisfy the matching criteria, we also include some or all tuples that do not match the criteria.
- There are three types of outer joins. They are
- Left Outer Join
- Right Outer Join
- Full Outer Join
- Consider the following 2 Tables :
Table A
| Num | Square |
|---|---|
| 2 | 4 |
| 3 | 9 |
| 4 | 16 |
Table B
| Num | Cube |
|---|---|
| 2 | 8 |
| 3 | 27 |
| 5 | 125 |
Left Outer Join(A 
B)
- In the left outer join, the operation allows keeping all tuple in the left relation.
- If there is no matching tuple is found in the right relation, then the attributes of the right relation in the join results are filled with null values.
Example:
A B
| Num | Square | Cube |
|---|---|---|
| 2 | 4 | 8 |
| 3 | 9 | 27 |
| 4 | 16 | – |
Right Outer Join(A B)
- In the right outer join, the operation allows keeping all tuple in the right relation.
- If there is no matching tuple is found in the left relation, then the attributes of the left relation in the join results are filled with null values.
Example:
AB
| Num | Square | Cube |
|---|---|---|
| 2 | 4 | 4 |
| 3 | 9 | 9 |
| 5 | – | 125 |
Full Outer Join: ( A B)
In a full outer join, all tuples from both relations are included in the result, irrespective of the matching condition.
A B
| Num | Square | Cube |
|---|---|---|
| 2 | 4 | 8 |
| 3 | 9 | 27 |
| 4 | 16 | – |
| 5 | – | 125 |
Intersection (∩)
- An intersection is defined by the symbol ∩
- A ∩ B defines a relation consisting of a set of all tuple that is in both A and B. However, A and B must be union-compatible.
Example:
Consider 2 tables –
Table A :
| Attribute1 | Attribute2 |
|---|---|
| 1 | 1 |
| 1 | 2 |
Table B :
| Attribute1 | Attribute2 |
|---|---|
| 1 | 1 |
| 1 | 3 |
A ∩ B
| Attribute1 | Attribute2 |
|---|---|
| 1 | 1 |
Prime Course Trailer
Related Banners
Get PrepInsta Prime & get Access to all 200+ courses offered by PrepInsta in One Subscription
Get over 200+ course One Subscription
Courses like AI/ML, Cloud Computing, Ethical Hacking, C, C++, Java, Python, DSA (All Languages), Competitive Coding (All Languages), TCS, Infosys, Wipro, Amazon, DBMS, SQL and others
Login/Signup to comment