Different Types of SQL JOINs

  different types of the JOINs in SQL:

• (INNER) JOIN: Returns records that have matching values in both tables
• LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table
• RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table
• FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table
• 
  

LEFT JOIN -

The LEFT JOIN keyword returns all records from the left table (table1), and the matched records from the right table (table2). The result is NULL from the right side, if there is no match.

SELECT Customers.CustomerName, Orders.OrderID

FROM Customers

LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID

ORDER BY Customers.CustomerName;

LEFT OUTER JOIN  

In this example we are combining two concepts to show that more than two tables can be JOINed in one SELECT statement and more than one JOIN type can be used in a single SELECT statement.  In the sample code below, we are retrieving the matching data between the Person.Contact and Sales.SalesPerson tables in conjunction with all of the data from the Sales.SalesPerson table and matching data in the Sales.SalesTerritory table.  For records that exist Sales.SalesPerson table and not in the Sales.SalesTerritory table, NULL values are returned for the columns in the Sales.SalesTerritory.  In addition, this code uses two columns to order the data i.e. ST.TerritoryID and C.LastName.

USE MSSQLTips;

GO

SELECT  C.ContactID,

 C.FirstName,

 C.LastName,

 SP.SalesPersonID,

 SP.CommissionPct,

 SP.SalesYTD,

 SP.SalesLastYear,

 SP.Bonus,

 ST.TerritoryID,

 ST.Name,

 ST.[Group],

 ST.SalesYTD

FROM Person.Contact C

INNER JOIN Sales.SalesPerson SP

 ON C.ContactID = SP.SalesPersonID

LEFT OUTER JOIN Sales.SalesTerritory ST 

 ON ST.TerritoryID = SP.TerritoryID

ORDER BY ST.TerritoryID, C.LastName

GO

RIGHT OUTER JOIN

  

In an effort to explain how the RIGHT OUTER JOIN and LEFT OUTER JOIN is logically a reciprocal on one another, the code below is re-written version of the LEFT OUTER JOIN above.  As you can see the JOIN order and tables are different, but the final result set matches the LEFT OUTER JOIN logic.   In the sample code below, we are retrieving the matching data between the Person.Contact and Sales.SalesPerson tables in conjunction with all of the data from the Sales.SalesPerson table and matching data in the Sales.SalesTerritory table.  For records that exist Sales.SalesPerson table and not in the Sales.SalesTerritory table, NULL values are returned for the columns in the Sales.SalesTerritory.

USE MSSQLTips;

GO 

SELECT  C.ContactID, 

 C.FirstName, 

 C.LastName, 

 SP.SalesPersonID,

 SP.CommissionPct,

 SP.SalesYTD,

 SP.SalesLastYear,

 SP.Bonus,

 ST.TerritoryID,

 ST.Name, ST.[Group],

 ST.SalesYTD 

FROM Sales.SalesTerritory ST 

RIGHT OUTER JOIN Sales.SalesPerson SP 

 ON ST.TerritoryID = SP.TerritoryID 

INNER JOIN Person.Contact C 

 ON C.ContactID = SP.SalesPersonID 

ORDER BY ST.TerritoryID, C.LastName

GO

Self Join 

In this example, we are actually self-joining to the HumanResources.Employee table.  We are doing this to obtain the information about the Employee and Manager relationship in the HumanResources.Employee table.  In conjunction with that JOIN logic we are also joining to the Person.Contact twice in order to capture the name and title data based on the original Employee and Manager relationships.  In addition, another new concept introduced in this query is aliasing each of the columns.  Although we could have done so in the previous examples, we made point of doing so in this query to differentiate between the Employee and Manager related data.

USE MSSQLTips;

GO

SELECT  M.ManagerID AS 'ManagerID',

 M1.ContactID AS 'ManagerContactID',

 M1.FirstName AS 'ManagerFirstName',

 M1.LastName AS 'ManagerLastName',

 M.Title AS 'ManagerTitle',

 E.EmployeeID AS 'EmployeeID',

 E1.ContactID AS 'EmployeeContactID',

 E1.FirstName AS 'EmployeeFirstName',

 E1.LastName AS 'EmployeeLastName',

 E.Title AS 'EmployeeTitle'

FROM HumanResources.Employee E 

INNER JOIN HumanResources.Employee M 

 ON E.ManagerID = M.EmployeeID 

INNER JOIN Person.Contact E1 

 ON E1.ContactID = E.ContactID 

INNER JOIN Person.Contact M1 

 ON M1.ContactID = M.ContactID

ORDER BY M1.LastName

GO

CROSS JOIN  

As indicated above, please heed caution when running or modifying this query in any SQL Server database environment.  The result set is intentionally limited by the TOP 100 clause and the WHERE clause to prevent a Cartesian product, which is the result of each of the rows from the left table multiplied by the number of rows in the right table.

USE MSSQLTips; 

GO 

SELECT  TOP 100 P.ProductID, 

 P.Name, 

 P.ListPrice, 

 P.Size, 

 P.ModifiedDate, 

 SOD.UnitPrice, 

 SOD.UnitPriceDiscount,

 SOD.OrderQty,

 SOD.LineTotal 

FROM Sales.SalesOrderDetail SOD 

CROSS JOIN Production.Product P 

WHERE SOD.UnitPrice > 3500 

ORDER BY SOD.UnitPrice DESC

GO

FULL OUTER JOIN Example

In our last example, we have modified the logic from the LEFT OUTER JOIN example above and converted the LEFT OUTER JOIN syntax to a FULL OUTER JOIN.  In this circumstance, the result set is the same as the LEFT OUTER JOIN where we are returning all of the data between both tables and data not available in the Sales.SalesTerritory is returned as NULL.

USE MSSQLTips;

GO

SELECT  C.ContactID,

 C.FirstName,

 C.LastName,

 SP.SalesPersonID,

 SP.CommissionPct,

 SP.SalesYTD,

 SP.SalesLastYear,

 SP.Bonus,

 ST.TerritoryID,

 ST.Name,

 ST.[Group],

 ST.SalesYTD

FROM Person.Contact C

INNER JOIN Sales.SalesPerson SP

 ON C.ContactID = SP.SalesPersonID

FULL OUTER JOIN Sales.SalesTerritory ST 

 ON ST.TerritoryID = SP.TerritoryID

ORDER BY ST.TerritoryID, C.LastName

GO