What are the different types of Joins used in SQL

SQL Joins :

Join clause is used to combine row from two or more table based on the matching column from the them.

There are 4 main type of join in SQL.

                          1. INNER JOIN                                

                          2. LEFT (OUTER) JOIN

                          3. RIGHT (OUTER) JOIN

                          4. FULL (OUTER) JOIN

 1. INNER JOIN: This join returns that have matching values from both the table. 

this join also known as SIMPLE JOIN.

Syntax:

Select Column_name(s) from Table 1 INNER JOIN Table 2 ON Table 1.Column_name = Table 2.Column_name

SQL Statement

Select * from Emp INNER JOIN Dept ON Emp.ID = Dept.ID

2. LEFT (OUTER) JOIN:  This Join returns all record from Left table and its matched row from Right table.  

Syntax:

Select Column_name(s) from Table 1 LEFT JOIN Table 2 ON Table 1.Column_name = Table 2.Column_name

SQL Statement

Select * from Emp LEFT JOIN Dept ON Emp.ID = Dept.ID

3. RIGHT (OUTER) JOIN: This Join returns all records from Right table and its matched row from Left table. 

Syntax:

Select Column_name(s) from Table 1 RIGHT JOIN Table 2 ON Table 1.Column_name = Table 2.Column_name

SQL Statement

Select * from Emp RIGHT JOIN Dept ON Emp.ID = Dept.ID

4. FULL (OUTER) JOIN: This Join returns all records when there is match either in the RIGHT table or in the LEFT table.

Syntax:

Select Column_name(s) from Table 1 FULL OUTER JOIN Table 2 ON Table 1.Column_name = Table 2.Column_name

SQL Statement

Select * from Emp FULL OUTER JOIN Dept ON Emp.ID = Dept.ID