by Jasleen Chhabra | Updated on 29 September 2024

Join Operations in DBMS 

In relational databases, Join operations combine tuples from different relations based on a common condition, allowing you to retrieve related data from multiple tables. This operation is vital for working with relational databases because it helps to merge data from multiple sources based on specified conditions.

In this blog, we'll explore various types of Join operations in relational databases, their syntax, and practical examples using simple tables.

---

1. Join Operation:

A Join operation combines tuples from two or more relations, providing results based on a condition that is specified between the attributes of the tables involved. It is denoted by the symbol ?.

Example:

Let's say we have two tables: EMPLOYEE and DEPARTMENT.

EMP_ID	EMP_NAME	DEPT_ID
101	Alice	D01
102	Bob	D02
103	Charlie	D03

DEPT_ID	DEPT_NAME
D01	Marketing
D02	Finance
D03	HR

Operation:

EMPLOYEE ? DEPARTMENT
Here, the Join condition is that both tables share the DEPT_ID.

Output:

EMP_ID	EMP_NAME	DEPT_ID	DEPT_NAME
101	Alice	D01	Marketing
102	Bob	D02	Finance
103	Charlie	D03	HR

---

2. Natural Join:

A Natural Join automatically joins two tables based on all columns that have the same name and data type. It is denoted by ?.

Example:

We have two tables STUDENT and GRADE:

STUDENT_ID	STUDENT_NAME	GRADE_ID
1	John	G1
2	Maria	G2

GRADE_ID	GRADE_NAME
G1	A
G2	B

Operation:

STUDENT ? GRADE

Output:

STUDENT_ID	STUDENT_NAME	GRADE_ID	GRADE_NAME
1	John	G1	A
2	Maria	G2	B

---

3. Outer Join:

Outer joins are useful when we want to include records that do not have a matching record in the other table. There are three main types of outer joins: Left Outer Join, Right Outer Join, and Full Outer Join.

---

a. Left Outer Join (?):

This join returns all records from the left table and the matched records from the right table. Records that don’t have a match in the right table will contain NULL for missing values.

Example:

EMPLOYEE and SALARY tables:

EMP_ID	EMP_NAME
1	Sophie
2	Chris
3	Emma

EMP_ID	SALARY
1	50000
2	60000

Operation:

EMPLOYEE ? SALARY

Output:

EMP_ID	EMP_NAME	SALARY
1	Sophie	50000
2	Chris	60000
3	Emma	NULL

---

b. Right Outer Join (?):

This join returns all records from the right table and the matched records from the left table. Records that don’t have a match in the left table will contain NULL.

Example:

Same EMPLOYEE and SALARY tables as above:

Operation:

EMPLOYEE ? SALARY

Output:

EMP_ID	EMP_NAME	SALARY
1	Sophie	50000
2	Chris	60000
3	NULL	70000

---

c. Full Outer Join (?):

The Full Outer Join returns all records when there is a match in either the left or right table. If there is no match, NULL values are filled in.

Example:

EMP_ID	EMP_NAME
1	Alice
2	Bob

EMP_ID	SALARY
1	55000
3	70000

Operation:

EMPLOYEE ? SALARY

Output:

EMP_ID	EMP_NAME	SALARY
1	Alice	55000
2	Bob	NULL
3	NULL	70000

---

4. Equi Join:

An Equi Join uses the equality operator (=) to match records between two tables. It is the most basic form of a join and returns all records where the specified columns have matching values.

Example:

STUDENT and COURSE tables:

STUDENT_ID	STUDENT_NAME
101	John
102	Alice

COURSE_ID	STUDENT_ID	COURSE_NAME
C1	101	Physics
C2	102	Chemistry

Operation:

STUDENT ? COURSE

Output:

STUDENT_ID	STUDENT_NAME	COURSE_NAME
101	John	Physics
102	Alice	Chemistry

---

5. Self Join:

A Self Join joins a table with itself. It is useful when you want to compare rows within the same table.

Example:

EMPLOYEE table with manager relationships:

EMP_ID	EMP_NAME	MANAGER_ID
1	Jack	2
2	Emma	3
3	Harry	NULL

Operation:

EMPLOYEE E1 ? EMPLOYEE E2 (E1.MANAGER_ID = E2.EMP_ID)

Output:

EMP_ID	EMP_NAME	MANAGER_NAME
1	Jack	Emma
2	Emma	Harry

---

Conclusion:

Join operations are crucial for combining data from multiple tables based on specific conditions, enabling complex queries in relational databases. We covered the various types of join operations: Natural Join, Outer Join (Left, Right, Full), Equi Join, and Self Join. Understanding these operations allows for more efficient and flexible data retrieval from multiple relations.

---