Different types of joins in sql server

Sql joins are used to retrieve data from multiple tables. Joins allow you to view data from related tables in a single result set. Depending on the requirements to view data from multiple table, you can apply different types of joins, Such as inner join, cross join, equi join or self join

Examples Table for joins

The Different Types of Joins in SQL Server

• Inner join or Equi join
• Self Join
• Outer Join
• Cross join

Using an Inner join (Equi Join)

An inner join retrieves record from multiple tables by using a comparison operator on a common column. When an inner join is applied, only rows with values satisfying the join condition in the common column are displayed

Example for Inner Join 
SELECT Stud.Studid,Stud.StudFirstName,Stud.StudLastName, Dept.DepartmentName 
FROM Student Stud  INNER JOIN Department dept ON Stud.Departmentid=Dept.Departmenttid

Output for  Inner Join: 

Using an Self Join 
In a self join a table is joined with itself. As a result, one row in a table correlates with other rows in the same table.

Example for Self Join 
SELECT Stud1.Studid,Stud1.StudFirstName+' '+Stud1.StudLastName as StudentName, 
Stud2.ManagerName as ManagerName   FROM Student Stud1 
INNER JOIN Student Stud2  ON Stud1.Departmentid=Stud2.Studid

Output for Self Join:

The Outer join can be of three types

• Left Outer Join
• Right Outer Join
• Full Outer Join

Using an Left Outer Join

A left outer join returns all rows from the table specified on the left side of the LEFT OUTER JOIN keyword and the matching rows are not found in the table specified on the right side, NULL values are displayed in the column that get data from the table specified on the right side

Example for LEFT OUTER JOIN :
SELECT Stud.Studid,Stud.StudFirstName,Stud.StudLastName, Dept.DepartmentName 

FROM Student Stud  LEFT OUTER JOIN Department dept ON Stud.Departmentid=Dept.Departmenttid

Output for LEFT OUTER JOIN :

Using an Right Outer Join

A Right outer join returns all rows from the table specified on the right side of the RIGHT OUTER JOIN keyword and the matching rows from the table specified on the left side

Example for RIGHT OUTER JOIN

SELECT Stud.Studid,Stud.StudFirstName,Stud.StudLastName, Dept.DepartmentName 

FROM Student Stud  RIGHT OUTER JOIN Department dept ON Stud.Departmentid=Dept.Departmenttid

Output for RIGHT OUTER JOIN:

Using an FULL OUTER JOIN 

A Full outer join is a combination of left outer join and right outer join. This join returns all the matching and non matching rows from both the tables. However, the matching records are displayed only once. In case of non-matching rows, a NULL value is displayed for the columns for which data is not available

Example for FULL OUTER JOIN

SELECT Stud.Studid,Stud.StudFirstName,Stud.StudLastName, Dept.DepartmentName 

FROM Student Stud  FULL OUTER JOIN Department dept ON Stud.Departmentid=Dept.Departmenttid

Output for FULL OUTER JOIN

Using an Cross Join 
A cross join, also know as Cartesian product, between two tables join each row from one table with each row of the other table

Example for Cross Join
SELECT Stud.Studid,Stud.StudFirstName,Stud.StudLastName, Dept.DepartmentName  
FROM Student Stud CROSS JOIN Department dept

Output for Cross Join

Tutorial for Sql Joins