Sql server joins are used to combine result data from two or more tables. Often we need to display data from two tables. Where sql server joins are very useful.
There are three types of sql server joins.
1) Inner Join
2) Outer Join
3) Cross Join
1. Inner Join:
Inner join will display data based on common columns of two tables. It will find
the data from both tables based on join condition.This join take every records of left table and looks in right table based on join conditions.
2. Outer Joins:
There are there types of outer joins.
2.1 Left Outer Join
2.2 Right Outer Join
2.3 Full Outer Join
2.1 Left Outer Join:
Left outer join will display all the rows from left table of join and display data from right table based on join condition. First it will display every row of left table and looks into the right table to display data based on join condition. If matching records are found then it will display data of right table with left table data otherwise put a null values instead of right table data.
2.2 Right Outer Join:
Right outer join will display all the rows from right table of join and display data from left table based on join condition. First It will display every row of right table and looks into the left table to display data based on join condition. If matching records are found then it will display data of left table with right table data otherwise put a null value instead of left table data.
2.3 Full Outer Join:
This joins are combination of both left outer join and right outer join. First it will put data like left outer join and it will put data like right outer join. If respective records are found in opposite table then it will display data otherwise it will display null values.
3 Cross Join:
Cross join is slightly different then full outer join. It does not have join condition it is not required in cross join. It will display data like Cartesian product of the tables. For example if table 1 have 3 rows and table 2 have 7 rows. Then it will display 21 rows from both tables.
First it will select first row of table 1 and first row of table 2
then it will select first row of table 1 and second row of table 2
then it will select first row of table 1 and third row of table 2
so on it will display data.