SQL Server Join Types Examples

Problem

I am new to Microsoft SQL Server and want to learn about the JOIN options for the relational database. What are all of the JOIN options in SQL Server? What is the significance of each of the options? I am a little confused on the differences and syntax, can you provide some examples and explanations? Are JOINs only for SELECT statements? Check out this SQL Tutorial to learn about SQL Server Join Types and example syntax.

Solution

Joining tables to obtain the needed data for a query, script or stored procedure is a key concept as you learn about SQL Server development. In a nutshell, joins are typically performed in the FROM clause of a table or view for the SELECT, INSERT…SELECT, SELECT…INTO, UPDATE and DELETE statements. In previous versions of SQL Server, join logic could also have been included in the WHERE clause with = (INNER JOIN), *= (LEFT OUTER JOIN), =* (RIGHT OUTER JOIN), etc. syntax, but the support has been reduced and the best practice in SQL Server is to use the syntax outlined in the examples below.

Types of Joins in SQL Server

Before we jump into code, let’s provide some baseline information on the types of JOINs in SQL Server:

SQL INNER JOIN – Match rows between the two tables specified in the INNER JOIN statement based on one or more columns having matching data – Equi Join. Preferably the join is based on referential integrity enforcing the relationship between the tables to ensure data integrity.
- Just to add a little commentary to the basic definitions above, in general the INNER JOIN option is considered to be the most common join needed in applications and/or queries. Although that is the case in some environments, it is really dependent on the database design, referential integrity and data needed for the application. As such, please take the time to understand the data being requested then select the proper join option.
- Although most join logic is based on matching values between the two columns specified, it is possible to also include logic using greater than, less than, not equals, etc.
SQL LEFT OUTER JOIN – Based on the two tables specified in the join clause, all data is returned from the left table. On the right table, the matching data is returned in addition to NULL values where a record exists in the left table, but not in the right table.
- Another item to keep in mind is that the LEFT and RIGHT OUTER JOIN logic is opposite of one another. So, you can change either the order of the tables in the specific join statement or change the JOIN from left to right or vice versa and get the same results.
SQL RIGHT OUTER JOIN – Based on the two tables specified in the join clause, all data is returned from the right table. On the left table, the matching data is returned in addition to NULL values where a record exists in the right table but not in the left table.
SQL Self Join – In this circumstance, the same table is specified twice with two different aliases in order to match the data within the same table.
SQL CROSS JOIN – Based on the two tables specified in the join clause, a Cartesian product is created if a WHERE clause does filter the rows. The size of the Cartesian product is based on multiplying the number of rows from the left table by the number of rows in the right table. Please heed caution when using a CROSS JOIN.
SQL FULL JOIN – Based on the two tables specified in the join clause, all data is returned from both tables regardless of matching data.

Let’s walk through examples from the AdventureWorks sample database that is available for SQL Server to provide example SQL statements for each type of JOIN then provide some insight into the usage and sample result sets.

SQL Server INNER JOIN Example

In the following query we have a SQL INNER JOIN clause between the Sales.SalesOrderDetail and Production.Product tables.
The tables are aliased with the following:
- SOD for Sales.SalesOrderDetail
- P for Production.Product.
The JOIN condition is based on matching rows in the SOD.ProductID and P.ProductID columns.
The records are filtered by only returning records with the SOD.UnitPrice (column name) 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.

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

SQL Server LEFT OUTER JOIN Example

In the following query 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 rows between the Person.Contact and Sales.SalesPerson tables in conjunction with all of the data from the Sales.SalesPerson table and matching rows 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.

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

SQL Server 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 following query 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 rows between the Person.Contact and Sales.SalesPerson tables in conjunction with all of the data from the Sales.SalesPerson table and matching rows 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.

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

SQL Server 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 column names.
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.

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

SQL Server CROSS JOIN Example

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.

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

SQL Server 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.

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

Next Steps

As you begin to start coding in SQL Server 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:

Check out the related MSSQLTips articles:

Resources

Community

Subscribe