- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
What is join operation in relational algebra (DBMS)?
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 −
- Related Questions & Answers
- Explain rename operation in relational algebra (DBMS)?
- Explain project operation in relational algebra (DBMS)?
- Explain intersection operation in relational algebra (DBMS)?
- Explain union operation in relational algebra (DBMS)?
- Explain division operation in relational algebra (DBMS)?
- Explain the select operation in relational algebra (DBMS)?
- Explain the relational algebra in DBMS?
- Explain cartesian product in relational algebra (DBMS)?
- Explain the binary operations in relational algebra (DBMS)?
- Explain the evaluation of relational algebra expression(DBMS)
- Write queries using aggregate functions in relational algebra (DBMS)?
- Difference between Relational Algebra and Relational Calculus
- Basic Operators in Relational Algebra
- What is lossless join decomposition in DBMS?
- What are the capabilities of DBMS and why relational DBMS is powerful?