DBMS Joins: Theta, Natural, Equi

[sophryu99]

Join in DBMS is a binary operation which allows you to combine join product and selection in one single statement. The goal of creating a join condition is that it helps you to combine the data from two or more DBMS tables. The tables in DBMS are associated using the primary key and foreign keys.

Types of Join

There are mainly two types of joins in DBMS:

1. Inner Joins: Theta, Natural, EQUI
2. Outer Join: Left, Right, Full

[sophryu99]

Inner Join

Inner Join is used to return rows from both tables which satisfy the given condition. It is the most widely used join operation and can be considered as a default join-type

Inner Join is further divided into three subtypes:

• Theta join
• Natural join
• EQUI join

[sophryu99]

Theta Join

Theta Join allows you to merge two tables based on the condition represented by theta. Theta joins work for all comparison operators. It is denoted by symbol θ. The general case of JOIN operation is called a Theta join.

Syntax:

A ⋈θ B

Example

Table A		Table B
column 1	column 2	column 1	column 2
1	1	1	1
1	2	1	3

[sophryu99]

EQUI Join

EQUI Join is done when a Theta join uses only the equivalence condition. EQUI join is the most difficult operation to implement efficiently in an RDBMS, and one reason why RDBMS have essential performance problems.

For example:

A ⋈ A.column 2 =  B.column 2 (B)

A ⋈ A.column 2 = B.column 2 (B)
column 1	column 2
1	1

[sophryu99]

Natural Join (⋈)

Natural Join does not utilize any of the comparison operators. In this type of join, the attributes should have the same name and domain. In Natural Join, there should be at least one common attribute between two relations.

It performs selection forming equality on those attributes which appear in both relations and eliminates the duplicate attributes.

Example:

Consider the following two tables

C
Num	Square
2	4
3	9

C ⋈ D

[sophryu99]

Outer Join

An Outer Join doesn’t require each record in the two join tables to have a matching record. In this type of join, the table retains each record even if no other matching record exists.

Three types of Outer Joins are:

• Left Outer Join
• Right Outer Join
• Full Outer Join