Types of Joins

Types of Joins in Relational Algebra

A join operation in relational algebra combines rows from two or more relations based on a related column between them. Joins are essential in relational databases for combining data from multiple tables based on logical relationships between them. There are several types of joins in relational algebra, each with different behaviors for matching rows.

Here are the primary types of joins:

1. Inner Join
2. Natural Join
3. Theta Join
4. Equi Join
5. Outer Join (Left, Right, Full)
6. Self Join

Let's discuss each of these types in detail:

---

1. Inner Join

An Inner Join (often just called "Join") returns only the rows where there is a match between the two relations based on a specified condition. It combines rows from two relations if the condition (usually an equality between specified attributes) is satisfied. If there is no match, the row is not included in the result.

• Notation: R ⨝ S
• Description: The result of an inner join is the set of tuples that are present in both relations R and S, where a given condition (usually equality on common attributes) holds.

Example:

Consider two relations, Employees and Departments:

Employees:

EmployeeID	Name	DeptID
1	Alice	D1
2	Bob	D2
3	Charlie	D3

Departments:

DeptID	DeptName
D1	HR
D2	IT
D4	Finance

Inner Join on DeptID:

Employees ⨝ Departments

Result:

EmployeeID	Name	DeptID	DeptName
1	Alice	D1	HR
2	Bob	D2	IT

• Only the rows where DeptID matches in both relations are included in the result.
• The row where DeptID = D3 in Employees and DeptID = D4 in Departments are not included because there is no matching department for them.

---

2. Natural Join

A Natural Join is a specific type of inner join that automatically matches columns with the same name in both relations. It eliminates duplicate columns from the result.

• Notation: R ⨝ S
• Description: A natural join automatically joins tables based on all columns that have the same name and removes duplicates of these columns in the result.

Example:

Consider the following relations:

Employees:

EmployeeID	Name	DeptID
1	Alice	D1
2	Bob	D2
3	Charlie	D3

Departments:

DeptID	DeptName
D1	HR
D2	IT
D4	Finance

A Natural Join on DeptID will be:

Employees ⨝ Departments

Result:

EmployeeID	Name	DeptID	DeptName
1	Alice	D1	HR
2	Bob	D2	IT

• The join automatically matches the DeptID column and removes the duplicate DeptID column from the result.

---

3. Theta Join

A Theta Join is a join where the condition is not restricted to equality. Instead, it can be any condition based on comparison operators like =, >, <, >=, <=, !=, etc.

• Notation: R ⨝θ S, where θ is the condition (e.g., =, <, >, etc.).
• Description: The result of a theta join is the set of tuples that satisfy a specified condition between two relations.

Example:

Consider the following relations:

Employees:

EmployeeID	Name	Salary
1	Alice	50000
2	Bob	60000
3	Charlie	70000

Departments:

DeptID	SalaryLimit
D1	60000
D2	65000

A Theta Join on the condition Salary > SalaryLimit would be:

Employees ⨝ (Salary > SalaryLimit) Departments

Result:

EmployeeID	Name	Salary	DeptID	SalaryLimit
2	Bob	60000	D1	60000
3	Charlie	70000	D2	65000

• The result includes employees whose salary is greater than the SalaryLimit of the department.

---

4. Equi Join

An Equi Join is a type of theta join where the condition is specifically an equality condition (=) between columns from two relations.

• Notation: R ⨝ (R.A = S.B) S
• Description: It’s essentially an inner join where the condition is an equality between specified attributes.

Example:

Using the Employees and Departments relations:

Employees ⨝ (Employees.DeptID = Departments.DeptID) Departments

Result:

EmployeeID	Name	DeptID	DeptName
1	Alice	D1	HR
2	Bob	D2	IT

• The result is the same as an inner join, but specifically with an equality condition between matching columns.

---

5. Outer Joins

Outer Joins include Left Outer Join, Right Outer Join, and Full Outer Join. These joins return all tuples from one relation, and the matching tuples from the other relation. If there is no match, the result will include NULL values for the attributes of the relation that does not have a matching row.

5.1 Left Outer Join

The Left Outer Join returns all rows from the left relation and the matched rows from the right relation. If there is no match, the result will contain NULL for columns from the right relation.

• Notation: R ⟕ S
• Description: The left relation's tuples are returned, and for those that don't have a match in the right relation, NULL values are returned for the right relation's columns.

Example:

Employees ⟕ Departments

Result:

EmployeeID	Name	DeptID	DeptName
1	Alice	D1	HR
2	Bob	D2	IT
3	Charlie	D3	NULL

• Since D3 is not in the Departments table, the DeptName is NULL for Charlie.

---

5.2 Right Outer Join

The Right Outer Join is similar to the left outer join, but it returns all rows from the right relation and the matched rows from the left relation. If no match exists, NULL values are used for columns from the left relation.

• Notation: R ⟖ S
• Description: All tuples from the right relation are included, with matching tuples from the left relation. If there is no match, the left relation's attributes are NULL.

Example:

Employees ⟖ Departments

Result:

EmployeeID	Name	DeptID	DeptName
1	Alice	D1	HR
2	Bob	D2	IT
NULL	NULL	D4	Finance

• The department D4 appears in the result even though no employee is associated with it, with NULL values for the employee's columns.

---

5.3 Full Outer Join

The Full Outer Join returns all rows from both relations. If there is no match, NULL values are placed in the columns of the relation with no matching tuple.

• Notation: R ⟗ S
• Description: Combines the effects of both left and right outer joins, returning all rows from both relations.

Example:

Employees ⟗ Departments

Result:

EmployeeID	Name	DeptID	DeptName
1	Alice	D1	HR
2	Bob	D2	IT
3	Charlie	D3	NULL
NULL	NULL	D4	Finance

• All rows from both relations are returned. For non-matching rows, NULL values are placed where there are no matches.

---

6. Self Join

A Self Join is a join where a relation is joined with itself. This is useful when you need to compare rows within the same relation, such as finding employees who work in the same department.

• Notation: R ⨝ R
• Description: A relation is joined with itself based on a condition.

Example:

Employees ⨝ (Employees.DeptID = Employees.DeptID) Employees

This operation would compare all employees with each other who belong to the same department.

---

Summary

1. Inner Join: Returns matching rows based on a condition.
2. Natural Join: A type of inner join that automatically matches columns with the same name.
3. Theta Join: Join based on a condition using any comparison operator.
4. Equi Join: A type of theta join where the condition is specifically an equality.
5. Outer Join: Includes Left, Right, and Full joins, returning unmatched rows with NULL values.
6. Self Join: Joins a relation with itself.

Each type of join has its specific use case depending on the nature of the data and the relationship between the tables.