Ice Pond Software

Let's walk through examples from the AdventureWorks sample database that is available for MSSQL to provide examples of each type of join then provide some insight into the usage and sample result sets.

INNER JOIN Example

In this example we are joining between the Sales.SalesOrderDetail and Production.Product tables.  The tables are aliased with the following:  SOD for Sales.SalesOrderDetail and P for Production.Product.  The JOIN logic is based on matching records in the SOD.ProductID and P.ProductID columns.  The records are filtered by only returning records with the SOD.UnitPrice greater than 1000.  Finally, the result set is returned in order with the most expensive first based on the ORDER BY clause and only the highest 100 products based on the TOP clause.

USE AdventureWorks; 
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 
INNER JOIN Production.Product P 
 ON SOD.ProductID = P.ProductID 
WHERE SOD.UnitPrice > 1000 
ORDER BY SOD.UnitPrice DESC
GO

LEFT OUTER JOIN Example

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 AdventureWorks;
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 Example

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 AdventureWorks;
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 Example

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 AdventureWorks;
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.ManagerID 
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 Example

As indicated above, please heed caution when running or modifying this query in any MSSQL 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 AdventureWorks; 
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 AdventureWorks;
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

Next Steps

• As you begin to start coding in MSSQL be sure to have a firm understanding of the JOIN options available as well as the associated data that is retrieved.  Be sure to select the correct JOIN logic based on the data that needs to be retrieved.
• Once you have a firm grasp of the JOIN logic with SELECT statements, progress to using the logic with  INSERT...SELECT, SELECT...INTO, UPDATE and DELETE statements
• In your learning process progresses, be sure to check out some of the alternatives to JOINs such as:
• Joining data and differences of using UNION and UNION ALL in MSSQL
• Comparing Multiple MSSQL Datasets with the INTERSECT and EXCEPT operators
• Special thanks to Dave of the MSSQLTips community for suggesting this tip.  If you have a MSSQL problem you would like answered, please email tips@mssqltips.com to see if we can provide the solution to you.
• Check out the related MSSQLTips:
• MSSQL Cursor Examples
• MSSQL T-SQL Aggregate Functions
• Getting started with MSSQL stored procedures
• MSSQL 2000 Text Data Manipulation
• Date/Time Conversions Using MSSQL

• Understanding MSSQL Physical Joins
• Calculate Running Totals Using MSSQL CROSS JOINs
• Using CROSS JOIN queries to find records out of sequence
• How to join MSSQL tables where columns include NULL values
• Joining data and differences of using UNION and UNION ALL in MSSQL
• More...