Explain join operations with the help of an example in DBMS

DBMSDatabaseBig Data Analytics

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
raja
Published on 06-Jul-2021 12:44:22
Advertisements