- Data Structure
- Networking
- RDBMS
- Operating System
- Java
- MS Excel
- iOS
- HTML
- CSS
- Android
- Python
- C Programming
- C++
- C#
- MongoDB
- MySQL
- Javascript
- PHP
- Physics
- Chemistry
- Biology
- Mathematics
- English
- Economics
- Psychology
- Social Studies
- Fashion Studies
- Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Explain join operations with the help of an example in DBMS
Join operation combines two relations with respect to a condition, It is denoted by ⋈. Joins are of different types, Theta join, Natural join, Outer join (Left outer join, Right outer join, Full outer Join).
Example
Consider an example as given below −
Step 1
Query
create a table student (name char(30), regno number(10));
Output
Table created.
Step 2
Query
insert into student values (‘hari’, 1); Insert into student values (‘subbu’, 2); Insert into student values (‘srinu’, 3);
Output
3 rows created.
Step 3
Query
select * from student;
Output
Name | Regno |
---|---|
Hari | 1 |
Subbu | 2 |
Srinu | 3 |
Step 4
Query
Create table marks(regno number(10), total number(10));
Output
table created.
Step 5
Query
insert into marks values (1, 400); Insert into marks values(2,450); Insert into marks values (3, 300);
Output
3 rows created.
Step 6
Query
select * from marks;
Output
Regno | Total |
---|---|
1 | 400 |
2 | 450 |
3 | 300 |
Natural join − If we join two tables on equal condition then it is called natural join or equi join. Generally, join is referred to as natural join.
The syntax for natural join is as follows −
select columnname(s) from tablename1 join tablename2 on tablename1.columnname=tablename2.columnname;
Step 7
Query
Select * from student join marks on student.regno = marks.regno;
Output
Name | Regno | Regno | Total |
---|---|---|---|
Hari | 1 | 1 | 400 |
Subbu | 2 | 2 | 450 |
Left join − It is an extension of natural join to deal with missing values of relation.
Step 8
Query
Select * from student left join marks on student.regno = marks.regno;
Output
Name | Regno | Regno | Total |
---|---|---|---|
Hari | 1 | 1 | 400 |
Subbu | 2 | 2 | 450 |
Srinu | 3 | NULL | NULL |
Right join − Here all the tuples of table2 (right table) appear in the output.
The mismatching values of table1 are filled with NULL
Step 9
Query
Select * from student right join marks on student.regno = marks.regno;
Output
Name | Regno | Regno | Total |
---|---|---|---|
Hari | 1 | 1 | 400 |
Subbu | 2 | 2 | 450 |
NULL | NULL | NULL | NULL |
Full join − Full outer join=left outer join U right outer join
Step 10
Query
Select * from student full join on student.regno = marks.regno;
Output
Name | Regno | Regno | Total |
---|---|---|---|
Hari | 1 | 1 | 400 |
Subbu | 2 | 2 | 450 |
Srinu | 3 | NULL | NULL |
NULL | NULL | 5 | 350 |