• Data is stored in multiple tables that are related to each other in relational data bases like SQL Server, MySQL etc with a common key value.
• When we need to get row by combining one or more tables based on common fields between them can be achieved using a SQL Join.
• When we need to extract records from two or more tables into a new table, JOIN clause helps us to acheive this based on certain condition.
• Based on logical relationship between tables join clause helps us to query and access data from multiple tables.
• In other words, Join show how data from a row can be fetched from one table to another table.

Different types of Joins are:

• Inner join/Simple Join
• Left Outer join/Left Join
• Right Outer Join/Right join
• Full Outer join
• Cross Join
• Self Join

Inner Join

• In SQL if a defined condition is valid the inner join will select all the matching rows and columns from both tables.

Syntax

Select column_1, column_2 column_3 from table_1 innerjoin table_2 ON table_1.column = table_2.column;

Left Join

• In left join all records are retrived from left table(table1) and matched rows and column from right table(table2).
• If there is no matching rows or columns, left join returns NULL.

Syntax

Select column_1, column_2, columns from Table_1 left join table_2 ON table_1.column_name = table_2.column name.

Right Join or Right Outer Join

• Records from the right table are retrived in RIGHT JOIN and the matched rows or columns from the left table(table1).
• If there is no matching rows or columns it will return NULL.

Syntax

Select column_1, column_2, cloumn(s) FROM table_1 right join table_2 ON table_1.column_name= table2.column_name.

Full Join or Full Outer Join

• This join is a combination of both Left join and right join.
• The joined tables returns all the records from both the tables.
• If no matches are found in the table, this join will return a NULL Value.
• It is also called as Full Outer Join.

Syntax

Select column_1, column_2, column(s) from table_1 Full join table_2 ON table_1.column_name = table_2.column_name;

Cross Join

• It is also known as Cartesian Join which returns cartesian product of two or more joined tables.
• Cross join produces a table that merges each rows from the first table with each rows of second table.
• No condition ir required for CROSS JOIN.

Syntax

Select * from table_1 cross join table_2;  
Or,
Select column1, column2, column3 from table_1, table_2; 

Self Join

• Tables formed by joining itself is called Self Join.
• It makes a temporary naming of atleast one table in SQL Statement.

Syntax

Select column1, column2, column(s) from table_1 Tbl1, table_2 Tbl2 where condition;