As we know we store data in multiple table for normalization purpose. To retrieve this data we use JOIN in SQL server.


Below are types of JOIN available in SQL Server available.


To retrieve data from more than one table we use the INNER JOIN. It return common data from multiple table.

types of join in sql server - inner join

It return all data from left side table and common data from right side table. If right side table data not available it will show null value.

types of join in sql server - left join

It return all data from right side table and common data from left side table. If left side table data not available it will show null value.

types of join in sql server - right join

Some time it happen we need join on same table then we use the SELF JOIN. Example, In employee table having information of employee and there manager. Because it happens that one employee can be manager of another.
So in this case we required SELF JOIN.


types of join in sql server - self join

It return the all record from all tables.It join all row from one table with all row with right side table.
Example if table A is having 3 record and table B having 2 record then cross join return 6 rows.


types of join in sql server - cross join