SQL Server Join is an important concept for every developer to learn who deal with Database. Basically, Join facilitates getting data from multiple tables in a single query. In this article, we will learn how many different kinds of join are there in SQL Server and in what scenarios each join type can be used.
SQL Server Join Types:
- INNER JOIN or Simple JOIN: Returns the common data between two tables.
- LEFT JOIN or LEFT OUTER JOIN: Returns the common data as well as all data from the left table between two tables.
- RIGHT JOIN or RIGHT OUTER JOIN: Returns the common data as well as all data from the right table between two tables.
- FULL JOIN or FULL OUTER JOIN: Returns all the data from both left as well as the right table between two tables.
Let's understand each join type with an example
Suppose we have two tables Customer and SalesOrderHeader respectively which has below columns
INNER JOIN
Suppose we want to return all the records from the Customer table which has the corresponding record in the SalesOrderHeader table.
Select SC.CustomerID,SC.AccountNumber,SO.OrderDate, SO.SalesOrderNumber,SO.TotalDue
From Sales.Customer SC
INNER JOIN Sales.SalesOrderHeader SO
ON SC.CustomerID = SO.CustomerID
LEFT OUTER JOIN or LEFT JOIN
Suppose we want to return all the records from the Customer table irrespective of whether we have records in the SalesOrderHeader table then we can use Left Join.
Select SC.CustomerID,SC.AccountNumber,SO.OrderDate, SO.SalesOrderNumber,SO.TotalDue
From Sales.Customer SC
LEFT JOIN Sales.SalesOrderHeader SO
ON SC.CustomerID = SO.CustomerID
In the above result, we can see that we are getting all the records from the left table i.e. Customer table even if we don't have corresponding records in SalesOrderHeader Table.
RIGHT OUTER JOIN or RIGHT JOIN
Similarly, If we want to return all the records from the right table i.e. SalesOrderHeader table even if there are no corresponding records in the left table i.e. Customer table then we can use RIGHT JOIN
Select SC.CustomerID,SC.AccountNumber,SO.OrderDate, SO.SalesOrderNumber,SO.TotalDue
From Sales.Customer SC
RIGHT JOIN Sales.SalesOrderHeader SO
ON SC.CustomerID = SO.CustomerID
FULL JOIN or FULL OUTER JOIN
Last if we want to return common data between two tables as well as uncommon data then we can go for FULL join or Full Outer Join.
Select SC.CustomerID,SC.AccountNumber,SO.OrderDate, SO.SalesOrderNumber,SO.TotalDue
From Sales.Customer SC
FULL JOIN Sales.SalesOrderHeader SO
ON SC.CustomerID = SO.CustomerID
Hope you have understood the different join types in SQL Server.
Share This Post
Support Me