Difference between Equi Join, Natural Join and Cross Join in sql

Different types of Inner Join are :
1) Equi Join
2) Natural Join
3) Cross Join

One of the most common interview questions that is being asked is explain Equi Join, Natural Join and Cross Join in sql. I will break the question in different meaningful easy to understand concepts.

Consider the below tables
Employee

EmpId	EmpName
1	John
2	David
3	Peter
4	Eric

Area

AreaId	AreaName	EmpId
3	New York	1
4	Canada	3
5	Australia	3
6	England	4

Equi join
Equi join is the first type of Inner Join.
It joins two or more tables where the specified columns are equal.
In this type of join, you can only use '=' operator in comparing the columns.
Operators like '>', '<' are not allowed in this type of join.

Example query: Select * from Employee emp
INNER JOIN Area area on area.EmpId = emp.EmpId

Result:

EmpId	EmpName	AreaId	AreaName	EmpId
3	Peter	4	Canada	3
3	Peter	5	Australia	3
4	Eric	6	England	4

As shown above, EmpId column is appearing twice.

---

---

---

---

Natural join
It is same as equi­join but the difference is that in natural join, the common attribute appears only once.

Example query: Select * from Employee emp
NATURAL JOIN Area area on area.EmpId = emp.EmpId

Result:

EmpId	EmpName	AreaId	AreaName
3	Peter	4	Canada
3	Peter	5	Australia
4	Eric	6	England

As shown above, EmpId column is appearing only once.

---

---

---

---

Cross join
A cross join that produces Cartesian product of the tables.. The size of a Cartesian product is the number of the rows in first table multiplied by the number of rows in the second table.

Example query: Select * from Employee emp
CROSS JOIN Area area

Result:

EmpId	EmpName	AreaId	AreaName	EmpId
1	John	3	New York	1
1	John	4	Canada	3
1	John	5	Australia	3
1	John	6	England	4
2	David	3	New York	1
2	David	4	Canada	3
2	David	5	Australia	3
2	David	6	England	4
3	Peter	3	New York	1
3	Peter	4	Canada	3
3	Peter	5	Australia	3
3	Peter	6	England	4
4	Eric	3	New York	1
4	Eric	4	Canada	3
4	Eric	5	Australia	3
4	Eric	6	England	4

As shown in the results above, the number of rows returned in the result are 4 * 4 = 16.