Inner join Theta join Equi join Natural join || Lesson 50 || DBMS || Learning Monkey ||

Here we will discuss about Inner join Theta join Equi join Natural join
Before understanding the inner join we will understand what is meant by the join operator.

The concept of join has been clearly explained in SQL.

Join ( ) in Relational  Algebra:

Join is a binary relational operator denoted by is used to combine related tuples from two relations into single longer tuples.

We will try to understand the concept of joins
For example, consider the join statement given below.

DEPT (Mgr_SSN = SSN ) EMP

How this join statement will work is, the first Mgr_SSN value (123) of the DEPT table is compared with every value of SSN.

If Mgr_SSN is equal to SSN in the EMP Table then that row in the EMP table and the row in the DEPT table will get combined and formed into one single tuple.


Similarly, the second-row Mgr_SSN value (789) of the DEPT table is compared with every value of SSN.
The same join operation can be done by using the cartesian product, but it has to be used with the select operator as shown below.

(Mgr_SSN = SSN ) (DEPT EMP)

First, the cartesian product of DEPT and EMP is done.
Now the select condition (Mgr_SSN = SSN ) will be applied on each and every row.

If (Mgr_SSN = SSN ) then those rows will be fetched out.
Various Forms of the Join Operations:

There are various forms of operators.

1. Inner Joins

2. Outer Joins

Inner Joins:

In Inner join, only those tuples that satisfy the matching criteria are included while the rest will be excluded.

Types of inner joins are 1. Theta Join 2. Equi Join 3. Natural join.

Outer Joins:

In Outer join, along with the tuples that satisfy the matching criteria, we also include the tuples that do not match the criteria.

Types of outer joins are 1. Left outer join 2. Right outer join. 3. Full join

In this post, we will discuss only the Inner joins.

In our next post, we will clearly discuss Outer joins.

Theta Join:

In general, the join condition (Ai Bj) is of the form R (Ai Bj) S, Ai is an attribute of R and Bj is an attribute of S. Ai and Bj have the same domain and is one of the comparisons operator { }. A join with such genera condition is theta join.

This means if the operator in the join condition is any of the following } then it is theta join.

Equi Join:

If the comparison operator used in the join condition is only '='. Then it is Equi join.

Natural join (*):

If the join operation is naturally made on the attribute with the same name and type is called natural join.

For example, DEPT * EMP is natural join and the join condition is applied to the attributes with the same name and type.

In our case, it applied to Dno of DEPT and EMP.

In this post, the discussion on Inner join Theta join Equi join Natural join is done clearly.

In our next post, we will clearly discuss about outer joins.



Link for our website: https://learningmonkey.in

Follow us on Facebook @   / learningmonkey  

Follow us on Instagram @   / learningmonkey1  

Follow us on Twitter @   / _learningmonkey  

Mail us @ [email protected]