Types of Joins in DBMS

In this video, we will be discussing the different types of joins in detail. But before that let us know why we need Join?

Join needs two or more relations to perform an operation. Hence it is a binary operator that allows merging relations. The aim of using join is to combine the information from two or more tables (Relations).

Joins are basically of two types, Inner Joins and Outer Joins, then these types are further classified into sub-types, which are as follows:
1) Inner Join:
- Theta
- Natural
- Equi

2) Outer Join:
- Left
- Right
- Full

Inner Join: The result of this joins the tuples from both the relations which satisfy the given predicate. It can be thought of as a default join-type.

Inner Join is of the following types:
- Theta Join: Theta Join is used to combine two relations based on the predicate or condition. This condition is presented as theta. All comparison operators can be used by theta join.
- Natural Join: Natural Join does not include any of the comparison operators. Here common attribute should be there between two relations that share a common domain as well.
- EQUI Join: EQUI Join is used by putting the equivalence conditions in Theta join.

Outer Join: This doesn’t need any common record. It preserves each record from relation even if there is no other matching record exists.

Outer Join is of following types:
- Left Outer Join: The result of this join is the tuples from the table on the left even if no matching tuples have been found in the table on the right. An unmatched record is found in the table on the right, NULL is returned.
- Right Outer Join: The result of this join is the tuples from the table on the right even if no matching rows have been found in the table on the left. An unmatched record is found in the table on the left, NULL is returned.
- Full Outer Join: In a Full Outer Join, all tuples from both relations are included in the result, despite of the matching condition.

SQL Join (Inner left-right & full joins)- https://www.geeksforgeeks.org/sql-join-set-1-inner-left-right-and-full-joins/