Joins in DBMS

In this article, we will learn about a very important topic of Database Management System (DBMS) called ‘join’, A join is an operation that combines the rows of two or more tables based on related columns. This operation is used for retrieving the data from multiple tables simultaneously using common columns of tables. From this article, you will learn about the syntax of join, types of join, examples, and FAQs.

What is Join?

Join is an operation in DBMS(Database Management System) that combines the row of two or more tables based on related columns between them. The main purpose of Join is to retrieve the data from multiple tables in other words Join is used to perform multi-table query. It is denoted by ⨝.

Syntax 1

R3 <- ⨝^(R1) <join_condition> ^(R2)

where R1 and R2 are two relations to be join and R3 is a relation that will holds the result of join operation.

Example

Temp <- ⨝^(student) _{S.roll==E.roll}^(Exam)

where S and E are alias of the student and exam respectively

Types of Join

1. Inner Join
2. Outer join

Inner join

Inner Join is a join operation in DBMS that combines two or more table based on related columns and return only rows that have matching values among tables.Inner join of two types.

• Equi Join
• Natural Join

Equi Join

Equi Join is a type of Inner join in which we use euivalence(‘=’) condition in join condition

Example:

Table A

Column A	Column B
a	a
a	b

Table B

Column A	Column B
a	a
a	c

A ⨝ _{A.Column B = B.Column B} (B)

Result:

Column A	Column B
a	a

Natural Join

Natural join is a type of inner join in which we not need of any comparison operators. In natural join columns should have the same name and domain. There should be at least one common attribute between two tables.

Eample:

Table A

Number	Square
2	4
3	9

Table B

Number	Cube
2	8
3	27

A ⨝ B

Number	Square	Cube
2	4	8
3	9	27

Outer Join

Outer join is a type of join that retrieve matching as well as non-maching records from related tables.

There three types of outer join

• Left outer join
• Right outer join
• Full outer join

Left Outer Join

It is also called left join. This type of outer join retrieve all records from left table and retrive maching record from right table.

Example:

Table A

Number	Square
2	4
3	9
4	16

Table B

Number	Cube
2	8
3	27
5	125

A ⟕ B

Result:

Number	Square	Cube
2	4	8
3	9	27
4	16	–

Right Outer Join

It is also called right join. This type of outer join retrieve all records from right table and retrive maching record from right table.

Example:

Table A and Table B are same as in left outer join

A ⟖ B

Number	Square	Cube
2	4	8
3	9	27
5	–	125

Full Outer Join

In full outer join all the rows from both table are inserted in result table

Eaxmple:

Table A and Table B are same as in left outer join

A ⟗ B

Result:

Number	Square	Cube
2	4	8
3	9	27
4	16	–
5	–	125

Conclusion

In the field of Database Management System, Join is the very important tool for retriving the data from the multiple tables simultaneously . Learning and understanding about join syntax and types of syntax including inner and outer joins is very efficient for writing query for database and extarcting valuable understand.

FAQs on Joins

Q.1: What is the primary purpose of a join operation in DBMS?

Answer:

The Primary purpose of join operation in DBMS is to two or more tables based on related columns , enabling the retrival of data from multiple tables in single query.

Q.2: What is the difference between an inner join and an outer join?

Answer:

An inner join returns the only matching values that available in both tables while outer join returns all the data which is maching as well as non-maching values from the both tables.

Q.3: When is an Equi Join used in a database query?

Answer:

An Equi Join is a type of inner join that uses equivalence (usually ‘=’) conditions in the join condition. It is used when you want to combine rows where specific columns have equal values.

Q.4: What is a Natural Join, and when is it used?

Answer:

A Natural Join is a type of inner join where no comparison operators are needed. It is used when columns in the two tables have the same name and domain, and there is at least one common attribute between them.

Q.5: In a full outer join, what does the result table include?

Answer:

In a full outer join, the result table includes all the rows from both tables, whether they have matching values or not.