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

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

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

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

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

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