Joins in SQL

In today's article I will show you a simple tutorial with example for what are the different type of joins in SQL Server. How you can use and apply joins in SQL server. The different type of types of joins in SQL server are Inner join, Left outer join, Right outer join, Full outer join, Cross join.

What is Join In SQL?

Joins in SQL server is a logical combine of multiple columns to get the relational data from one or multiple tables in SQL server table. By using = (EQUAL) and <> (OR) operator we can perform logical relation between two tables, in SQL server database table. = and <> operator used to compare record between two tables for getting desired value. 

Different type of Joins in SQL?

SQL server is having meagerly 5 type of joins which are as follows:

1. Inner join
2. Left outer join
3. Right outer join
4. Full outer join
5. Cross join

For showing example for we will use below mention tables. We will use these Employee and Project. Here we will use select statement to demonstrate the different type of joins in the SQL server.

Here is the Project table.

Inner join

Inner join in SQL server is the join which will return the record if the given condition match between two tables. To perform the inner join, we use "Inner Join".

 SELECT E.[Id]
      ,E.[Employee_Name]
      ,E.[Department]
      ,E.[Salary]
	  ,P.ProjectName
  FROM [Employee].[dbo].[Employee] As E
  Inner Join [Employee].[dbo].[Projects] As P on E.Id=P.Employee_Id 

In above query I have shown how you can perform inner by using two tables. Here I have put Employee Id from Employee table and Project able column which is holding the Employee Id. Here is Inner join if the column id or value does not match on that case it will not return any record otherwise it will return the value.

Left outer join

Left outer join is the join where we will get all the record from the left table (Projects) and all the matching record from the right-side table (Employee)in condition.  If there is no match in the records of the table it will return null for left table and all records from the right table. 

 SELECT E.[Id]
      ,E.[Employee_Name]
      ,E.[Department]
      ,E.[Salary]
	  ,P.ProjectName
  FROM [Employee].[dbo].[Employee] As E
  Left Outer Join [Employee].[dbo].[Projects] As P on E.Id=P.Employee_Id 

Here in above query, I have used Left outer join. To perform this, I have added one more record in the Employee table whose reference not there in the Project table. 

Now when we run the above query, we will get null for the left side of the tale and all record from right table.

Right outer join

Right outer join is the join where we will get all the record from the Right table (Employee) and all the matching record from the left side table (Projects)in condition.  If there is no match in the records of the table it will return null for Right table and all records from the left table. 

 SELECT E.[Id]
      ,E.[Employee_Name]
      ,E.[Department]
      ,E.[Salary]
	  ,P.ProjectName
  FROM [Employee].[dbo].[Employee] As E
  Right Outer Join [Employee].[dbo].[Projects] As P on E.Id=P.Employee_Id 

Here in above query, I have used Right outer join. To perform this, I have added one more record in the Project table whose reference not there in the Employee table. 

Now when we run the above query, we will get null for the right side of the table (Employee) and all record from left table (Projects).

Full Outer join

Full outer join which returns all record from the Left Table (Projects) and Right Table(employee). Full Outer Join is also known as Full Join. Now check the query for the Full Outer Join.

 SELECT E.[Id]
      ,E.[Employee_Name]
      ,E.[Department]
      ,E.[Salary]
	  ,P.ProjectName
  FROM [Employee].[dbo].[Employee] As E
  Full Outer Join [Employee].[dbo].[Projects] As P on E.Id=P.Employee_Id 

The above query will return output. 

Here in above result, you can see the first we have left table record and null for right table and on other hand record for right table and null for left table.

Cross join

Cross join is the join where we will all records from both the table (Employee, Projects).

 SELECT E.[Id]
      ,E.[Employee_Name]
      ,E.[Department]
      ,E.[Salary]
	  ,P.ProjectName
  FROM [Employee].[dbo].[Employee] As E
  Cross Join [Employee].[dbo].[Projects] as P 

Here we have shown Cross join in both the tables. Here we will get all record.