Joins in SQL

What are Joins ?

Joins may be defined as SQL queries, which are used to combine rows from two or more tables or views based on the relationship between the tables.

Why and When Joins are required ?

In our earlier posts , we have read about the process of Normalization ,to reduce the redundancy. As a result of normalization the database is divided into multiple tables. if we look from ETL prospective for e.g. In Datawarehouse systems , in order to generate report , data is required from multiple tables . This is where joins comes into picture to combine data from different tables.

Types of Joins :

1. Natural Join.
2. Equi Join / Inner Join.
3. Non Equi Join.
4. Cross Join.
5. Self Join.
6. Outer Join

Cross Join :

1. It Joins two or more tables and returns a cartesian product of the joined tables.
2. Whether records in both tables are matching or not , it will combine each row of first table with each row of the second table.

Self Join :

Outer Join :

 Natural Join , Inner Join , Equi Join : Same or different ??? 

There are lot of discussions regarding this, Some say they are equal and some say they are different. Actually, there is a very thin line difference between them.

Natural Join :

Select * from <table1> NATURAL JOIN <table2> on  <table1.col1> =  <table2.col1>;
e.g.
Select * from EMP NATURAL JOIN DEPT on EMP.Deptno = DEPT.Deptno;

1. It joins two or more tables and the output is the common rows of both the tables .

2. The Common Column between the two tables is displayed as one single column i.e. appears only once in the        output resultset.

3.Only Equal  ‘= ‘ Operator can be used while comparing the tables in Natural Join.

Inner Join :

Select * from INNER JOIN on = ; e.g. Select * from EMP INNER JOIN DEPT on EMP.Deptno = DEPT.Deptno;

Select * from <table1> INNER JOIN <table2> on  <table1.col1> <  <table2.col1>;

Select * from <table1> INNER JOIN <table2> on  <table1.col1> >  <table2.col1>;

1. It joins two or more tables and the output is the common rows of both the tables.

2. The Common Column between the two tables are displayed as two columns i.e. appears twice in the output resultset.

3.Besides ‘=’ other operators ‘>’ ,'<‘ can also be used while comparing the tables in Inner Join.

Equi Join :

Select * from <table1> EQUI JOIN <table2> on  <table1.col1> =  <table2.col1>;
e.g.
Select * from EMP EQUI JOIN DEPT on EMP.Deptno = DEPT.Deptno;

1. It joins two or more tables and the output is the common rows of both the tables.

2. The Common Column between the two tables are displayed as two columns i.e. appears twice in the output resultset.

3. Equi join can be considered as part of Inner join but here only ‘=’ operator can be used while comparing the     tables .