SQL Joins

• Join is useful to fetching records from multiple tables with reference to common column between them.
• SQL Joins are used to retrieve data from multiple tables.
• Join keyword is used in SQL queries for joining two or more tables.

Types of Joins:

Inner Join:

• Select the records that have matching values from the both tables.
• Whenever we use Inner Join clause, we normally think about intersection. 

Syntax:

Select table1.column1, table2.column2...

From table1 

Inner Join table2 

On table1.common_field = table2.common_field;

Left Outer Join:

• It returns a  result table with all records from the left table with only matching records from the right table.

Syntax:

Select table1.column1, table2.column2...

From table1 

Left Join table2 

On table1.common_field = table2.common_field;

Right Outer Join:

• It returns a result table with only matched records from the left table and all records from the right table.

Syntax:

Select table1.column1, table2.column2...

From table1 

Right Join table2 

On table1.common_field = table2.common_field;

Full Outer Join:

• The Full Outer Join returns a result table with the matched data of two tables then remaining rows of both left table and right table.
• Whenever we use Full Outer Join clause, we normally think about Union. 

Syntax:

Select table1.column1, table2.column2...

From table1 

Full Join table2 

On table1.common_field = table2.common_field;

Cross Join:

• A Cross Join that produces Cartesian product of the tables that are involved in the join.
• The size of a Cartesian is the number of the rows in the first table multiplied by the  number or rows in the second table.

Syntax:

Select * from table1 Cross Join table2;

or

Select * from table1, table2;

Self Join:

• Joining the table itself is called Self Join.
• Self Join is used to retrieve the records having some relation or similarity with other records in the same table.
• Here, we need to use aliases for the same table to set a self join between single table and retrieve records satisfying the condition in where clause.

Syntax:

Select a.cloumn_name, b.column_name...

From table1 a, table1 b

Where a.common_field = b.common_field;